Can someone help me, i have problem with mysql processes. It eating a lot of my CPU and memory. I have 12MB ram Intel XEOn server. The load doesnt go down. Please help
We need more information such as top, iotop, my.cnf, need to know whats going on with the server and processes.
df -h Filesystem Size Used Avail Use% Mounted on /dev/sda3 66G 52G 10G 84% / tmpfs 5.9G 0 5.9G 0% /dev/shm /dev/sda1 485M 58M 402M 13% /boot top top - 02:44:21 up 2:18, 2 users, load average: 6.54, 3.42, 24.52 Tasks: 416 total, 67 running, 346 sleeping, 2 stopped, 1 zombie Cpu(s): 93.4%us, 6.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Mem: 12186620k total, 12052508k used, 134112k free, 14836k buffers Swap: 8191992k total, 743928k used, 7448064k free, 5517184k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 15904 mysql 20 0 4141m 99m 3640 S 19.5 0.8 0:05.24 mysqld 13710 nobody 20 0 269m 49m 14m R 13.6 0.4 19:45.03 httpd 15367 nobody 20 0 296m 67m 7436 R 13.6 0.6 0:00.59 httpd 15516 nobody 20 0 296m 67m 7392 R 13.6 0.6 0:00.57 httpd 15659 nobody 20 0 312m 83m 7440 R 13.6 0.7 0:00.68 httpd 15933 nobody 20 0 319m 100m 14m R 13.6 0.8 0:01.25 httpd 15939 nobody 20 0 321m 96m 5804 R 13.6 0.8 0:03.67 httpd 15962 nobody 20 0 313m 83m 6400 R 13.6 0.7 0:00.63 httpd 15964 nobody 20 0 316m 87m 5668 R 13.6 0.7 0:00.66 httpd 15998 nobody 20 0 288m 64m 5820 R 13.6 0.5 0:01.26 httpd 15624 nobody 20 0 324m 99m 7532 R 13.2 0.8 0:05.13 httpd 15653 nobody 20 0 297m 71m 6536 R 13.2 0.6 0:01.39 httpd 15684 nobody 20 0 291m 68m 7780 R 13.2 0.6 0:01.22 httpd 15748 nobody 20 0 320m 90m 6616 R 13.2 0.8 0:00.72 httpd 15927 nobody 20 0 318m 89m 5684 R 13.2 0.7 0:00.73 httpd 15984 nobody 20 0 320m 92m 5744 R 13.2 0.8 0:00.79 httpd 16009 nobody 20 0 291m 63m 6504 S 13.2 0.5 0:00.94 httpd 16015 nobody 20 0 287m 64m 5804 R 13.2 0.5 0:03.08 httpd 15716 nobody 20 0 292m 63m 5724 R 12.9 0.5 0:00.49 httpd 15739 nobody 20 0 291m 67m 7512 R 12.9 0.6 0:01.25 httpd 15743 nobody 20 0 289m 65m 5840 R 12.9 0.6 0:01.16 httpd 15923 nobody 20 0 314m 97m 13m R 12.9 0.8 0:01.14 httpd 15936 nobody 20 0 297m 73m 5804 R 12.9 0.6 0:04.17 httpd The original my.cnf was empty. So i tried googling on how to optimize my mysql and found many threads and copy paste others .my.cnf taken from this thread http://forums.cpanel.net/f5/cpu-memory-mysql-usage-48398.html my.cnf [mysqld] safe-show-database skip-locking skip-innodb max_connections = 650 key_buffer = 16M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 2M table_cache = 1280 thread_cache_size = 64 wait_timeout = 1200 connect_timeout = 10 tmp_table_size = 64M max_allowed_packet = 16M max_connect_errors = 10 query_cache_limit = 1M query_cache_size = 32M query_cache_type = 1 query_prealloc_size = 16384 query_alloc_block_size = 16384 [mysqld_safe] open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M I dont know how to get iotop.
Increase Key buffer to 512MB 16MB is way to low. Give the following a try: mysqltuner.pl www.day32.com/MySQL/ That should help you with my.cnf tuning. How many CPUs? The MySQL service does not look to be your problem its only at 19% CPU from the top. However, Apache looks to be overloading the system more. What is your apache/php configuration and settings? The I/O looks like it might be coming from SWAPiness which is probably from Apache using up all the RAM.
As you mentioned in the subject "Mysql overloading" therefore I would like to recommend you, do not make any changes in the my.cnf without checking servers available resources, use the "MySQLTuner" script it will provide you the correct settings for your server.
Thank you guys for the replies. Looks like i fixed it by installing litespeed. Everything is stable now top - 18:59:27 up 18:33, 1 user, load average: 1.22, 0.77, 0.63 Tasks: 227 total, 3 running, 224 sleeping, 0 stopped, 0 zombie Cpu(s): 11.4%us, 0.9%sy, 0.0%ni, 87.6%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 12186620k total, 3343272k used, 8843348k free, 45144k buffers Swap: 8191992k total, 105100k used, 8086892k free, 2410684k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 6884 panime 20 0 138m 46m 23m S 21.6 0.4 0:16.45 lsphp5 6994 panime 20 0 139m 47m 23m S 14.6 0.4 0:03.77 lsphp5 6993 panime 20 0 139m 47m 23m S 14.3 0.4 0:05.16 lsphp5 6998 fave 20 0 136m 44m 22m S 13.3 0.4 0:01.43 lsphp5 6982 biker 20 0 133m 42m 22m R 11.0 0.4 0:01.46 lsphp5 6995 fave 20 0 136m 44m 22m S 8.3 0.4 0:01.82 lsphp5 6883 panime 20 0 138m 46m 23m S 7.3 0.4 0:11.42 lsphp5 19891 mysql 20 0 1875m 175m 4228 S 5.0 1.5 28:59.99 mysqld 6976 panime 20 0 139m 47m 23m S 1.7 0.4 0:09.56 lsphp5 2323 nobody 1 -19 129m 20m 5764 S 1.0 0.2 0:54.86 litespeed 2324 nobody 1 -19 49888 5360 516 S 0.3 0.0 0:02.64 litespeed 7000 fave 20 0 136m 44m 22m S 0.3 0.4 0:02.25 lsphp5 1 root 20 0 19328 812 588 S 0.0 0.0 0:01.47 init 2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd 3 root RT 0 0 0 0 S 0.0 0.0 0:06.72 migration/0 4 root 20 0 0 0 0 S 0.0 0.0 0:03.30 ksoftirqd/0 5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0 I enable memcache, eaccelator but the problem is usually the cached is eating my memory slowly, is it normal? i had to flushed it every time my free memory reaches 2MB. So i have to monitor it every 2 hours. Can cron job do that automatically? How to do that?
Linux by default will use up all the memory it can possible to enhance performance. If you think those two service are eating up too much ram you can tweak their values.
I use php script on the server to periodically stop processes which have been running over 1 hour, something likes: <?php $queryProcessTimeLimit = 3600; require_once 'config.php'; ini_set('display_startup_errors', 1); ini_set('display_errors', 1); require_once FAST_PATH . '/Db.php'; $db = Fast_Db::getInstance(); $sql = 'SHOW FULL PROCESSLIST'; $processes = $db->fetchAll($sql); foreach($processes as $process) { if ($process['Time'] > $queryProcessTimeLimit ) { $processId = $process['Id']; $sql = 'KILL ' . $processId; $db->exec($sql); } } ?> Fast_Db is a PHP class, you can replace it with your favorite lib. If you need cron job service, try easycron.com.
I would like to recommend you please do not use the "memcache, eaccelator" on the same server, use any one and please copy and paste the output of following command. free -m