Long "updating" state for mysql db. Does this indicate a problem?
Posted by mrzippy, 10-30-2012, 01:57 PM Hello, I am working with a customer to try and help him figure out what is "wrong" with his software. He's been complaining that the server hardware is at fault, so I'm trying to help him figure out that it's not hw related. We ran the "mysql> SHOW PROCESSLIST" command and got output like this: In the TIME column, you will notice that the times are rather long. Does this indicate a problem with the code or database or hw? Thanks! (BTW - not sure it matters, but it's a drupal 5 database.
Posted by alyak, 10-30-2012, 02:53 PM nice picture .... or database designed terrible , or some lock . looks like problem with database design . so simle query must work very fast . check indexes in ad_statistic table show inddexes from ad_statistic; seem need add ALTER TABLE ad_statistic ADD INDEX( aid , date , action );
Posted by zoid, 10-30-2012, 03:07 PM This query is not too complex. Are the right indexes in place (on the three columns in the where clause)? Also how many records does the table contain? What table type is it, MyISAM or InnoDB?
Posted by mrzippy, 10-30-2012, 03:27 PM Hello, The table is MyISAM and has about 10,221,972 rows. (1.2gb in size.) How can I tell if the right indexes are in place? In phpmyadmin for the ad_statistics table, I clicked the "Structure" tab and see this:
Posted by Dr_Michael, 10-30-2012, 03:49 PM First try to repair the db and then try to optimize it. Both actions can be done from phpmyadmin.