Wondering Soul Posted April 20, 2008 Share Posted April 20, 2008 Hi, recently I have been having a few problems with MySQL on my server. Before I go into the details, I thought I would post some server info as someone always asks for some specs :PProcessor: Intel G5 Xeon 2830 Dual Quad Core (8 core)Memory: 8 GB 667 Mhz DDR2 PC2-5300Hard Disks: 8 x 73 GB Pluggable SAS SFF 10,000 RPM UHDRunning: Linux/CentOS 5 Final Ok, so the problem I had has now occurred on three separate occasions in the past two days. It seems to occur at times when my forums aren't particularly active ( 80 Guests, 200 Members online in past 15 mins). That amount drastically increases during my peak times and the problem has never occurred then. The last occurrence of this problem forced me to kill mysqld because it was causing the server to very quickly use most of the available RAM that I have, almost to the point where I was finding it hard to log into SSH. After I killed the process and restarted it, all was fine and has been ever since. My forums are the only site on the server that are resource intensive on MySQL, the other sites don't have as many active users and contain less database driven pages. I managed to grab the output of a top command during one of these spikes: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5995 mysql 15 0 00000 0000 0000 S 72 6.9 4:00.64 mysqld 1 root 15 0 2040 652 564 S 0 0.1 0:00.28 init 5798 root 15 0 1700 616 520 S 0 0.1 0:00.08 syslogd 5825 root 15 0 6144 1032 668 S 0 0.1 0:00.00 sshd 5834 root 16 0 2684 884 712 S 0 0.1 0:00.08 xinetd 5842 clamav 18 0 2912 784 612 S 0 0.1 0:00.00 freshclam 5854 tinydns 18 0 1624 324 264 S 0 0.0 0:00.00 tinydns Notice that MySQL isn't performing very well at all! My database is 1.5GB and has its overheads cleared every 4 days automatically, all of the session tables are also kept on HEAP (Memory) storage mode because I have had problems with those tables locking up in the past... So, in relation to IP.Board, would anyone be able to take a wild guess at what could cause that type of spike (that continues to increase until stopped)? Any ideas are welcome :) Link to comment Share on other sites More sharing options...
Bono Posted May 8, 2008 Share Posted May 8, 2008 Looks like you need to optimize mysql server, and this doesn't look as it is IPB related. Link to comment Share on other sites More sharing options...
Velvet Elvis Posted May 8, 2008 Share Posted May 8, 2008 can you post your my.cnf and the output of 'mysqladmin extended-status'? I'll try to remember to check back in here and respond. I'm not an expert on this stuff but I've made it a personal challenge to keep my board running on a 256 meg VPS for as long as I possibly can. Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 8, 2008 Share Posted May 8, 2008 Hi thanks for your responses. This has only happened once more since the time I posted this so its not a huge problem anymore but im wondering what was up. Here is the my.cnf: port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K skip-bdb skip-innodb [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout[mysqld] I can't currently access my server through command line (OS change taking place atm) so I can't run mysqladmin extended-status right now. Ill post it when I can though! Link to comment Share on other sites More sharing options...
Bono Posted May 9, 2008 Share Posted May 9, 2008 Did you made this config? Your goal should be to cache as much as possible of database, and you have plenty of ram so that shouldn't be so big problem. Please paste here results of command: free -m Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 10, 2008 Share Posted May 10, 2008 No I didn't make this config. One of my administrators did it while I was away from home (although after just looking through that, it looks to basically be the default setup...). The results from that command are as follows:[root@******** ~]# free -m total used free shared buffers cached Mem: 8192 3173 5019 0 0 0 -/+ buffers/cache: 3173 5019 Swap: 0 0 0 This was during one of the load spikes as you can probably tell. Link to comment Share on other sites More sharing options...
Bono Posted May 10, 2008 Share Posted May 10, 2008 No I didn't make this config. One of my administrators did it while I was away from home (although after just looking through that, it looks to basically be the default setup...). The results from that command are as follows:[root@******** ~]# free -m total used free shared buffers cached Mem: 8192 3173 5019 0 0 0 -/+ buffers/cache: 3173 5019 Swap: 0 0 0 This was during one of the load spikes as you can probably tell. That is good at least its not hardware issue, you are not caching anything. Add this: thread_concurrency = 16 key_buffer change to at least 256MB table_cache = 1800 read_rnd_buffer_size > change to 1 or 2MB. And check if things have improved at peak times, this is best i can do with so much information. When you finish editing restart mysql server. Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 10, 2008 Share Posted May 10, 2008 Ok thank you. I have changed that information and restarted MySQL. Ill wait for a peak time and see what happens! Thanks again :) Link to comment Share on other sites More sharing options...
Bono Posted May 10, 2008 Share Posted May 10, 2008 During peak times grab free -m information also. Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 10, 2008 Share Posted May 10, 2008 Yes I was planning on doing that. Ill grab free -m and a top just for the sake of it. Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 17, 2008 Share Posted May 17, 2008 Ok well that seems to have helped if not solved my problem!! Here's the info (free -m):[root@********* ~]# free -m total used free shared buffers cached Mem: 8192 2128 6064 0 83 2045 Swap: 0 0 0 My servers performance has improved greatly, so I think that these values are as they should be...not totally sure though! Link to comment Share on other sites More sharing options...
Bono Posted May 18, 2008 Share Posted May 18, 2008 You can always raise key_buffer to 512 or even 768mb, that would probably be good thing because it would lower even more IO. So in next period try to put higher value and observe server how it is working, if is better you can leave it. There is formula for calculation also: MySQL memory used = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size) Link to comment Share on other sites More sharing options...
Wondering Soul Posted May 18, 2008 Share Posted May 18, 2008 Ok will do! Thank you very much for your continued help :) Link to comment Share on other sites More sharing options...
Bono Posted May 19, 2008 Share Posted May 19, 2008 With next update please include two bottom lines of status in mysql (mysql > \s) Like this: Uptime: 109 days 10 hours 28 min 5 sec Threads: 2 Questions: 1101030262 Slow queries: 21 Opens: 11701 Flush tables: 1 Open tables: 1799 Queries per second avg: 116.446 Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.