MySQL Tuning - Query Cache

[Rudolfmdlt]

Hello All,

Running 150 agents on a DL 380 GEN 8e with 10k SAS RAID 1+0.

We where plagued by mysql lockups causing havoc.

What we found was increasing the query cache from 128M to 256M or 512M killed the call centre within minutes.

When we set the query cache to 4M, all the problems went away.

Is it safe to disable the query cache completely?

Regards,

Rudolf

[mflorell]

How much RAM is on your system?

What CPU(s) are you using?

What RAID hardware are you using?

[Rudolfmdlt]

Hello Mat,

16 Gigs
Intel Xenon - Cannot check cores atm but > 6 dedicated cores, I believe 12 threads
HP P420i Hardware RAID 512MB Raid Memory

Regards,

Rudolf

[mflorell]

Having such a low query cache usually leads to other performance issues.

One of your issues may be that you are using 10k disks. For larger installations you want to use either 15k disks or SSD. Also, we only recommend "LSI Logic MegaRAID" caching RAID controllers. We have had clients with problems using pretty much every other type of RAID controller at higher agent levels.

[Rudolfmdlt]

Hello Matt,

Could you give me an example of such a problem? I am desperate to understand the MySQL problems under load better.

I would think the query cache would be a "good" thing, but the entire call centre falls apart at 150 agents, no reports. With almost no query cache, we have 150 agents, full predictive, 20 people on real time reports and 5 managers pulling csv reports.

Also, once the query cache locks up and the table start getting locked, it becomes an exponential disaster. We have to kill all the reports, and get half the agents off before the locks free up. With the query cache set to practically unusable, we have a functioning call centre again.

I got a quote from HP for 4 new 15k SAS drives - weighing up RAID 10 15k SAS or RAID 1 SSD. With the prices of SSD's, it would be cheaper to build an entire new cluster on SAS than do RAID 10 SSD.

Our asterisk DB currently sits at 29 Gigs. Is this possibly a problem? We have not deleted any lists in 2 years, we have arched the "active" tables (list, log ect) to T - 2 months. I know MySQl as a developer, but this massive high performance stuff is keeping me on my toes.

Thank you for your input.

Regards,

Rudolf

[mflorell]

The settings for other options in my.cnf may be causing some of your problems, it's hard to say what the exact problem is in your case, but effectively removing the query cache means that you are running every query directly off of the disks, which is something that is unsustainable as you grow your system over 200 agents in most cases.

Most of the problems we find with database servers are pretty simple to fix:
- remove any virtualization or other applications that are running on the same machine
- add more RAM or more CPU cores
- use only LSI Logic MegaRAID cards
- Use SSD or 15k drives
- archive the log tables and the vicidial_list table regularly
- tune the my.cnf file to your hardware and database usage

Over the last 10 years, when the client follows our recommendations, we have fixed every single database issue that has been presented to us.

The problems are when the client doesn't listen to us, then it's more of a battle to get things working properly.

I can't even tell you how much time clients of ours have wasted because they wouldn't listen to us. Like the client that purchased LSI Logic 3ware cards and refused to buy MegaRAID for some reason. They didn't believe us that that was the problem, so they hired Percona to do a full MySQL analysis, and several weeks(and $3000) later, they told our client the same thing we told them. The client finally bought a LSI Logic MegaRAID card, and just like we said, all of their database issues went away.

I have similar stories about clients using virtual servers and shared storage for their databases.

[Rudolfmdlt]

I can't even tell you how much time clients of ours have wasted because they wouldn't listen to us.

-> I feel your pain. For some reason they will hire professionals and then tell the professionals what to do.

Please see replies in line:
- remove any virtualization or other applications that are running on the same machine
---->DONE. No Virtualisation, No Thin Clients.
- add more RAM or more CPU cores
----> Machine Load is still very low. it has 8 cores and load is around 2.4
- use only LSI Logic MegaRAID cards
-----> We cannot get this in South Africa. Will try an order for the next instal
- Use SSD or 15k drives
-----> Ordering 15k Drives.
- archive the log tables and the vicidial_list table regularly
------> Please can you tell me how big your asterisk DB's are in some big call centres that have been running for a couple of years. Do you archive perpetually? What is regularly? Weeks, Months? Currently we are doing 80 000 calls per day with 150 agents and we are keeping 3 months worth of data in the "live" tables. we archive the rest. What is an acceptable size for the live list and log tables in a cluser like this?

Rudolf
- tune the my.cnf file to your hardware and database usage
------> We have tried our best and the tune that has the biggest effect is killing the mysql_cache.

Regards,

Rudolf

[mflorell]

For archiving, it's really the number of records in the tables that are heavily used:
- vicidial_list, we usually try to keep this below 2 million records, but we have some larger clients with over 20 million records in here
- vicidial_log(and the other log tables that can be archived with ADMIN_archive_log_tables.pl), most auto-dialing clients have their archiving set to 12 months, but some clients go as low as 1 month, although that is usually a response to underpowered hardware

As for LSI Logic MegaRAID cards, this company is listed as the distributor in South Africa, have you tried contacting them? http://www.asbis.co.za/

As for my.cnf tuning, there is a utility that is included in Vicidial (extras/mysql-tuning.sh). It's not perfect, but it can help you to tune your settings, just make sure you run it during non-production hours.