maxxx Posted March 30, 2007 Share Posted March 30, 2007 I've got some strange problem, which I noticed a while ago. My server is tracking companies on the web and I experience about 250,000 queries per day to my database server. After a few days, MySQL locks up and freezes the box. Can anybody elaborate on the following two messages from PhpMyAdmin in the system tab? I can read it, but it doesn't mean much to me. Are the values high for a healthy system? Handler_read_rnd 1,794 k The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly. Handler_read_rnd_next 4,815 k The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have. Quote Link to comment Share on other sites More sharing options...
Vikas Jayna Posted March 30, 2007 Share Posted March 30, 2007 You'll need to figure out the following things:- 1) Which tables are being queried most often. 2) Which columns appear most often in the where clause of these queries. 3) Are these columns indexed? Check out http://dev.mysql.com/doc/refman/5.0/en/indexes.html to know more about indexes. In case these columns are not indexed, then create an index on them. Quote Link to comment Share on other sites More sharing options...
maxxx Posted March 30, 2007 Author Share Posted March 30, 2007 Hi Vikas, Thanks for your reply. I've read something on table optimization a few days ago and that's what that article suggested as well, so I already went ahead and added indexes and made sure that the queries use them. It helped, but the values are still high as you can see. Just to give you some more information: I have currently around 9,600 records with ~8 fields each. Records increase by about 2,500 per month. I read all records at least twice every hour and every time an employee accesses some of the information as well. I'm averaging about 1-2 queries per second. Now, is that a lot or should the machine be able to handle it? Does MySQL lock up because the queries that have not been executed yet are queued? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 4, 2007 Share Posted April 4, 2007 Turn on the slow query log, and take it from there. Quote Link to comment Share on other sites More sharing options...
Vikas Jayna Posted April 5, 2007 Share Posted April 5, 2007 Well! the data size of 9600 records is miniscule and really 1-2 queries per second should not be freezing the box even if the queries are not optimized. Quote Link to comment Share on other sites More sharing options...
gluck Posted April 5, 2007 Share Posted April 5, 2007 Hi Vikas, Thanks for your reply. I've read something on table optimization a few days ago and that's what that article suggested as well, so I already went ahead and added indexes and made sure that the queries use them. It helped, but the values are still high as you can see. Just to give you some more information: I have currently around 9,600 records with ~8 fields each. Records increase by about 2,500 per month. I read all records at least twice every hour and every time an employee accesses some of the information as well. I'm averaging about 1-2 queries per second. Now, is that a lot or should the machine be able to handle it? Does MySQL lock up because the queries that have not been executed yet are queued? MySQL should be able to handle this very easy. What do mean when you say MySQL locks up? Do you have to restart the DB sever or does it simply not connect? If that is the case then the culprit could be your code. Do a top and see how much memory each request is taking. It could be possible that you scripting language either is clogging resouces (DB connections) or you end up executing some queries that clog the DB. Look for an dangling processes also. Quote Link to comment Share on other sites More sharing options...
maxxx Posted April 5, 2007 Author Share Posted April 5, 2007 You know, I'm really lost, too. I did as fenway suggested and got the following output: Reading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 5 Time=68.60s (343s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost SET insert_id=N; INSERT INTO `projects` ( `id` , `project_name` , `project_url` , `date` , `type` , `user` ) VALUES ( NULL , 'S', 'S', 'S', 'S', 'S' ) Count: 5 Time=66.00s (330s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost UPDATE `projects` SET `project_budget` = 'S', `ends` = 'S' WHERE `id` = 'S' Count: 1 Time=36.00s (36s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost UPDATE `user` SET `session` = 'S', `last_login` = N WHERE `id` = 'S' Count: 1 Time=33.00s (33s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost UPDATE `projects` SET `project_budget` = 'S', `ends` = 'S' WHERE `project_url` = 'S' There is no reason apparent to me why those queries start taking up to more than 1 minute! I'm very confused... MySQL should be able to handle this very easy. What do mean when you say MySQL locks up? MySQL takes a long time to respond. When executing /etc/init.d/mysql restart, mysql doesn't restart within 2 hours (I didn't want to wait longer at that point). Simple queries start taking a very long time. Is it possible that queries get queued and the queue gets too full? Quote Link to comment Share on other sites More sharing options...
fenway Posted April 5, 2007 Share Posted April 5, 2007 Could you post the SHOW CREATE TABLE for projects? Quote Link to comment Share on other sites More sharing options...
gluck Posted April 5, 2007 Share Posted April 5, 2007 MySQL should be able to handle this very easy. What do mean when you say MySQL locks up? MySQL takes a long time to respond. When executing /etc/init.d/mysql restart, mysql doesn't restart within 2 hours (I didn't want to wait longer at that point). Simple queries start taking a very long time. Is it possible that queries get queued and the queue gets too full? You should be able to restart the DB server right away. Killing all processes should fix any memory leaks. There is something wrong. Start with only one table: change the queries and see what the response is. Paste the table structure as suggested by fenway Quote Link to comment Share on other sites More sharing options...
maxxx Posted April 6, 2007 Author Share Posted April 6, 2007 Hey guys, I really appreciate your help on this! #ps ux // process table of the machine USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND root 1 0.0 0.1 1564 544 ? Ss Apr04 0:00 init [3] root 2 0.0 0.0 0 0 ? S Apr04 0:00 [migration/0] root 3 0.0 0.0 0 0 ? SN Apr04 0:00 [ksoftirqd/0] root 4 0.0 0.0 0 0 ? S Apr04 0:00 [watchdog/0] root 5 0.0 0.0 0 0 ? S< Apr04 0:00 [events/0] root 6 0.0 0.0 0 0 ? S< Apr04 0:00 [khelper] root 7 0.0 0.0 0 0 ? S< Apr04 0:00 [kthread] root 90 0.0 0.0 0 0 ? S< Apr04 0:00 [kblockd/0] root 91 0.0 0.0 0 0 ? S< Apr04 0:00 [kacpid] root 157 0.0 0.0 0 0 ? S< Apr04 0:00 [ata/0] root 158 0.0 0.0 0 0 ? S< Apr04 0:00 [ata_aux] root 159 0.0 0.0 0 0 ? S< Apr04 0:00 [ksuspend_usbd] root 162 0.0 0.0 0 0 ? S< Apr04 0:00 [khubd] root 164 0.0 0.0 0 0 ? S< Apr04 0:00 [kseriod] root 174 0.0 0.0 0 0 ? S< Apr04 0:00 [khpsbpkt] root 188 0.0 0.0 0 0 ? S Apr04 0:00 [pdflush] root 189 0.0 0.0 0 0 ? S Apr04 0:00 [pdflush] root 190 0.0 0.0 0 0 ? S< Apr04 0:00 [kswapd0] root 191 0.0 0.0 0 0 ? S< Apr04 0:00 [aio/0] root 192 0.0 0.0 0 0 ? S< Apr04 0:00 [jfsIO] root 193 0.0 0.0 0 0 ? S< Apr04 0:00 [jfsCommit] root 194 0.0 0.0 0 0 ? S< Apr04 0:00 [jfsSync] root 890 0.0 0.0 0 0 ? S< Apr04 0:00 [kpsmoused] root 902 0.0 0.0 0 0 ? S< Apr04 0:00 [kjournald] root 1085 0.0 0.1 1888 696 ? S<s Apr04 0:02 /sbin/udevd --d root 4699 0.0 0.3 3252 1836 ? Ss Apr04 0:00 /usr/sbin/syslo root 5106 0.0 0.0 1716 248 ? Ss Apr04 0:03 /sbin/dhcpcd -h root 5317 0.0 0.2 3984 1060 ? Ss Apr04 0:00 /usr/sbin/sshd root 5373 0.0 0.0 1576 360 ? Ss Apr04 0:00 /usr/bin/svscan root 5380 0.0 0.0 1416 312 ? S Apr04 0:00 supervise dnsca root 5381 0.0 0.0 1416 312 ? S Apr04 0:00 supervise log root 5436 0.0 1.5 19540 7868 ? Ss Apr04 0:00 /usr/sbin/apach root 5696 0.0 0.5 7964 2612 ? Ss Apr04 0:00 /usr/sbin/smbd root 5705 0.0 0.2 3780 1204 ? Ss Apr04 0:00 /usr/sbin/nmbd root 5722 0.0 0.2 7964 1072 ? S Apr04 0:00 /usr/sbin/smbd root 5762 0.0 0.1 1816 692 ? Ss Apr04 0:00 /usr/sbin/cron root 5820 0.0 0.1 2232 896 ? Ss Apr04 0:00 /usr/sbin/xinet root 5891 0.0 0.1 1604 644 tty1 Ss+ Apr04 0:00 /sbin/agetty 38 root 5893 0.0 0.1 1600 640 tty2 Ss+ Apr04 0:00 /sbin/agetty 38 root 5894 0.0 0.1 1600 640 tty3 Ss+ Apr04 0:00 /sbin/agetty 38 root 5895 0.0 0.1 1604 644 tty4 Ss+ Apr04 0:00 /sbin/agetty 38 root 5896 0.0 0.1 1600 640 tty5 Ss+ Apr04 0:00 /sbin/agetty 38 root 5898 0.0 0.1 1604 644 tty6 Ss+ Apr04 0:00 /sbin/agetty 38 root 8164 0.0 0.1 2124 820 ? S Apr05 0:00 /usr/sbin/cron root 8165 0.0 0.2 2464 1060 ? Ss Apr05 0:00 /bin/bash -c te root 8166 0.0 0.2 2464 1132 ? S Apr05 0:00 /bin/bash /usr/ root 8170 0.0 0.2 2460 1052 ? S Apr05 0:00 /bin/bash /etc/ root 8172 0.0 0.1 3036 992 ? S Apr05 0:00 /usr/sbin/sendm root 8173 0.0 0.9 15292 5024 ? S Apr05 0:00 /usr/lib/php5/b root 8904 0.0 0.1 2124 820 ? S 01:40 0:00 /usr/sbin/cron root 8905 0.0 0.2 2464 1052 ? Ss 01:40 0:00 /bin/bash /etc/ root 8917 0.0 0.1 3036 992 ? S 01:40 0:00 /usr/sbin/sendm root 8918 0.0 0.9 15292 5024 ? S 01:40 0:00 /usr/lib/php5/b root 9012 0.0 0.1 2124 820 ? S 01:57 0:00 /usr/sbin/cron root 9013 0.0 0.2 2460 1052 ? Ss 01:57 0:00 /bin/bash /etc/ root 9015 0.0 0.9 15296 5032 ? S 01:57 0:00 /usr/lib/php5/b root 9016 0.0 0.1 3032 988 ? S 01:57 0:00 /usr/sbin/sendm root 9256 1.6 0.4 6800 2188 ? Ss 02:46 0:00 sshd: root@pts/ root 9259 0.3 0.3 2668 1536 pts/0 Ss 02:46 0:00 -bash root 9264 0.0 0.1 2196 888 pts/0 R+ 02:46 0:00 ps ux -> \G SHOW CREATE TABLE projects *************************** 1. row *************************** Table: projects Create Table: CREATE TABLE `projects` ( `id` int(5) NOT NULL auto_increment, `project_name` varchar(255) NOT NULL default '', `project_url` varchar(255) NOT NULL default '', `date` varchar(10) NOT NULL default '', `type` enum('latest','lost','won') NOT NULL, `user` varchar(10) NOT NULL default '', `project_budget` varchar(16) default NULL, `ends` varchar(20) default NULL, UNIQUE KEY `id` (`id`), KEY `project_url` (`project_url`), KEY `user` (`user`), KEY `type` (`type`), KEY `id_2` (`id`), KEY `ends` (`ends`), KEY `project_budget` (`project_budget`), KEY `date` (`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) # free total used free shared buffers cached Mem: 511916 159372 352544 0 15720 53544 -/+ buffers/cache: 90108 421808 Swap: 506036 0 506036 ~edit~ You know, folks, what's just struck me? It's saying that the entire swap disk is empty. Is that normal, and may this be the source of the problem? If so, how do I fix that? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.