Why is this query slow? Should I use MyISAM rather than InnoDB here?

I assume you are getting the record anyway. You could also use something like memcached for storing the locks I don't know the specifics off the top of my head, but my understanding is that InnoDB is great for concurrent reads, but sucks for concurrent writes. MyISAM might be better, but my gut tells me the current design is flawed.

I assume you are getting the record anyway. You could also use something like memcached for storing the locks. I don't know the specifics off the top of my head, but my understanding is that InnoDB is great for concurrent reads, but sucks for concurrent writes.

MyISAM might be better, but my gut tells me the current design is flawed.

InnoDB is much better for write-heavy applications than read-heavy applications. – Xorlev Mar 2 '10 at 0:28 I totally agree with the memcached idea. That may be the direction we head if we cannot find further optimizations.

– Chad Johnson Mar 2 '10 at 0:38 Well, for sake of optimization, you might want to check out MyTop to watch the processes a little, see the ratio to inserts / updates / deletes, etc. I think we need more information about what is going on before you can optimize it. Do you know how frequently this table is getting written to and deleted? Are there only 12 records, but they are changing every second?

– Jackson Miller Mar 2 '10 at 3:13 Okay, I'm getting the following: 0.29 inserts/s, 0.03 updates/s, 0.09 deletes/s, 20365.73 reads/s. 20.37K reads/s seems like a lot--do you agree? I'm not sure the 20K reads is all the same table.

– Chad Johnson Mar 2 '10 at 19:26 Whoa! That seems like a ton to me.1,000 would be a pretty big number. Can you share a little more about how many locks are getting checked on a typical page load?

Could there be a looping cron job running somewhere? – Jackson Miller 5.738 at 1:54.

No, I've never used EXPLAIN. I'll give it a go. – Chad Johnson Mar 2 '10 at 0:32 Nothing stands out with an EXPLAIN for a SELECT query against that table.

– Chad Johnson Mar 2 '10 at 0:36.

You could try segmenting the table on user_id to help with that.

Well, thanks for the input, but that sounds terribly, terribly messy from a database administration perspective. – Chad Johnson Mar 2 '10 at 0:30 Should too many connections really be a problem with InnoDB and a couple thousand users? – Chad Johnson Mar 2 '10 at 0:42.

Switching on the Innodb monitors can help narrow down the causes of poor performance: SHOW ENGINE INNODB STATUS and the InnoDB Monitors.

Okay, I'm getting the following: 0.29 inserts/s, 0.03 updates/s, 0.09 deletes/s, 20365.73 reads/s. 20.37K reads/s seems like a lot--do you agree? I'm not sure the 20K reads is all the same table.

I also get "Foreign key constraint fails for table record_lock. " Could the foreign key constraint failure slow things down? – Chad Johnson Mar 2 '10 at 19:22 I cannot see a foreign key constraint in the record-lock table.Do you have contraints from other tables into the record-lock table?

Looking again at the slow-log entries - the locks are not held for long, the record-lock table is small and so the actual statements should take no time: is it just that your system is getting maxed out doing other work? 20k reads/sec could do that. How is the output from "iostat -dx " looking on your database?

Is cpu pegged? %util? Did you try the monitors - it can give a lot of detail on contention and so on.

– Martin Mar 2 '10 at 20:41 Another thought: if you are serious about using MySQL, then this O'Reilly book is well worth the money: "gh Performance MySQL: Optimization, Backups, Replication, and More" – Martin Mar 3 '10 at 7:26.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.