Performance issues

MySQL & MariaDB Other MySQL® Questions
1

We are having some performance issues with our current server configuration and I finally found this forum and hope I can get some help

We keep getting many errors like this and they all seem to be related to the same problem

MySQL server has gone away
Got error 12 from storage engine

The server is a dual processor with 6 Gb of RAM
Here is the my.cnf file that we are using

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockft_min_word_len = 1ft_stopword_file =max_heap_table_size = 64Mmax_connections = 1000max_allowed_packet = 12Mgroup_concat_max_len = 4096000wait_timeout = 30key_buffer = 1024Msort_buffer_size = 4Mjoin_buffer_size = 8Mread_buffer_size = 16Mmyisam_sort_buffer_size = 32Mthread_concurrency = 4thread_cache = 32table_cache = 2048query_cache_limit = 2Mquery_cache_size = 256Mquery_cache_type = 1query_prealloc_size = 256Kquery_alloc_block_size = 96Kread_rnd_buffer_size = 8Mtmp_table_size = 64Mthread_stack = 2Mdefault-character-set = utf8[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Is there anything obviously that we are missing? How can I change the variables and see if I am on the right direction

Sorry if some of the questions sound stupid )
Thanks a lot

2

What OS are you running?

The problem you are seeing is that the OS is denying MySQL to allocate more memory.

This is either due to:

  1. No more memory available on the server.
    or
  2. That you are using a 32bit version of the OS.
    Generally 32bit OS versions only allows each process about 2-3Gb and since you are allowing 1GB for key_buffer it isn’t that much memory left if you have a lot of connections against your DB.
3

The server is running CentOS more exactly… Linux taz 2.6.9-67.0.1.ELsmp #1 SMP Wed Dec 19 16:01:12 EST 2007 i686 i686 i386 GNU/Linux

Should I try to decrease the key_buffer and query_cache_size values? We are also using application cache
Do you think the read, sort, join buffer_size values would need any changes

Can you please advice how should I try to test and see which are the optimizal parameters

4

Just make a quick check with “ulimit” that it says unlimited (depends a bit between distributions and I don’t know what CentOS has as default).
After that you can start by reducing some of your variables.

Yes you can reduce the key_buffer_size and query_cache_size if you want to.
But these two variables are global for the entire process and I suggest that you should instead start to look at some of your variables that are allocated per thread instead.
Like for example your settings for the tmp_table and max_heap_table_size, those are pretty big in your config and if you have a lot of connections that allocate there own buffer of these then you can very fast reach the memory limit.

BTW 1
Is this a stand alone DB server or are you running the application on the same server?

BTW 2
How much swap space is configured and how much is the server using of that swap space? Try using “top” to read memory usage and for example “vmstat 2 10” at full load and check the si and so columns, they should be pretty small numbers, if they are large then you should definitely reduce the overall memory consumption of the MySQL process.

5

Yes I tried and it said unlimited. I tried adjusting the suggested variable tmp_table and max_heap_table_size especiall since we are no longer using heap tables on this application

It is running the application on the same server and this is killing it for sure… )
We have no other solution for now since the main server had some hardware problems and we have to replace the mother board so we had to put everything on this server only

BTW I have found an usefull script for checking the configuration variables and suggesting improvements

[URL]http://www.day32.com/MySQL/[/URL]

Not sure if you or anyone else had used that but I think it might help

6

Though break, what output did you get from using top and vmstat?

Because if your server is still swapping you should start reducing some of the other variables also.

The reason is that you want the server to use MySQL to use as much RAM as possible. BUT if it uses too much and the OS starts to swap, performance is degraded A LOT. So it is usually better to have a larger headroom than optimizing to much which means that it starts to swap at peak times (when performance is needed the most).

The worst case scenario is that the server starts to swap so much that basically no real work is performed since the CPU/disks are busy with just swapping in and out active processes to/from RAM.
And at those times you can barely log in to the server.

7

According to your top you are only using about 2GB of memory at the moment. The rest of your 6GB RAM is basically used for OS file cache (which is the cached figure). That is fully normal since Linux tries to use as much free memory as possible for the OS file cache.

IMO you have a pretty small swap partition compared to your amount of RAM, but it’s not a big issue so you don’t have to take immediate action.

But you should really take a look at your PHP application code and see if you can optimize it since the CPU is the bottleneck in this case.
Right now the server doesn’t seem to be no way near any MySQL code 12 errors since you have so much memory available (os file cache is decreased when more memory for other processes is needed).

My suggestion to you is to focus your attention on the PHP application and the Apache logs to see if you can find something about out of memory errors there. Because I think that apache should also have this problem on your server at those times.

And if you can’t find any way to optimize the PHP code then you should upgrade your machine to a faster CPU or dual or quad CPU machine.
Because your CPU is definitely the limit in this case.