FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?
Okay, I realize that query won't work, but that's essentially want I want to do: Add four months to the current date, then return the first day of that month, e.g.: floor ( 2008-10-16 + 4 months ) = 2009-02-1 Is there a nice SQL way of achieving this? ...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 -- 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
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: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
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
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
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
Re: Optimal MySQL server -- opinions?
On 28-Apr-08, at 11:26 AM, Francisco Rodrigo Cortinas Maseda wrote: The other counterpart is that if you can put the entire databases on RAM, your disk needs are not so high. So if you can afford that. Silly question, but with MySQL default configuration (say, huge.cnf), does it automatically put the entire database in RAM when there's enough available? I realize there are lots of parameters in the cnf file, some of which I've set. But for a 2 or 3GB database, and a server with 16GB of RAM, is there anything to do beyond using a generous cnf file to maximize MySQL's use of RAM? ...Rene
Re: Optimal MySQL server -- opinions?
On 29-Apr-08, at 10:41 AM, Andrew Braithwaite wrote: Hi, Three things... 1. You need to let us know what the DB server will be doing. Many CPU cores are only important of you have many CPU intensive MySQL connections in parallel. Will you have a read-intensive or write-intensive database load? Those 2950III you're considering can take up to 8 disks. If you're doing very read-intensive operations, you may want to consider a RAID1 pair for your OS, apps and MySQL tmp tables and a RAID 5 array for the MySQL data (to get the most disk space for your money without sacrificing redundancy). However; if you're doing a heavy work with lots of reads and writes at the same time then you should consider RAID1+0 for your data. RAM will always help for both MySQL caches and buffers and don't underestimate the great effect that lots of RAM for your filesystem cache will have (talking about unix/linux here, can't speak for windows). Right now, averaging 13 queries / second. 47% SELECTS 14% INSERTS 22% UPDATES 7% CHANGE DB I was thinking of doing a RAID 1/1 (four drives, all 15K SAS), with OS/ Apps on the first mirror, and MySQL data on the second. I'm working at tuning my table definitions and queries and a few other things in order to bring down the number of queries. Although I'm using indexes liberally, it seems LEFT JOINs are killing the queries that touch the 17 million row table. So I'm working on that. I want to fix my own queries before moving to faster hardware, but in any case, I would still like to get best iron for the task, long-term. With 8 disks, would something like RAID1 for OS/Apps, then RAID1+0 for MySQL data allow for substantially higher IO transactions? Row sizes are very small, it's mainly a latency thing from what I can see. 2. All the hardware vendors have promotions running all the time which they change every month. One month it will be cheaper disk, the next month will be cut-price RAM etc... The end result will be about the same... I've just been hoping that Dell would drop the price on the 2950III. It's been the same for about six months. 3. It's very easy to upgrade memory and processors as long as you don't mind 15 minutes or so of downtime for that server, linux will just see the new h/w when it comes back up. With hardware like HP and Dell you won't even need a screwdriver, it's all easy to use clips. Good advice, thanks. From reading everyone's advice, I'm inclined to save money on the CPU (just one quad-core Xeon 5430 at 2.66GHZ, 6MB L2 Cache) and spend more money on RAM (16GB instead of 8GB). ...Rene
Configuring a PowerEdge 2950III, suggestions?
Hi, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene
Optimal MySQL server -- opinions?
Okay, the previous subject was too narrow, what I am really looking for are opinions on general disk/memory/cpu configurations, manufacturer notwithstanding... As stated previously, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene
CPU usage periodically rises 50%, stays there
I've posted this problem before, but thought I had somehow addressed it. Today however the problem returned. In a nutshell: MySQL (version 5.0.45, on OS X 10.4.11, Xserve dual G5 2GHz) will after either a day or three weeks of uptime, suddenly usage way more CPU that it should. That is to say, mysqld shows in Activity Monitor 90% CPU usage when normally it sits around 30-40%. Slow query log file doesn't show anything unusual. Now here's the really strange part: If I shutdown all processes/services that use MySQL (and I can see nothing making a connection to the database), the CPU still stays high. Yet nothing is going on. It will stay like this for hours. The only way to fix the problem is to shutdown mysqld and restart. However, shutting it down takes a long time--15-20 minutes perhaps. Here's what MySQL Administrator returns after I click Shutdown: --- Stopping server... Shutting down MySQL ERROR! Failed to stop server, trying shutdown... Failed to stop server. --- And then it shuts down. Bizarre, non? Any ideas? ...Rene
