Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Slow update in memory table

counterpointcounterpoint EntrantCurrent User Role Beginner
What can cause a MEMORY table (which I understood to be very fast) to regularly update slowly with queries similar to:

UPDATE aliro_session SET time = '1268959445', marker = marker+1 WHERE session_id = '9215b8263dd679e2ffd78a39a8253e18' AND isadmin = 0 AND time > 1268958545</pre>

The field session_id is the primary key, so finding the record should be simple. And none of the fields in the table is too complicated:

CREATE TABLE IF NOT EXISTS `aliro_session` ( `time` int(11) NOT NULL default '0', `session_id` char(32) NOT NULL, `isadmin` tinyint(3) unsigned NOT NULL default '0', `guest` tinyint(4) NOT NULL default '1', `userid` int(11) NOT NULL default '0', `gid` tinyint(3) unsigned NOT NULL default '0', `marker` smallint(6) NOT NULL default '0', `ipaddress` varchar(15) NOT NULL, KEY `session_id` USING HASH (`session_id`)) ENGINE=MEMORY DEFAULT CHARSET=utf8;</pre>

Is there some reason why this would regularly crop up as a slow-ish query?


  • xaprbxaprb Mentor Inactive User Role Beginner
    In the context of your other message, I would start to suspect that your server is globally suffering from something, and it's not the query itself that's slow, but collateral damage caused by something else happening.
  • counterpointcounterpoint Entrant Current User Role Beginner
    That could be - although it rather undermines the advice to put optimization effort into slow queries. How do you pick out the queries that are slow on their own account as against those that are slow because of pressure on the server (which is also a web server running CMSs that involve significant PHP code execution)?

    During a period when the server was not running very well, the number of queries taking more than 0.5 seconds was less than 1%. So maybe there isn't really a problem of slow queries at all.

    But what remains puzzling is that there is still a definite and fairly consistent pattern to which queries are relatively slow. Some of these make sense, because they are relatively complex queries that do a lot of work (most have been worked on and don't seem to have much scope for improvement). Others are hard to understand - they are queries that normally execute very quickly indeed, yet somehow turn up as slow queries on a regular basis.

    Is there anything to be found in all this, or is at a pointless pursuit?
  • xaprbxaprb Mentor Inactive User Role Beginner
    MySQL doesn't have good metrics to help you work on problems like this. You have to do a bunch of manual work to correlate queries in the log, system activity, and so on. You need to be gathering a lot of information about everything you can. The Percona enhancements to the slow query log can help a LOT here.

    The pattern is fairly easy to theorize about, but hard to prove. For example, it makes sense that the queries that suffer might be accessing data that is locked by another query that's running slowly or has a high arrival rate. One of the techniques I sometimes use for cases like this is to get a GDB stack trace of the whole server when it's doing badly and aggregate that.

    You might have a hard time figuring it out from the slow query log -- re-running the query doesn't always show you anything meaningful about what's in the log. The log was then, this is now.
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.