Large Cluster my.cnf Tuning Help

[mark_flynn]

Hi All,

We currently have a large cluster setup within our company which consists of the following:

Code: Select all

Setup Using PoundTeam Cluster Manual
3 X Asterisk Nodes
2 X Web Nodes
1 x Replicated Stats Server
1 x DB Server
all on VERSION: 2.4-362a BUILD: 120316-1203

The DB server is setup as follows:

Code: Select all

ProLiant BL460c G1
Dual Quad-Core Intel Xeon, 2666 MHz ( load | avg 0.16 )
32768 MB  Memory (usage | avg 569MB)
Business Class Enterprise SAN for hard disks (iostat | await  svctm  %util - 0.17   0.17   0.56)

Also we have HA configured on the DB which fails over the DB to an identical spec server in 2 ping hops


Currently we are trialling this server with 26 Agents on it (ONLY) but have come across some issues which are the following:

Code: Select all

delays in script pop
slow navigation around the admin.php (clicking campaign Detail View more than the rest)
delays in API functions
etc

Looking into the mysql using mysql-tuner.sh, mysql-primer.pl and mysqlreport.pl we have noticed that alot of mysql performance tips and settings across the web have had no impact.

can anyone tell me if there is a problem with my my.cnf file or ways i could improve it

Code: Select all

skip-locking
key_buffer = 1024M
max_allowed_packet = 2M
table_cache = 8192
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16
#log=/var/log/mysqldquery.log
# ViciDial DB-Only Settings
skip-name-resolve
connect_timeout=60
long_query_time = 3
log_slow_queries
log_warnings=0
max_connections=512
open_files_limit=24576
max_heap_table_size = 64M
concurrent_insert=2
query_cache_min_res_unit = 1024 # Use this is you have TONS of small queries
expire_logs_days=3
low_priority_updates=1
join_buffer_size=262144
skip-innodb

Ive also noticed a large number of the slow querys in the log over 3.0 seconds come from vicidial_live_agents, vicidial_log tables has anyone increased/improved the indexes on this table and maybe could share with me what they did.

if you need anymore infomation please ask and ill reply with it.

Many Thanks

Mark Flynn

[mark_flynn]

we are also seeing a spike in locks and activity on the DB server when the AST_cleanup_agent_log.pl runs each hour can i disable this script and just let it run after shift?

[mflorell]

How frequently are you archiving your logs?

[mark_flynn]

Hi Matt,

I have in my my.cnf file expire_logs_days=3

if you mean the /usr/share/astguiclient/ADMIN_archive_log_tables.pl cron job - we currently have this set to inactive on the DB server. but the server has not been running for 2 months anyway.

what are the average locks you see on your DB server we get peaks at around 50-75 every hour but then go back down to below 10.

I now know its not the Agent cleanup pl file because i set it to inactive for an hour and it still peaked.

any further ideas?

Cheers for response.

[mark_flynn]

anyone

Cheers

[mflorell]

How many concurrent agents?

How many records in vicidial_log?

"Business Class Enterprise SAN" tells me nothing specific about your hard-drive situation, why are you not using SAS 15k drives in a RAID 10 on the same machine? This very well could be your problem.

[mark_flynn]

Hi Matt,

currently we are testing this cluster with only 20-25 agents. The vicidial_log table currently has 193k rows in it. and further details on our SAN:

Currently using anhttp://h20000.www2.hp.com/bizsupport/TechSupport/Document.jsp?objectID=c00365593 with SATA Disks but we are in the process of mirgrating it onto an NetApp FAS3210SAN with 24 SAS 15k drives in RAID 6 details found herehttp://www.netapp.com/uk/products/storage-systems/fas3200/fas3200-tech-specs.html

the thing is we are not seeing any peaks in iostat.

also since the last post i have turned off the query_cache on the DB and it seems to have helped but still getting lock peaks per hour (shown below)



cheers

[mflorell]

We always recommend a stand-alone DB server machine with internal SAS/SCSI 15k drives in a RAID 10. We specifically do NOT recommend using RAID5 or RAID6 because of how slow to write they are as well as issues with recovery from failure.

We have built hundreds of systems, many built to handle 100+ seats and we know what works and what doesn't. We just had a case last month of a company with similar issues to yours, using an external "enterprise-class" drive system, and when they changed to internal 15k drives with a RAID-10 through an LSI-Logic MegaRAID card with a caching raid function, all of their DB problems went away.