•  

Announcement

Announcement Module
Collapse
No announcement yet.

Slow in MEMORY queries

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow in MEMORY queries

    #1
    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:\"c urrency\";s:3:\"USD\";s:13:\"currency_rate\";d:1.3 11299999999999910116343926347326487302780151367187 5;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_cu lture\";s:2:\"en\";s:8:\"has_blog\";b:1;s:15:\"act ive_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_us er\";a:8:{s:3:\"uid\";i:00001;s:4:\"name\";s:8:\"D Q 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:\"ti meoffset\";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 = 'd1622affzzzzxb6fedzzz560yyyyx1a535f9xyyyx111f971j jjjxaaaa6d8efd6';


    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
    Tags: None
  • #2
    chris7 wrote on Wed, 08 February 2012 09:31
    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?

    chris7 wrote on Wed, 08 February 2012 09:31
    Any ideas how to fix the problem?
    How often does it occur and is it a real problem??

    Comment

    • #3
      Use SHOW PROFILES.

      Comment

      • #4
        Thank you both for replies.

        Quote:
        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?
        Nothing at all. The server does NOTHING except being Percona server strictly dedicated to PHP session storage. No cron jobs, no nothing.

        Quote:
        How often does it occur and is it a real problem??
        It occurs 4-7 times a day. Well, it is 4-7 unhappy users a day who had to wait 1-2 seconds (rather than usual 0.002) for their session data to load. Indeed this isn't a huge practical problem. More my curiosity to find out why in RAM operation which should take microseconds (which 99.999 of the time is the case) all of sudden takes 1.5 - 1.7 seconds.

        Quote:
        Use SHOW PROFILES
        This seems a great way forward... however I understand Percona will only hold profiling information for up to 100 most recent queries. The server is doing on average 70 queries per second and problem occurs 4-7 times a day. Would you be able to suggest a practical solution how to actually capture profiling info for this few slow queries?

        Best regards,
        Chris

        Comment

        • #5
          You didn't mention that it's intermittent. That's totally different.

          You should use the Percona Server feature to log profiling information into the slow query log.

          Comment

          Working...
          X