Slow in MEMORY queries

chris7chris7 EntrantInactive User Role Participant
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 | |+
+
+
+
+
+
+</pre>


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# [email protected]: 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';</pre>


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

Comments

  • sterinsterin Mentor Inactive User Role Leader
    <cite>chris7 wrote on Wed, 08 February 2012 09:31</cite>
    Would anyone be able to offer any suggestions why a simple UPDATE query might be taking 1.3 second to execute?
    What is the server doing at that point in time? High CPU or IO? Any backup jobs? Does the syslog contain anything at that point in time?

    <cite>chris7 wrote on Wed, 08 February 2012 09:31</cite>
    Any ideas how to fix the problem?
    How often does it occur and is it a real problem??
  • xaprbxaprb Mentor Inactive User Role Leader
    Use SHOW PROFILES.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.