Take the 2-minute tour ×
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:

[mysqld]

log-error=/var/log/mysqld.log
log_warnings = 1
user=mysql
back_log = 75
max_connections = 50000
interactive_timeout=1000
skip_innodb
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
symbolic-links=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
thread_concurrency = 128
net_read_timeout=2400

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

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

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
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.

Regards,

share|improve this 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: github.com/Star2Billing/a2billing/blob/master/AGI/a2billing.php 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.