[Spce-user] Tuning MySQL due to slow queries on mr4.5.4

[Gavin Sweet]

https://github.com/major/MySQLTuner-perl

 

 

From: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] On Behalf Of Walter Klomp
Sent: 29 June 2017 15:47
To: spce-user at lists.sipwise.com
Subject: [Spce-user] Tuning MySQL due to slow queries on mr4.5.4

 

Hi,

 

I am having some serious performance issues where MySQL server comes to a halt temporarily and registrations fail (SPCE returns 100 trying, then 500 and then 200 after that on the same register request - which is probably not RFC compliant either) 

 

… I have been tweaking the my.cnf configuration (by modifying the my.cnf.customtt.tt2 template) a bit to alleviate it with some success, but I still see the below:

 

using

 

                -- MYSQL PERFORMANCE TUNING PRIMER —

 

 

TEMP TABLES

Current max_heap_table_size = 128 M

Current tmp_table_size = 512 M

Of 71563 temp tables, 45% were created on disk

Effective in-memory tmp_table_size is limited to max_heap_table_size.

Perhaps you should increase your tmp_table_size and/or max_heap_table_size

to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.

If you are using these columns raising these values might not impact your

ratio of on disk temp tables.

 

How big should this be, noting the whole database size is currently around 960Mb (6000+ subscribers).  This is running on Redhat Openstack with CEPH storage (with 20 spinning high speed drives).

 

12 cores and 32GB RAM (of which currently less than half is used). Increasing bufferpoolsize does nothing (as it only needs about 1 Gig and it is set to 4G at the moment)

 

Hope to find some MySQL tuning experts here…

 

Thanks

Walter.

 

 

 


 

	

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/mailman/private/spce-user_lists.sipwise.com/attachments/20170629/5b75dd86/attachment.html>