[Spce-user] Tuning MySQL due to slow queries on mr4.5.4
walter at myrepublic.net
Thu Jun 29 11:30:37 EDT 2017
Yes, following those as well - did improve a bit… just can’t get rid of the huge amount of temp tables being created...
> On 29 Jun 2017, at 11:16 PM, Gavin Sweet <gavin.sweet at simetrictelecom.com> wrote:
> https://github.com/major/MySQLTuner-perl <https://github.com/major/MySQLTuner-perl>
> From: Spce-user [mailto:spce-user-bounces at lists.sipwise.com <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 <mailto:spce-user at lists.sipwise.com>
> Subject: [Spce-user] Tuning MySQL due to slow queries on mr4.5.4
> 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:
> -- 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…
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Spce-user