Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Centos 6.4 under 2.6.32-358.18.1.el6.x86_64, running Asterisk 11& A2billing in real-time switching with Mysql 5.5.33.

My Server it's a Dell R420 having dual Intel(R) Xeon(R) CPU E5-2470 0 @ 2.30GHz, 32 Cores, and 32 Bbs Ram.

My problem is I'm facing serious mysql delay in processing requests in peak hours, where I'm finding over 30 seconds delay in process calls, as asterisk agi need to run about 10 queries, into 10 tables in the DB before processing a call...

Below is my.cnf configuration:


log_warnings = 1
back_log = 75
max_connections = 50000
key_buffer_size = 384M
myisam_sort_buffer_size = 1028M
join_buffer_size = 1M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 1028
thread_cache_size = 32
wait_timeout = 7200
connect_timeout = 40
tmp_table_size = 768M
max_heap_table_size = 768M
max_allowed_packet = 1024M
max_connect_errors = 5000
read_rnd_buffer_size = 1024M
bulk_insert_buffer_size = 160M
query_cache_limit = 8098M
query_cache_size =8048M
query_cache_type = 1
query_prealloc_size = 65536
query_alloc_block_size = 131072
default-storage-engine = MyISAM
thread_concurrency = 128

Below is my load in not so overloaded hour:

147 active SIP dialogs

uptime 19:02:21 up 6 days, 13:53, 1 user, load average: 1.63, 1.20, 0.90


top - 19:05:34 up 6 days, 13:56, 1 user, load average: 0.33, 1.08, 0.95 Tasks: 674 total, 2 running, 672 sleeping, 0 stopped, 0 zombie Cpu(s): 5.8%us, 1.3%sy, 0.0%ni, 92.8%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 32829392k total, 19988176k used, 12841216k free, 218132k buffers Swap: 92371960k total, 10272k used, 92361688k free, 13327708k cached

13976 mysql 20 0 36.1g 4.1g 6552 S 196.3 13.0 36:17.48 mysqld
19032 root -11 0 17.1g 575m 15m S 36.5 1.8 3031:40 asterisk

Please, what I'm really bad calculating in this, I'm confused in several values, here... and I'd even require a Professional help in this, please if any are available for help, professionally, I'd really appreciate immediate intervention and advice.


share|improve this question

closed as off-topic by Tom V, RolandoMySQLDBA, mustaccio, RLF, Max Vernon Nov 19 at 1:23

This question appears to be off-topic. The users who voted to close gave these specific reasons:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – mustaccio, RLF, Max Vernon
  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Tom V, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

Maybe you should also post these information "create table statements" and some querys with there explain here – Raymond Nijland Oct 5 '13 at 15:23
Thank you for your fast replay Raymond, the script which is handling all queries is a php agi script by a2billing, it's this one: as you can see, it's doing multiple queries at the same time: select,show,create view,insert and update, all at the same time in multiple threads... – user128057 Oct 5 '13 at 19:10
You are lucky i can read that because i'am an PHP programmeur with great knowledge off the MySQL source code and documenation.. but the problem looks like an PHP programmeur what doens't understand the MySQL database and floods it with querys or incorrect indexes what makes MySQL slow.. – Raymond Nijland Oct 5 '13 at 19:22
Raymond, sorry, I'm new in this forum, and I don't see any way to send a PM, can we discuss that, to try to figure it out professionally? – user128057 Oct 5 '13 at 19:29
NP but this is an open forum and there is no PM i know off.. – Raymond Nijland Oct 5 '13 at 19:30

Browse other questions tagged or ask your own question.