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

[Walter Klomp]

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
>  
> 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/pipermail/spce-user_lists.sipwise.com/attachments/20170629/238e5084/attachment-0001.html>