Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » Performance issues
Performance issues [message #2560] Thu, 31 January 2008 03:04 Go to next message
arthurica is currently offline arthurica
Messages: 7
Registered: January 2008
Junior Member
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/mysql
socket=/var/lib/mysql/mysql.sock

ft_min_word_len = 1
ft_stopword_file =

max_heap_table_size = 64M
max_connections = 1000
max_allowed_packet = 12M
group_concat_max_len = 4096000
wait_timeout = 30

key_buffer = 1024M

sort_buffer_size = 4M
join_buffer_size = 8M
read_buffer_size = 16M
myisam_sort_buffer_size = 32M

thread_concurrency = 4
thread_cache = 32
table_cache = 2048

query_cache_limit = 2M
query_cache_size = 256M
query_cache_type = 1

query_prealloc_size = 256K
query_alloc_block_size = 96K
read_rnd_buffer_size = 8M
tmp_table_size = 64M
thread_stack = 2M

default-character-set = utf8

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-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 Smile
Thanks a lot
Report message to a moderator
Re: Performance issues [message #2564 is a reply to message #2560 ] Thu, 31 January 2008 10:36 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 323
Registered: March 2007
Location: Sweden
Senior Member
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.
Report message to a moderator
Re: Performance issues [message #2566 is a reply to message #2564 ] Thu, 31 January 2008 11:27 Go to previous messageGo to next message
arthurica is currently offline arthurica
Messages: 7
Registered: January 2008
Junior Member
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
Report message to a moderator
Re: Performance issues [message #2567 is a reply to message #2560 ] Thu, 31 January 2008 12:48 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 323
Registered: March 2007
Location: Sweden
Senior Member
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.
Report message to a moderator
Re: Performance issues [message #2573 is a reply to message #2567 ] Fri, 01 February 2008 02:54 Go to previous messageGo to next message
arthurica is currently offline arthurica
Messages: 7
Registered: January 2008
Junior Member
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.. Smile
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

http://www.day32.com/MySQL/

Not sure if you or anyone else had used that but I think it might help
Report message to a moderator
Re: Performance issues [message #2576 is a reply to message #2560 ] Fri, 01 February 2008 04:56 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 323
Registered: March 2007
Location: Sweden
Senior Member
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.
Report message to a moderator
Re: Performance issues [message #2577 is a reply to message #2573 ] Fri, 01 February 2008 05:18 Go to previous messageGo to next message
arthurica is currently offline arthurica
Messages: 7
Registered: January 2008
Junior Member
Here is the vmstat

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 7  0  12492 612644 212288 4176648    3    3    76   127   12     3 65  8 22  5


and top

top - 12:09:49 up 3 days,  4:03,  2 users,  load average: 9.81, 11.87, 14.53
Tasks: 145 total,  13 running, 132 sleeping,   0 stopped,   0 zombie
Cpu(s): 87.0% us, 12.1% sy,  0.0% ni,  0.5% id,  0.2% wa,  0.2% hi,  0.0% si
Mem:   6227900k total,  5644480k used,   583420k free,   213476k buffers
Swap:  1044208k total,    12492k used,  1031716k free,  4221476k cached

  PID USER      PR  NI %CPU    TIME+  %MEM  VIRT  RES  SHR S COMMAND
22194 apache    25   0   23   0:00.69  0.4 30900  21m  17m R convert
21989 apache    16   0   20   0:01.87  0.2 30364  13m 3192 R httpd
18248 apache    16   0   19   0:30.25  0.2 31360  14m 3296 S httpd
20446 apache    15   0   15   0:10.50  0.2 29676  12m 3276 S httpd
21723 apache    15   0   14   0:02.81  0.2 30364  13m 3244 S httpd
18827 apache    15   0   13   0:27.03  0.2 30532  13m 3276 S httpd
27220 mysql     15   0   13  28:08.44  9.7 2224m 590m 4664 S mysqld
20445 apache    15   0   12   0:14.18  0.2 30384  13m 3284 S httpd
20589 apache    15   0   12   0:12.94  0.2 30580  13m 3284 S httpd
21156 apache    15   0   12   0:07.65  0.2 30400  13m 3240 S httpd
19133 apache    15   0   11   0:29.02  0.2 30300  13m 3280 S httpd
19818 apache    15   0   11   0:11.86  0.2 30388  13m 3276 S httpd
20441 apache    15   0   11   0:13.47  0.2 30360  13m 3272 S httpd
20333 apache    15   0   11   0:17.50  0.2 30452  13m 3272 S httpd
20339 apache    15   0   11   0:13.61  0.2 30236  13m 3260 S httpd
19175 apache    15   0   10   0:25.20  0.2 30344  13m 3272 S httpd
16798 apache    16   0   10   0:52.30  0.2 30236  13m 3288 S httpd
20587 apache    15   0    9   0:12.63  0.2 30240  13m 3268 R httpd
20435 apache    15   0    9   0:05.97  0.2 30248  13m 3272 S httpd
16647 apache    16   0    8   0:41.78  0.2 30920  13m 3296 R httpd
16800 apache    16   0    8   0:43.65  0.2 30616  13m 3288 R httpd
20447 apache    15   0    8   0:10.98  0.2 30152  13m 3260 S httpd
20597 apache    16   0    8   0:11.99  0.2 30520  13m 3284 S httpd
20412 apache    16   0    8   0:10.53  0.2 30440  13m 3268 S httpd
18042 apache    15   0    7   0:43.17  0.2 30316  13m 3304 S httpd
20250 apache    15   0    7   0:19.18  0.2 30196  13m 3304 S httpd
20300 apache    15   0    7   0:17.60  0.2 30076  13m 3280 S httpd
20338 apache    15   0    7   0:12.63  0.2 30588  13m 3284 S httpd
20443 apache    15   0    7   0:15.22  0.2 30248  13m 3248 S httpd
20581 apache    15   0    7   0:09.19  0.2 30392  13m 3272 S httpd
20582 apache    16   0    6   0:11.76  0.2 30652  13m 3236 R httpd
17587 apache    17   0    5   0:47.21  0.2 31132  14m 3288 R httpd
20590 apache    15   0    2   0:06.56  0.2 29976  12m 3256 S httpd
22214 exim      19   0    2   0:00.06  0.1 12040 4548 1080 R exim
20575 apache    15   0    1   0:12.55  0.2 30360  13m 3280 S httpd
20578 apache    15   0    1   0:11.13  0.2 30560  13m 3260 S httpd
21205 apache    15   0    1   0:04.93  0.2 29812  12m 3256 S httpd
19285 apache    15   0    1   0:28.52  0.2 30036  13m 3288 S httpd
21457 root      16   0    1   0:00.29  0.0  2332 1048  780 R top
  483 root      15   0    0   5:53.11  0.0     0    0    0 S kjournald
18662 apache    15   0    0   0:32.25  0.2 30376  13m 3288 S httpd
19171 apache    15   0    0   0:26.00  0.2 30084  13m 3284 R httpd
19184 apache    15   0    0   0:34.71  0.2 30216  13m 3292 S httpd
20128 apache    15   0    0   0:13.72  0.2 30244  13m 3252 S httpd
20303 apache    15   0    0   0:11.14  0.2 30336  13m 3280 S httpd
20320 apache    16   0    0   0:17.68  0.2 30452  13m 3268 S httpd
20427 apache    15   0    0   0:11.95  0.2 30292  13m 3272 S httpd
21558 apache    15   0    0   0:03.59  0.2 30012  12m 3248 S httpd
    1 root      16   0    0   0:15.59  0.0  2660  548  468 S init


The server is pretty slowly at this time
Report message to a moderator
Re: Performance issues [message #2578 is a reply to message #2560 ] Fri, 01 February 2008 07:36 Go to previous messageGo to next message
sterin is currently offline sterin
Messages: 323
Registered: March 2007
Location: Sweden
Senior Member
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.
Report message to a moderator
Re: Performance issues [message #2579 is a reply to message #2577 ] Fri, 01 February 2008 07:45 Go to previous message
arthurica is currently offline arthurica
Messages: 7
Registered: January 2008
Junior Member
The application is pretty complex but it needs to be optimised indeed. Thanks a lot for your feedback
Report message to a moderator
Previous Topic:Poor performance on joins after MYSQL 4 -> 5 migration
Next Topic:MyISAM vs InnoDB on Windows with limited memory
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]


Current Time: Fri Nov 21 00:38:08 EST 2008

Total time taken to generate the page: 0.07891 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge