20 ms·

Honestly, the first thing I did was add indexes. On device_id, user_id, and date_added. I'm using Linode and I kept getting these I/O notifications...and the s

Honestly, the first thing I did was add indexes. On device_id, user_id, and date_added. I'm using Linode and I kept getting these I/O notifications...and the site would load extremely slow at times. After looking at the log of my Rails app, I saw that some requests were taking > 20 seconds. I determined that this was some kind of blocking at the database. I connected to MySQL and ran something simple... select count(*) from location; It took a long time...I turned profiling on and saw that it was taking a really long time in "table lock". I assumed (which probably wasn't a good idea) that it was going through and counting all the rows. I didn't really know what to make of this..because I didn't think that getting the count would take so long. I embarked on reading about partitioning, which may have been a solution for a problem that didn't actually exist (based on the feedback here). I attempted to partition (on what I put in the article), and everything seemed much snappier after that. If the indexes should have solved the problem (given that they were correct), I don't know why the location queries were taking so long. Anyway, I still have lots to learn on the database front, and maybe the fact that my VM had 7MB free of memory was causing weird things to happen, I'm not sure. Thanks for all the feedback and I have definitely learned a lot in this thread. kogir: I'm using MyISAM (which, in retrospect, seems stupid), so I don't even have foreign key constraints (InnoDB only, I believe).

carbocation 17y ago
Can you tell us the size of your query cache and your key cache, if they are enabled? If not, we should talk about enabling them. :-)
- chrismoos 17y ago
  They are rather low...I figured that would be good on a limited memory configuration (maybe not :( ). query_cache_limit = 1M query_cache_size = 16M key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8
  - carbocation 17y ago
    OK, at least they're enabled :-) I'm not a DBA, but have hit IO issues with my MYISAM/MYSQL database many times myself. 1) Have you run Mysqltuner? If not, wget http://mysqltuner.com/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl chmod to executable and execute it. I find it rather helpful. 2) Are you indexing every field that is part of any WHERE statement (at least for the big tables)? 3) Have you run EXPLAIN on the original query? (Suggested by someone else in this thread, and a critical question.) 4) Is the slow_query_log enabled, and are you sure about which query is causing the slowdown? 5) Have you tried tools like MySQL Administrator, which lets you monitor connections, threads, keys, queries, etc, in realtime?
rimantas 17y ago
MySQL does not scan any rows for SELECT COUNT(*) FROM table — the number of records is stored in table metadata. (Mind you, it only works for this kind of query, as soon as you add WHERE the story changes). On the other hand, MyISAM only has table level locks, not row level. I guess it is time for you to analyze what your app does with DB and how it can affect locking…
- jfarmer 17y ago
  Sort of true -- for the InnoDB engine SELECT COUNT(*) FROM table is more complicated because each transaction has its own view of the table. So unlike MyISAM (which uses one big table-level lock to guarantee consistency), it can't store a row count on a per-table basis.
jfarmer 17y ago
1. Were the keys composite or not? The correct key for the queries you listed is (device_id, date_added DESC). In MySQL you'd add this by doing "ALTER TABLE location ADD INDEX (device_id, date_added DESC)" MySQL uses one key per query, so having an index on (device_id) and an index on (date_added) individually will do no (or very little) good for the queries you listed. 2. MyISAM uses table-level locking. This will block all reads (selects) while writes (insert, update, delete) are happening. It does not prevent multiple selects from happening at the same time. If you have a write-heavy table that you are also reading from you need to be using InnoDB. 3. SELECT COUNT(*) FROM location should be instantaneous in MyISAM . It will be slow(er) in InnoDB. If this query was blocking it's because something was trying to write to the table. 4. Foreign key constraints have nothing to do with anything here. That's one advantage of InnoDB, although most web apps I know don't use them, but not one that would help you. Rather, what will help you is the table-level vs. row-level for MyISAM vs. InnoDB. This allows selects to happen to unaffected rows while writes are happening.

carbocation 17y ago

Can you tell us the size of your query cache and your key cache, if they are enabled? If not, we should talk about enabling them. :-)

chrismoos 17y ago
They are rather low...I figured that would be good on a limited memory configuration (maybe not :( ). query_cache_limit = 1M query_cache_size = 16M key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8
- carbocation 17y ago
  OK, at least they're enabled :-) I'm not a DBA, but have hit IO issues with my MYISAM/MYSQL database many times myself. 1) Have you run Mysqltuner? If not, wget http://mysqltuner.com/mysqltuner.pl http://mysqltuner.com/mysqltuner.pl chmod to executable and execute it. I find it rather helpful. 2) Are you indexing every field that is part of any WHERE statement (at least for the big tables)? 3) Have you run EXPLAIN on the original query? (Suggested by someone else in this thread, and a critical question.) 4) Is the slow_query_log enabled, and are you sure about which query is causing the slowdown? 5) Have you tried tools like MySQL Administrator, which lets you monitor connections, threads, keys, queries, etc, in realtime?

rimantas 17y ago

MySQL does not scan any rows for SELECT COUNT(*) FROM table — the number of records is stored in table metadata. (Mind you, it only works for this kind of query, as soon as you add WHERE the story changes). On the other hand, MyISAM only has table level locks, not row level. I guess it is time for you to analyze what your app does with DB and how it can affect locking…

jfarmer 17y ago
Sort of true -- for the InnoDB engine SELECT COUNT(*) FROM table is more complicated because each transaction has its own view of the table. So unlike MyISAM (which uses one big table-level lock to guarantee consistency), it can't store a row count on a per-table basis.

jfarmer 17y ago

1. Were the keys composite or not? The correct key for the queries you listed is (device_id, date_added DESC). In MySQL you'd add this by doing "ALTER TABLE location ADD INDEX (device_id, date_added DESC)" MySQL uses one key per query, so having an index on (device_id) and an index on (date_added) individually will do no (or very little) good for the queries you listed. 2. MyISAM uses table-level locking. This will block all reads (selects) while writes (insert, update, delete) are happening. It does not prevent multiple selects from happening at the same time. If you have a write-heavy table that you are also reading from you need to be using InnoDB. 3. SELECT COUNT(*) FROM location should be instantaneous in MyISAM . It will be slow(er) in InnoDB. If this query was blocking it's because something was trying to write to the table. 4. Foreign key constraints have nothing to do with anything here. That's one advantage of InnoDB, although most web apps I know don't use them, but not one that would help you. Rather, what will help you is the table-level vs. row-level for MyISAM vs. InnoDB. This allows selects to happen to unaffected rows while writes are happening.