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

[Walter Klomp]

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/dfa44a95/attachment.html>