By Ainur on
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
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.
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
possible mixup?
The my.cnf file you've listed for MySQL 4.1 is labelled 'new' and the one listed for 5 is 'old'. Did you get them backwards?
I see you've enabled the slow query log in the 'new' file. That's good: take a look there and see if there are any queries taking up a majority of the server's time. I've seen that problem before: one query was tying up the server for a minute or more every time it ran.
Have you cleaned out the sessions table, as well as set the watchdog entries to be deleted after a reasonable timeframe (2-4 weeks)? Are there any tables which are outrageously large given the size of your database?
Another thing to try is to work with the query cache. Look at the query cache efficiency. Try upping the query cache size if you have lots of RAM (and you do).
The cause might also be a module you have installed. At this point it's hard to tell because you upgraded multiple components at once.
I've cleaned the sessions
I've cleaned the sessions table, and my watchdog is set to delete records after 1 week.
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/sTable locking
My first suspect in this situation would be table locking. MySQL 5 with MyISAM tables has speed issues with tables locking. Try switching a few of the tables over to InnoDB and turning off table locking.
Hiveminds Magazine - for web publishers and community builders
http://www.hiveminds.co.uk
http://demo.hiveminds.co.uk
Drupal Support Wiki | Drupal Sverige