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/MACH CSW
25943 mysqld 92.6% 57:11:01 6473 0/0 154/154
1121358/340 3231
20067 php 9.1% 6:53:45 1764 0/238 14/7
6128/14 584
25957 Terminal 7.0% 12:20:23 150 0/0 1013/814
244/2407 648
[...]
And PS:
USER PID %CPU %MEM VSZ RSS TT STAT STARTED TIME
mysql 25943 114.1 -29.2 1239384 613296 ?? R 10Sep08 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 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]