GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Performance issues

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance issues

    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.

    Comment


    • #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

      Comment


      • #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.

        Comment


        • #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

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

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

          Comment


          • #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.

            Comment


            • #7
              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.53Tasks: 145 total, 13 running, 132 sleeping, 0 stopped, 0 zombieCpu(s): 87.0% us, 12.1% sy, 0.0% ni, 0.5% id, 0.2% wa, 0.2% hi, 0.0% siMem: 6227900k total, 5644480k used, 583420k free, 213476k buffersSwap: 1044208k total, 12492k used, 1031716k free, 4221476k cached PID USER PR NI %CPU TIME+ %MEM VIRT RES SHR S COMMAND22194 apache 25 0 23 0:00.69 0.4 30900 21m 17m R convert21989 apache 16 0 20 0:01.87 0.2 30364 13m 3192 R httpd18248 apache 16 0 19 0:30.25 0.2 31360 14m 3296 S httpd20446 apache 15 0 15 0:10.50 0.2 29676 12m 3276 S httpd21723 apache 15 0 14 0:02.81 0.2 30364 13m 3244 S httpd18827 apache 15 0 13 0:27.03 0.2 30532 13m 3276 S httpd27220 mysql 15 0 13 28:08.44 9.7 2224m 590m 4664 S mysqld20445 apache 15 0 12 0:14.18 0.2 30384 13m 3284 S httpd20589 apache 15 0 12 0:12.94 0.2 30580 13m 3284 S httpd21156 apache 15 0 12 0:07.65 0.2 30400 13m 3240 S httpd19133 apache 15 0 11 0:29.02 0.2 30300 13m 3280 S httpd19818 apache 15 0 11 0:11.86 0.2 30388 13m 3276 S httpd20441 apache 15 0 11 0:13.47 0.2 30360 13m 3272 S httpd20333 apache 15 0 11 0:17.50 0.2 30452 13m 3272 S httpd20339 apache 15 0 11 0:13.61 0.2 30236 13m 3260 S httpd19175 apache 15 0 10 0:25.20 0.2 30344 13m 3272 S httpd16798 apache 16 0 10 0:52.30 0.2 30236 13m 3288 S httpd20587 apache 15 0 9 0:12.63 0.2 30240 13m 3268 R httpd20435 apache 15 0 9 0:05.97 0.2 30248 13m 3272 S httpd16647 apache 16 0 8 0:41.78 0.2 30920 13m 3296 R httpd16800 apache 16 0 8 0:43.65 0.2 30616 13m 3288 R httpd20447 apache 15 0 8 0:10.98 0.2 30152 13m 3260 S httpd20597 apache 16 0 8 0:11.99 0.2 30520 13m 3284 S httpd20412 apache 16 0 8 0:10.53 0.2 30440 13m 3268 S httpd18042 apache 15 0 7 0:43.17 0.2 30316 13m 3304 S httpd20250 apache 15 0 7 0:19.18 0.2 30196 13m 3304 S httpd20300 apache 15 0 7 0:17.60 0.2 30076 13m 3280 S httpd20338 apache 15 0 7 0:12.63 0.2 30588 13m 3284 S httpd20443 apache 15 0 7 0:15.22 0.2 30248 13m 3248 S httpd20581 apache 15 0 7 0:09.19 0.2 30392 13m 3272 S httpd20582 apache 16 0 6 0:11.76 0.2 30652 13m 3236 R httpd17587 apache 17 0 5 0:47.21 0.2 31132 14m 3288 R httpd20590 apache 15 0 2 0:06.56 0.2 29976 12m 3256 S httpd22214 exim 19 0 2 0:00.06 0.1 12040 4548 1080 R exim20575 apache 15 0 1 0:12.55 0.2 30360 13m 3280 S httpd20578 apache 15 0 1 0:11.13 0.2 30560 13m 3260 S httpd21205 apache 15 0 1 0:04.93 0.2 29812 12m 3256 S httpd19285 apache 15 0 1 0:28.52 0.2 30036 13m 3288 S httpd21457 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 kjournald18662 apache 15 0 0 0:32.25 0.2 30376 13m 3288 S httpd19171 apache 15 0 0 0:26.00 0.2 30084 13m 3284 R httpd19184 apache 15 0 0 0:34.71 0.2 30216 13m 3292 S httpd20128 apache 15 0 0 0:13.72 0.2 30244 13m 3252 S httpd20303 apache 15 0 0 0:11.14 0.2 30336 13m 3280 S httpd20320 apache 16 0 0 0:17.68 0.2 30452 13m 3268 S httpd20427 apache 15 0 0 0:11.95 0.2 30292 13m 3272 S httpd21558 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

              Comment


              • #8
                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.

                Comment


                • #9
                  The application is pretty complex but it needs to be optimised indeed. Thanks a lot for your feedback

                  Comment

                  Working...
                  X