Hi,
I have a dedicated Percona Server (GPL), Release 22.1 server running on 4 core 512 MB RAM Xen VPS. There is only one database session with one table session_user using MEMORY engine. Please see details below:
show table status from session:*************************** 1. row *************************** Name: session_user Engine: MEMORY Version: 10 Row_format: Dynamic Rows: 83 Avg_row_length: 12587 Data_length: 1044736Max_data_length: 628494084 Index_length: 399440 Data_free: 21951728 Auto_increment: NULL Create_time: 2011-12-11 12:49:23 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment:describe session_user:±---------------±-------------±-----±----±--------±------+| Field | Type | Null | Key | Default | Extra |±---------------±-------------±-----±----±--------±------+| sess_id | char(64) | NO | PRI | NULL | || partner_id | int(11) | NO | MUL | NULL | || web_product_id | int(11) | NO | MUL | NULL | || customer_id | int(11) | NO | MUL | NULL | || user_id | int(11) | NO | MUL | NULL | || domain | varchar(255) | NO | | NULL | || app | varchar(45) | NO | | NULL | || host | varchar(15) | NO | | NULL | || user_agent | varchar(255) | NO | | NULL | || sess_data | longblob | NO | | NULL | || sess_time | int(11) | NO | | NULL | || created_at | datetime | NO | | NULL | |±---------------±-------------±-----±----±--------±------+
The table holds around 100-150 records and server is hit by approx. 70 queries per second.
Few times a day I see the following in slow-queries.log:
/usr/sbin/mysqld, Version: 5.5.17-55-log (Percona Server (GPL), Release 22.1). started with:Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sockTime Id Command Argument# Time: 120208 6:41:41# User@Host: 2wima[2wima] @ [192.168.10.5]# Thread_id: 38784831 Schema: session Last_errno: 0 Killed: 0# Query_time: 1.329374 Lock_time: 0.000073 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 Rows_read: 0# Bytes_sent: 52 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0use session;SET timestamp=1328683301;UPDATE session_user SET sess_data = ‘symfony/user/sfUser/lastRequest|i:1328683300;symfony/user/sfUser/authenticated|b:1;symfony/user/sfUser/credentials|a:2:{i:0;s:4:"user";i:1;s:6:"Normal";}symfony/user/sfUser/attributes|a:3:{s:30:"symfony/user/sfUser/attributes";a:4:{s:7:"referer";s:0:"";s:8:"currency";s:3:"USD";s:13:"currency_rate";d:1.3112999999999999101163439263473264873027801513671875;s:10:"partner_id";i:1;}s:11:"web_product";a:10:{s:2:"id";i:2;s:5:"theme";s:7:"netserv";s:12:"product_code";s:1:"c";s:10:"web_domain";s:15:"dummy.net";s:4:"name";s:15:"Dummy";s:10:"short_name";s:15:"Dummy";s:15:"default_culture";s:2:"en";s:8:"has_blog";b:1;s:15:"active_cultures";a:2:{i:0;s:2:"en";i:1;s:2:"es";}s:17:"active_currencies";a:3:{i:0;s:3:"GBP";i:1;s:3:"USD";i:2;s:3:"EUR";}}s:12:"current_user";a:8:{s:3:"uid";i:00001;s:4:"name";s:8:"DQ three";s:9:"parent_id";i:00001;s:2:"cc";i:91;s:3:"ext";s:4:"200 1";s:12:"sip_password";s:6:"secret";s:10:"timeoffset";s:4:"+5.5";s:6:"mobile";N;}}symfony/user/sfUser/culture|s:2:"en";’, sess_time = 1328683300, host = ‘10.10.10.3’, app = ‘chat-user’ WHERE sess_id = ‘d1622affzzzzxb6fedzzz560yyyyx1a535f9xyyyx111f971jjjjxaaaa6d8efd6’;
Would anyone be able to offer any suggestions why a simple UPDATE query might be taking 1.3 second to execute? Any ideas how to fix the problem?
Thank you,
Chris