UNIX and Linux Applications

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 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?

Code:

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

Locks and long transactions (45 sec) says naughty programming, queries or missing indexes (no valid plan). If inside a SP a transaction churns say 5 tables using well indexed plans for all where, you never get much past a second each.

Keeping the RDBMS together is one reason service queues were invented. More than N clients can reduce performance. Using well written SP to do one row churn saves compile/plan time. Having a server reuse the connection for N sequential operations beats N sequential connections, too. JAVA connection pools do this.

Sorry, I don't have quick solutions for what I see in your files.
There are many select statements in state: locked ... Your biggest problem seems to be the storage engine - you're using MyISAM.
You should consider switching to InnoDB, but you should test the migration thoroughly. And you should upgrade to a newer version, 5.1 is may too old.

I suppose your best bet now is to activate the slow log (if I recall correctly, it's dynamic in 5.1) and try to tune your queries, to minimize the locking issue.

By the way, why you're using 2G for the InnoDB buffer pool and only 128M for your MyISAM key buffer? Do you have InnoDB tables
(not mentioned in the uploaded files)?