MySQL Causing a few problems...

[Wondering Soul]

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 :P

Processor: Intel G5 Xeon 2830 Dual Quad Core (8 core)
Memory: 8 GB 667 Mhz DDR2 PC2-5300
Hard Disks: 8 x 73 GB Pluggable SAS SFF 10,000 RPM UHD
Running: 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 :)

[Bono]

Looks like you need to optimize mysql server, and this doesn't look as it is IPB related.

[Velvet Elvis]

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.

[Wondering Soul]

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!

[Bono]

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

[Wondering Soul]

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.

[Bono]

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.

[Wondering Soul]

Ok thank you.

I have changed that information and restarted MySQL. Ill wait for a peak time and see what happens!

Thanks again :)

[Bono]

During peak times grab free -m information also.

[Wondering Soul]

Yes I was planning on doing that.

Ill grab free -m and a top just for the sake of it.

[Wondering Soul]

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!

[Bono]

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)

[Wondering Soul]

Ok will do!

Thank you very much for your continued help :)

[Bono]

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