Posted by Ainur on January 31, 2008 at 12:48pm
We are running community web site with medium of 2000-3000 visits per day, according to Google Analytics.
We used dedicated server AMD Athlon(tm) 64 X2 Dual Core Processor 4200+ with 2GB RAM
mysql Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (x86_64)
Here is my.cnf file
We upgraded to: AMD Athlon(tm) 64 X2 Dual Core Processor 6000+, With 8 GB DDR2 RAM
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (x86_64) using readline 5.2
Here is my.cnf file
SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.32-Debian_7etch3-log Debian etch distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 7 days 18 hours 34 min 26 sec Threads: 3 Questions: 70494889 Slow queries: 607 Opens: 71604 Flush tables: 1 Open tables: 64 Queries per second avg: 104.955
We upgraded Drupal from 4.7.x to 5.x.
The problem:
On the old server we didn’t experienced any problems with mysql, instead on the new machine we have the following situation:
After this upgrade we have serious MySQL overload...
On the new machine performing top via SSH I see:
top - 13:44:30 up 50 days, 17:28, 2 users, load average: 0.37, 0.56, 0.67
Tasks: 139 total, 2 running, 136 sleeping, 0 stopped, 1 zombie
Cpu(s): 19.4%us, 7.8%sy, 0.0%ni, 72.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8151860k total, 7297572k used, 854288k free, 262260k buffers
Swap: 2097140k total, 24k used, 2097116k free, 5648848k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29872 mysql 15 0 429m 183m 5812 S 30 2.3 2024:32 mysqld
26397 www-data 15 0 149m 31m 4500 R 24 0.4 0:03.34 apache2
26053 www-data 15 0 0 0 0 Z 0 0.0 0:08.46 apache2
26631 ainur 15 0 10728 1348 956 R 0 0.0 0:00.08 top
1 root 15 0 6124 684 564 S 0 0.0 0:01.92 init
2 root RT 0 0 0 0 S 0 0.0 0:00.46 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.00 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 0:00.66 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.02 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:03.71 events/0
9 root 10 -5 0 0 0 S 0 0.0 0:00.33 events/1
10 root 11 -5 0 0 0 S 0 0.0 0:00.00 khelper
11 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread
16 root 10 -5 0 0 0 S 0 0.0 0:00.30 kblockd/0
17 root 10 -5 0 0 0 S 0 0.0 0:00.04 kblockd/1
What am I doing wrong?
Comments
Have you tried running
Have you tried running mysqlreport, which Jeremy Andrews was just talking about in his talk for MySQL AB: http://hackmysql.com/mysqlreport
Optimize your my.cnf
If you are using MyISAM tables this is a pretty good hint:
key_buffer = 16M
Set this to the size of your DB indices (*.MYI) this is less than 3GB.
If you're using InnoDB you should really alter it's buffers accordingly for instance to the total size of your database if this is less than 6GB.
These are all ballpark figures, it's probably smart to read something about optimizing MySQL for some serious load. Also install innotop, it's much, much more helpful than 'top' when optimizing MySQL.
And post your observations
And please do post your observations on this :)
I'm no MySql expert and i always like to read more on this subject.
Thanks,
Andrei
Some stats: Password for
Some stats:
Password for database user dusoft: MySQL 5.0.32-Debian_7et uptime 0 20:48:15 Fri Feb 1 15:06:23 2008 __ Key _________________________________________________________________ Buffer used 97.16M of 256.00M %Used: 37.95 Current 114.34M %Usage: 44.67 Write hit 43.18% Read hit 99.99% __ Questions ___________________________________________________________ Total 13.92M 185.9/s DMS 8.31M 111.0/s %Total: 59.70 QC Hits 5.19M 69.3/s 37.29 Com_ 364.77k 4.9/s 2.62 COM_QUIT 53.33k 0.7/s 0.38 +Unknown 206 0.0/s 0.00 Slow (10) 57.55k 0.8/s 0.41 %DMS: 0.69 Log: ON DMS 8.31M 111.0/s 59.70 SELECT 4.16M 55.6/s 29.88 50.05 INSERT 2.29M 30.6/s 16.44 27.54 UPDATE 1.82M 24.3/s 13.08 21.92 DELETE 40.15k 0.5/s 0.29 0.48 REPLACE 1.24k 0.0/s 0.01 0.01 Com_ 364.77k 4.9/s 2.62 lock_tables 154.39k 2.1/s 1.11 unlock_tabl 154.39k 2.1/s 1.11 set_option 53.59k 0.7/s 0.38 __ SELECT and Sort _____________________________________________________ Scan 56.52k 0.8/s %SELECT: 1.36 Range 88.49k 1.2/s 2.13 Full join 173 0.0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 167.89k 2.2/s Sort range 27.06k 0.4/s Sort mrg pass 2 0.0/s __ Query Cache _________________________________________________________ Memory usage 12.47M of 32.00M %Used: 38.98 Block Fragmnt 22.53% Hits 5.19M 69.3/s Inserts 3.79M 50.6/s Insrt:Prune 5.51:1 41.4/s Hit:Insert 1.37:1 __ Table Locks _________________________________________________________ Waited 2.67k 0.0/s %Total: 0.03 Immediate 9.31M 124.3/s __ Tables ______________________________________________________________ Open 404 of 1024 %Cache: 39.45 Opened 1.12k 0.0/s __ Connections _________________________________________________________ Max used 12 of 200 %Max: 6.00 Total 53.33k 0.7/s __ Created Temp ________________________________________________________ Disk table 65.17k 0.9/s Table 165.70k 2.2/s Size: 32.0M File 12 0.0/s __ Threads _____________________________________________________________ Running 1 of 2 Cached 10 of 16 %Hit: 99.98 Created 12 0.0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 0 0/s Connects 3 0.0/s __ Bytes _______________________________________________________________ Sent 38.16G 509.5k/s Received 2.60G 34.7k/s __ InnoDB Buffer Pool __________________________________________________ Usage 0 of 0 %Used: 0.00 Read hit 0.00% Pages Free 0 %Total: 0.00 Data 0 0.00 %Drty: 0.00 Misc 0 0.00 Latched 0 0.00 Reads 0 0/s From file 0 0/s 0.00 Ahead Rnd 0 0/s Ahead Sql 0 0/s Writes 0 0/s Flushes 0 0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 0 0/s Writes 0 0/s fsync 0 0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 0 0/s Written 0 0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/sDisk table 65.17k
One on-disk temporary table created every second is less than ideal. (Ideally there would be none.) I doubt that accounts for all your troubles, but it's definitely worth looking into.
I found that a problem with the taxonomy module was creating (in our case) hundreds of thousands of tables on disk and killing performance. It took a while to track down the cause. Take a look at this: http://drupal.org/node/171685
Eli
Doesn't look that bad?
I'm not sure what the problem is? That Mysqlreport looks pretty OK, no obvious problems on first glace. A load average of 0.37 also doesn't seem like a 'MySQL overload' to me?
So my main question; what exactly is your problem? ;)
bartvb@drupal.org - the
bartvb@drupal.org - the problem is that on the new server value of TIME+ column of top output raises to 2000 within few days..
but on the old server this characteristic haven't raised to 100 during month or two of mysql uptime
may it be problem of top command doesn't count CPU time in right way?
Upgrade?
You also mention you upgraded from 4.x to 5.x? I can imagine that that could have a rather large effect on MySQL usage? Especially if you're currently using less caching or some additional modules?
If the only thing you changed is the hardware platform and the MySQL version then the cumulative CPU time shouldn't have changed significantly, at least not upward.
I currently have approx 0 experience with Drupal (still trying to find time to evaluate it properly for one of my projects :D) so I have no idea what the implications (DB wise) would be with an upgrade from version 4 to 5.
Whay about disks?
We have recently upgraded from MySQL 4.0.x to MySQL 5.0.x and also has a similar experience. Where we were fine with 4.x, we have had a lot of performace problems with 5.x
HW related ideas...
1) If you're using SATA, consider using SAS disks.
2) or try using several disks with RAID 5.
my.cnf related ideas...
1) Try to increase query cache.
2) Consider using InnoDB tables, the buffer pool may help a lot to reduce disk i/o. If you have 8GB, then you could define 4GB for buffer pool, depending on who big is your DB data might help a lot as well. InnoDB also offers ACID transactions.
3) If you don't have recovery to point in time procedures, then you may consider disabling the binlog. The make sure to make daily backups (mysqldump)... this may be an option depending on whether you can assume a day data loss in case of HW crash.