1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Rep Power

    MySQL optimization or why the server is worsened


    Advise me please how can I optimize my MySQL server. Or advise which way to look. Maybe someone had similar problems?

    Over the past two weeks the MySQL server dropped 2 times. I began searching for ways to optimize the server.

    Overall the picture like that:
    There is a separate MySQL server (Debian), it stores the information for the call center built on top of the Asterisk open source IP PBX (separate server). Almost all information of the call center stored in the MySQL: call queues, agents, IVR values, CDR etc. The Asterisk constantly interacts with the MySQL server to get or to put info. Average number of phone calls for the system is about 70 calls simultaneously.

    Recently I observed a delay of the redistribution of clients for a free call center agents. A delay was increased from 5 second up to 60 seconds and sometimes even more. I suspected InnoDB locks and transactions deadlocks. And then the MySQL server started falling.

    In SHOW ENGINE INNODB STATUS\G I see a lot of locks and transactions that hang over 40 sec but I can not figure out hot to deal with them. And is it necessary?
    2721452-*** (2) TRANSACTION:
    2721453:TRANSACTION 0 1345044118, ACTIVE 45 sec, process no 23678, OS thread id 140176187664128 starting index read, thread declared inside InnoDB 0
    2721454-mysql tables in use 1, locked 1
    2721455-10 lock struct(s), heap size 3024, 7 row lock(s), undo log entries 2
    Also in the output is a bunch of information but I do not know what conclusions could be made for those values. Please look and tell me what values are most suspicious on your point of view.
    Complete log of half a month can be found in the file *show_engine_innodbAndProc_stat_10Apr2013

    Peak CPU load by mysqld is quit high as can be seen from the graphs. Memory and disk are not heavily loaded. See all the graphics in *graphs
    The list of MySQL tables and their engines can be seen in *mysql_engines
    MySQL global variables in *mysqlGvars.txt
    The MySQL server hardware settings in *phys_mysql_server

    *All files can be downloaded by the link rapidshare.com/files/3386491301/alldata.zip
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Stockholm, Sweden
    Rep Power
    SHOW CREATE TABLE pbx.callcenter;
    And post the output here inside [code] tags.

    What does your disk setup look like?
    As far as I can figure out from your files you only have 2 disks connected connected to the MR9260-4i controller.
    The question is if you have a battery backed up cache on the disk controller used for write back?
    The reason I'm asking is explained in point 3.

    Because if you don't have a battery backed up cache on the RAID controller then changing this:
    innodb_flush_log_at_trx_commit 1
    innodb_flush_log_at_trx_commit 2
    which will probably give you a huge difference.
    With this set to 1 without a cache you will only be able to insert/update/delete about 100-150 queries per second.
    The drawback with setting it to 2 is that if the server dies from power failure you can't be sure that you don't loose a query. But the performance benefit is usually worth it.

    And related to the one above you should change these settings also:
    innodb_log_buffer_size 1048576
    innodb_log_file_size 5242880
    to something like:
    innodb_log_buffer_size 8M
    innodb_log_file_size 64M
    But changing the innodb log files is a bit tricky so read the last _two_ paragraphs on this page:

    And then you have some settings here that are just ridiculously large:
    read_buffer_size 536870912
    read_rnd_buffer_size 536870912
    sort_buffer_size 2097152000
    Which I would set to something like:
    read_buffer_size 2M
    read_rnd_buffer_size 16M
    sort_buffer_size 2M
    Because these settings are _per_session_ and not global settings. And making them much larger than that is usually not beneficial.

    Let me know how it works out

IMN logo majestic logo threadwatch logo seochat tools logo