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

[Rene Krenn]

hi walter,

the thing is that the my.cnf params have less effect compared to make
queries hit properly prepared indexes, or refactoring queries
explicitly. mysql's creation of temp tables during reqular queries is
typical if it has hard times to solve a select.

probably this is also the case with your setup and queries against
kamailio.location (again).

can you sent me which queries show up as slow?

regards, rene

On Thu, 2017-06-29 at 22:46 +0800, Walter Klomp wrote:
> 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.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user