Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
So with skip-name-resolve in my.cnf (and MySQL restarted), it should be okay to have [EMAIL PROTECTED] in the GRANT table since localhost resolves without DNS lookup? Or do I need to specify [EMAIL PROTECTED] Sorry, just need to clarify this bit before changing a few things. Thanks again. ...Rene On 24-Sep-08, at 3:33 PM, Ken Menzel wrote: Yes, you can still use a hostname in the connection string, that's not what mysql uses it for, that hostname gets you from the client to the server. If you use GRANT to permit access from certain hosts IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'. Then the server will not be able to resolve those named permissions from the connecting IP back to a domain name matched to a grant to allow login. Also I don't think you can use subnets in a grant, you can use a single IP. However 'myuser'@'%' should continue to work just fine as should localhost using the file socket. Everything else should behave normally. Localhost connections are usually through the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Presently, I'm only using localhost for MySQL database user privileges, e.g., : [EMAIL PROTECTED] -- for all privileges on all databases Do I need to change the above if I add skip-name-resolve to my.cnf? (Since localhost is, I thought, not really part of DNS but just an alias for 127.0.0.1 -- so there's no lookup needed, right?) If I do need to change it, would it be to add [EMAIL PROTECTED] ? ...Rene On 24-Sep-08, at 3:59 PM, Martin Gainty wrote: Hello Rene is hostname a FQDN or IP? Martin
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
from http://dev.mysql.com/doc/refman/5.1/en/connecting.html On Unix, MySQL programs treat the hostname |localhost| specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to |localhost|, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a |--port| or |-P| option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use |--host| or |-h| to specify a hostname value of |127.0.0.1|, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for |localhost|, by using the |--protocol=TCP| option. For example: shell *|mysql --host=127.0.0.1|* shell *|mysql --protocol=TCP |* *|Just try it. localhost is trated as a special name used for the socketfile. If you want to use 127.0.0.1 or have programs that use that then you will need to specify 127.0.0.1 when using skip-dns option. It does not hurt to grant both localhost and 127.0.0.1 if you are unsure when you are not using DNS. Ken |**||* *||* Rene Fournier wrote: So with skip-name-resolve in my.cnf (and MySQL restarted), it should be okay to have [EMAIL PROTECTED] in the GRANT table since localhost resolves without DNS lookup? Or do I need to specify [EMAIL PROTECTED] Sorry, just need to clarify this bit before changing a few things. Thanks again. ...Rene On 24-Sep-08, at 3:33 PM, Ken Menzel wrote: Yes, you can still use a hostname in the connection string, that's not what mysql uses it for, that hostname gets you from the client to the server. If you use GRANT to permit access from certain hosts IE 'grant all on mydb.* to 'mydbuuser'@'%.mydomain.com'. Then the server will not be able to resolve those named permissions from the connecting IP back to a domain name matched to a grant to allow login. Also I don't think you can use subnets in a grant, you can use a single IP. However 'myuser'@'%' should continue to work just fine as should localhost using the file socket. Everything else should behave normally. Localhost connections are usually through the the mysql socket file (/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost.
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Hello Rene is hostname a FQDN or IP? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: [EMAIL PROTECTED]; mysql@lists.mysql.com From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps Date: Wed, 24 Sep 2008 14:21:44 +0200 Thanks. I've read those links, and they sound like my problem. On each connection, MySQL calls gethostbyname() to resolve the hostname in the connection string into 127.0.0.1 -- e.g., mysql_connect(localhost, user, password) - 127.0.0.1. Because FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad things can happen while MySQL waits on gethostbyname(). At least, that's where the CPU is spending much of its time. Now, it sounds like using using 127.0.0.1 in place of localhost in the connection string is not enough, since MySQL will still call gethostbyaddr() as a reverse-lookup. (Right?) So this is why, as you say, it's necessary to add skip-name-resolve to my.cnf. (Right?) It's also then necessary to make the Grant tables not depend on hostnames (localhost), but specify 127.0.0.1. But here's the strange thing: On a test machine, I've added skip-name- resolve to my.cnf. But I can still use a hostname in the connection string, and it works. On 23-Sep-08, at 5:44 PM, Ken Menzel wrote: Hi Rene, This smells like an old freebsd issue with a non thread safe get- host-by-name issue and possibly other thread issues. Since Mac OS/X/ Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/ MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem
Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now
Re: Ancient, unsolved high-CPU problem
10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now
Re: Ancient, unsolved high-CPU problem
it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem
Appreciate the suggestions, some of which I've done. The processlist typically just shows the known PHP command-line scripts that run. Maybe 8-10 on average, 20 max. Here's a strange thing: If I stop all the requests to MySQL (shut down Apache, and exit all the commandline PHP scripts), MySQL's CPU usage remains high. So... no processes in MySQL, nothing hitting the database, yet MySQL CPU stays stuck at 60-70%. If I shutdown MySQL and restart, it's normal again. Weird, no? On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/ MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing
Re: Ancient, unsolved high-CPU problem
nothing on a server is weird, just not understood yet :-) so, 'show processlist' comes up with nothing. Does 'ps auxw' show any php processes still active? Does 'netstat -atp' show any established connections to mysql? How long does it take to re-create the problem? You've restarted mysql, how long to high cpu again? Did you start to rebuild indexes any time recently? You seem to have ruled out the web scripts, so can you stop the 30% cron job scripts running? in your other email CPU usage is 45%. The top figure is the percentage of CPU usage that mysql has used, compared with other processes. 92% of 45% (I believe). if you run 'vmstat 5' it will print out resource usage every 5 seconds (ignore the first line) which is more readable than top output as you see changes over time. Change 5 for an interval that suits your purpose (ie, if it takes 10 hours to happen, then vmstat 300). cheers, On 23 Sep 2008, at 15:04, Rene Fournier wrote: Appreciate the suggestions, some of which I've done. The processlist typically just shows the known PHP command-line scripts that run. Maybe 8-10 on average, 20 max. Here's a strange thing: If I stop all the requests to MySQL (shut down Apache, and exit all the commandline PHP scripts), MySQL's CPU usage remains high. So... no processes in MySQL, nothing hitting the database, yet MySQL CPU stays stuck at 60-70%. If I shutdown MySQL and restart, it's normal again. Weird, no? On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now -- MySQL General Mailing List
Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
Hi Rene, This smells like an old freebsd issue with a non thread safe get-host-by-name issue and possibly other thread issues. Since Mac OS/X/Darwin is a freebsd 4 branch it is a good bet they are the same. Is it possible for you to try adding skip-name-resolve to my.cnf. Alternatively you could compile with -D SKIP_DNS_CHECK. Please read about these options before trying them to understand any implication it my have on your GRANTs if you grant to a domain or server. Here are some links to more information, http://jeremy.zawodny.com/blog/archives/000203.html http://bugs.mysql.com/bug.php?id=414 http://www.mail-archive.com/mysql@lists.mysql.com/msg87497.html Hope this helps, Ken Rene Fournier wrote: In case a bit more data might help, here's what the server looks like right now, while experiencing the strange high-CPU load: VM_STAT sayeth: Mach Virtual Memory Statistics: (page size of 4096 bytes) Pages free: 534327. Pages active: 331233. Pages inactive: 1094528. Pages wired down: 137065. Translation faults: 957568490. Pages copy-on-write: 241306984. Pages zero filled:1302796176. Pages reactivated:790261. Pageins: 95668. Pageouts: 1212. Object cache: 217985425 hits of 220226841 lookups (98% hit rate) Top says: Processes: 115 total, 3 running, 112 sleeping... 504 threads 08:12:30 Load Avg: 2.43, 2.44, 2.30 CPU usage: 45.3% user, 48.2% sys, 6.5% idle Networks: 676 ipkts/72K 738 opkts /181K Disks: 10 reads/52K 594 writes/3049K VM: 0 pageins 0 pageouts PID COMMAND %CPU TIME FAULTS PGINS/COWS MSENT/MRCVD BSD/MACHCSW 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 1121358/3403231 20067 php 9.1% 6:53:45 1764 0/238 14/7 6128/14 584 25957 Terminal 7.0% 12:20:23150 0/0 1013/814 244/2407648 [...] And PS: USER PID %CPU %MEM VSZRSS TT STAT STARTED TIME mysql25943 114.1 -29.2 1239384 613296 ?? R10Sep08 3431:26.73 On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote: it's all a bit too general, we could be asking continual questions until someone asks the right one. However, I would put some debugging into the 30% scripts to check they complete before the next one starts, as if one script takes slightly longer (especially if the queries are the same) to complete then the rest build up quickly. Something else could be locking the table that your cron queries are trying to access, causing the stacking that never recovers. Once the problem occurs I'd be using 'show processlist' in mysql, and vmstat and ps to check the system resources. Is it definitely mysql, or php/apache, a slow disk, etc.. In terms of your stats below, I have (on a fairly average spec server) 500 queries per second and 2000 open tables. So, unless it's a PC or very badly tuned, it should be fine. cheers, Doug On 23 Sep 2008, at 14:16, Rene Fournier wrote: 10% of queries are web-based (Apache/PHP). 30% of queries are from command-line PHP scripts that get executed (average 1/second -- they end with mysql_close() btw). 60% of queries are from command-line PHP scripts that run continuously (in a loop, with sleep()), acting on incoming socket data. ...Rene On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote: Rene, How are you querying the database during normal use? What kind of applications are you using? ~Jeffrey Santos On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier [EMAIL PROTECTED] wrote: Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange
Ancient, unsolved high-CPU problem
For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ancient, unsolved high-CPU problem
Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per second avg: 14.765 I know what the slow queries are--some that take 20-30 seconds to compute, and they are normal. The number of open tables seems high, no? The database that gets 95% of the load has ~35 tables in total. As for cron jobs, I have a number of command-line PHP scripts that perform regular queries. They've been running for about 10 days now. The current high CPU state started a couple days ago. On 22-Sep-08, at 8:30 PM, Martin Gainty wrote: curious if you have any cron jobs starting to execute? what does mysqladmin status show ? Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Ancient, unsolved high-CPU problem Date: Mon, 22 Sep 2008 19:41:25 +0200 For the longest time, I've had a strange problem with MySQL. Basically, after a certain amount of time--sometimes a few days, sometimes a couple weeks--its CPU usage will go from a steady 20-30% to 80-90%. Actual load and number of queries is the same, nothing else changes. If I shutdown MySQL and restart it (not the server), CPU% goes back to normal. What could this be? (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11, MySQL 5.0.51a) ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Get more out of the Web. Learn 10 hidden secrets of Windows Live. Learn Now
