Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

MySQL optimization or why the server is worsened

sergibondarenkosergibondarenko EntrantInactive User Role Beginner
Hello

Please advise me 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 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?














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 *


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 *


The list of MySQL tables and their engines can be seen in *


MySQL global variables in *


The MySQL server hardware settings in *


*All files can be downloaded by the link http://rapidshare.com/files/3386491301/alldata.zip

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    I think you should start by focusing on those things:

    - You have default value of innodb_log_file_size - 5MB per each. This is really bad setting for write performance in InnoDB. You should change to like 256MB, or more if you upgrade from MySQL 5.1 to 5.5 (due to InnoDB recovery speed).

    - I can see pretty suspicious queries in processlist snapshots:

    SELECT login, name, extension FROM pbx.operators WHERE uid IN (SELECT uid FROM pbx.group_abon WHERE gid IN (SELECT gid FROM pbx.groups WHERE userfield IN (5) GROUP BY gid) GROUP BY uid)

    and

    SELECT `id`, `login`, `state`, `login_date`, `timestamp` FROM pbx.callcenter WHERE stat='1' AND `login` IN (SELECT `login` FROM pbx.operators WHERE `uid` IN (SELECT `uid` FROM pbx.group_abon WHERE `gid` IN (6205, 6206, 6207) AND gid LIKE '%')) ORDER BY login_date

    You should check their query plan with EXPLAIN, those queries might need to be rewritten.


    - Another thing is that you use MyISAM engine for most of your tables. Note that MyISAM doesn't really scale much and your decent hardware with 16-cores Xeon won't help much under high concurrent load. Not mentioning of all other differences between MyISAM and InnoDB. So I think you should consider moving to InnoDB.
    Here is some lecture you might be insterested in:
    http://www.percona.com/files/presentations/percona-live/nyc-2011/PerconaLiveNYC2011-Switching-to-Innodb-from-MyISAM.pdf
    https://blogs.oracle.com/MySQL/entry/comparing_innodb_to_myisam_performance
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.