<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class="">Hi,<div class=""><br class=""></div><div class="">Sent you some queries in a separate email. Did some more tuning and now tuning-primer.sh shows this (probably have to run it tomorrow again and monitor the slow logs).</div><div class="">The temp tables seem to still be created mostly on disk though. Why ? Are there any tables with TEXT and BLOB we should optimise to VARCHAR ?</div><div class=""><br class=""></div><div class="">Note this instance is running off a CEPH storage system. (Redhat)</div><div class=""><br class=""></div><div class=""><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span>-- MYSQL PERFORMANCE TUNING PRIMER --</div><div class=""><span class="Apple-tab-span" style="white-space:pre"> </span> - By: Matthew Montgomery -</div><div class=""><br class=""></div><div class="">MySQL Version 5.5.55-0+deb8u1-log x86_64</div><div class=""><br class=""></div><div class="">Uptime = 0 days 3 hrs 15 min 10 sec</div><div class="">Avg. qps = 22</div><div class="">Total Questions = 258193</div><div class="">Threads Connected = 46</div><div class=""><br class=""></div><div class="">Warning: Server has not been running for at least 48hrs.</div><div class="">It may not be safe to use these recommendations</div><div class=""><br class=""></div><div class="">To find out more information on how each of these</div><div class="">runtime variables effects performance visit:</div><div class=""><a href="http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html" class="">http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html</a></div><div class="">Visit <a href="http://www.mysql.com/products/enterprise/advisors.html" class="">http://www.mysql.com/products/enterprise/advisors.html</a></div><div class="">for info about MySQL's Enterprise Monitoring and Advisory Service</div><div class=""><br class=""></div><div class="">SLOW QUERIES</div><div class="">The slow query log is enabled.</div><div class="">Current long_query_time = 5.000000 sec.</div><div class="">You have 21 out of 258214 that take longer than 5.000000 sec. to complete</div><div class="">Your long_query_time seems to be fine</div><div class=""><br class=""></div><div class="">BINARY UPDATE LOG</div><div class="">The binary update log is enabled</div><div class=""><br class=""></div><div class="">WORKER THREADS</div><div class="">Current thread_cache_size = 64</div><div class="">Current threads_cached = 14</div><div class="">Current threads_per_sec = 0</div><div class="">Historic threads_per_sec = 0</div><div class="">Your thread_cache_size is fine</div><div class=""><br class=""></div><div class="">MAX CONNECTIONS</div><div class="">Current max_connections = 128</div><div class="">Current threads_connected = 46</div><div class="">Historic max_used_connections = 60</div><div class="">The number of used connections is 46% of the configured maximum.</div><div class="">Your max_connections variable seems to be fine.</div><div class=""><br class=""></div><div class="">INNODB STATUS</div><div class="">Current InnoDB index space = 579 M</div><div class="">Current InnoDB data space = 654 M</div><div class="">Current InnoDB buffer pool free = 93 %</div><div class="">Current innodb_buffer_pool_size = 6.00 G</div><div class="">Depending on how much space your innodb indexes take up it may be safe</div><div class="">to increase this value to up to 2 / 3 of total system memory</div><div class=""><br class=""></div><div class="">MEMORY USAGE</div><div class="">Max Memory Ever Allocated : 37.72 G</div><div class="">Configured Max Per-thread Buffers : 67.39 G</div><div class="">Configured Max Global Buffers : 6.13 G</div><div class="">Configured Max Memory Limit : 73.53 G</div><div class="">Physical Memory : 31.36 G</div><div class=""><br class=""></div><div class="">Max memory limit exceeds 90% of physical memory</div><div class=""><br class=""></div><div class="">KEY BUFFER</div><div class="">Current MyISAM index space = 156 K</div><div class="">Current key_buffer_size = 64 M</div><div class="">Key cache miss rate is 1 : 519</div><div class="">Key buffer free ratio = 81 %</div><div class="">Your key_buffer_size seems to be fine</div><div class=""><br class=""></div><div class="">QUERY CACHE</div><div class="">Query cache is enabled</div><div class="">Current query_cache_size = 32 M</div><div class="">Current query_cache_used = 29 M</div><div class="">Current query_cache_limit = 4 M</div><div class="">Current Query cache Memory fill ratio = 91.17 %</div><div class="">Current query_cache_min_res_unit = 4 K</div><div class="">MySQL won't cache query results that are larger than query_cache_limit in size</div><div class=""><br class=""></div><div class="">SORT OPERATIONS</div><div class="">Current sort_buffer_size = 512 M</div><div class="">Current read_rnd_buffer_size = 8 M</div><div class="">Sort buffer seems to be fine</div><div class=""><br class=""></div><div class="">JOINS</div><div class="">Current join_buffer_size = 16.00 M</div><div class="">You have had 57 queries where a join could not use an index properly</div><div class="">join_buffer_size >= 4 M</div><div class="">This is not advised</div><div class="">You should enable "log-queries-not-using-indexes"</div><div class="">Then look for non indexed joins in the slow query log.</div><div class=""><br class=""></div><div class="">OPEN FILES LIMIT</div><div class="">Current open_files_limit = 4234 files</div><div class="">The open_files_limit should typically be set to at least 2x-3x</div><div class="">that of table_cache if you have heavy MyISAM usage.</div><div class="">Your open_files_limit value seems to be fine</div><div class=""><br class=""></div><div class="">TABLE CACHE</div><div class="">Current table_open_cache = 2048 tables</div><div class="">Current table_definition_cache = 400 tables</div><div class="">You have a total of 343 tables</div><div class="">You have 392 open tables.</div><div class="">The table_cache value seems to be fine</div><div class=""><br class=""></div><div class=""><font color="#ff2600" class="">TEMP TABLES</font></div><div class=""><font color="#ff2600" class="">Current max_heap_table_size = 512 M</font></div><div class=""><font color="#ff2600" class="">Current tmp_table_size = 512 M</font></div><div class=""><font color="#ff2600" class="">Of 13208 temp tables, 39% were created on disk</font></div><div class=""><font color="#ff2600" class="">Perhaps you should increase your tmp_table_size and/or max_heap_table_size</font></div><div class=""><font color="#ff2600" class="">to reduce the number of disk-based temporary tables</font></div><div class=""><font color="#ff2600" class="">Note! BLOB and TEXT columns are not allow in memory tables.</font></div><div class=""><font color="#ff2600" class="">If you are using these columns raising these values might not impact your</font></div><div class=""><font color="#ff2600" class="">ratio of on disk temp tables.</font></div><div class=""><br class=""></div><div class="">TABLE SCANS</div><div class="">Current read_buffer_size = 2 M</div><div class="">Current table scan ratio = 264 : 1</div><div class="">read_buffer_size seems to be fine</div><div class=""><br class=""></div><div class="">TABLE LOCKING</div><div class="">Current Lock Wait ratio = 0 : 258520</div><div class="">Your table locking seems to be fine</div><div class=""><br class=""></div><div class="">
<div style="color: rgb(0, 0, 0); letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="346" style="font-family: Helvetica; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; width: 259.8pt;"><tbody class=""><tr class=""><td valign="top" style="padding: 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><br class=""></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class="">Warmest Regards,</div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="color: purple; text-decoration: none;" class=""><a href="https://myrepublic.com.sg/" target="_blank" style="color: purple;" class=""><br class="Apple-interchange-newline"><img border="0" width="140" height="92" id="_x0000_i1027" src="https://myrepublic.com.sg/resources/email-signature.png" class=""></a></span><o:p class=""></o:p></div></td><td style="padding: 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><b class=""><span style="font-size: 10pt; font-family: Arial; color: rgb(127, 43, 153);" class="">Walter Klomp</span></b><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">Head of Voice</span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">MyRepublic Limited</span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">T: +65 6816 1120</span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">F: +65 6717 2031</span></div></td></tr><tr class=""><td width="333" colspan="2" style="width: 249.75pt; padding: 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""> <o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">MyRepublic Limited</span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">33 Ubi Ave 3, Vertex Tower B, #04-13 Singapore 408868</span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class=""><br class=""></span><span style="color: rgb(77, 77, 77);" class=""><a href="https://myrepublic.com.sg/" target="_blank" style="color: purple;" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">myrepublic.com.sg</span></a></span><o:p class=""></o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: 'Times New Roman';" class=""><span style="font-size: 8.5pt; font-family: Arial; color: rgb(77, 77, 77);" class="">Follow us on: <a href="https://twitter.com/myrepublic" target="_blank" style="color: purple;" class=""><span style="color: rgb(77, 77, 77);" class="">Twitter</span></a> | <a href="https://facebook.com/myrepublicsg" target="_blank" style="color: purple;" class=""><span style="color: rgb(77, 77, 77);" class="">Facebook</span></a> | <a href="https://www.linkedin.com/company/myrepublic" style="color: purple;" class=""><span style="color: rgb(5, 99, 193);" class="">LinkedIn</span></a></span></div></td></tr></tbody></table><div style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 14px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;" class=""><br class=""></div><table class="x_MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="400" style="font-family: Helvetica; letter-spacing: normal; orphans: auto; text-indent: 0px; text-transform: none; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px; width: 259.8pt;"><tbody class=""><tr class=""><td valign="top" class="" style="padding: 0in;"><br class=""></td></tr></tbody></table></div>
</div>
<br class=""><div><blockquote type="cite" class=""><div class="">On 29 Jun 2017, at 11:06 PM, Rene Krenn <<a href="mailto:rkrenn@sipwise.com" class="">rkrenn@sipwise.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">hi walter,<br class=""><br class="">the thing is that the my.cnf params have less effect compared to make<br class="">queries hit properly prepared indexes, or refactoring queries<br class="">explicitly. mysql's creation of temp tables during reqular queries is<br class="">typical if it has hard times to solve a select.<br class=""><br class="">probably this is also the case with your setup and queries against<br class="">kamailio.location (again).<br class=""><br class="">can you sent me which queries show up as slow?<br class=""><br class="">regards, rene<br class=""><br class="">On Thu, 2017-06-29 at 22:46 +0800, Walter Klomp wrote:<br class=""><blockquote type="cite" class="">Hi,<br class=""><br class=""><br class="">I am having some serious performance issues where MySQL server comes<br class="">to a halt temporarily and registrations fail (SPCE returns 100 trying,<br class="">then 500 and then 200 after that on the same register request - which<br class="">is probably not RFC compliant either) <br class=""><br class=""><br class="">… I have been tweaking the my.cnf configuration (by modifying the<br class="">my.cnf.customtt.tt2 template) a bit to alleviate it with some success,<br class="">but I still see the below:<br class=""><br class=""><br class="">using<br class=""><br class=""><br class="">-- MYSQL PERFORMANCE TUNING PRIMER —<br class=""><br class=""><br class=""><br class=""><br class="">TEMP TABLES<br class="">Current max_heap_table_size = 128 M<br class="">Current tmp_table_size = 512 M<br class="">Of 71563 temp tables, 45% were created on disk<br class="">Effective in-memory tmp_table_size is limited to max_heap_table_size.<br class="">Perhaps you should increase your tmp_table_size and/or<br class="">max_heap_table_size<br class="">to reduce the number of disk-based temporary tables<br class="">Note! BLOB and TEXT columns are not allow in memory tables.<br class="">If you are using these columns raising these values might not impact<br class="">your<br class="">ratio of on disk temp tables.<br class=""><br class=""><br class="">How big should this be, noting the whole database size is currently<br class="">around 960Mb (6000+ subscribers). This is running on Redhat Openstack<br class="">with CEPH storage (with 20 spinning high speed drives).<br class=""><br class=""><br class="">12 cores and 32GB RAM (of which currently less than half is used).<br class="">Increasing bufferpoolsize does nothing (as it only needs about 1 Gig<br class="">and it is set to 4G at the moment)<br class=""><br class=""><br class="">Hope to find some MySQL tuning experts here…<br class=""><br class=""><br class="">Thanks<br class="">Walter.<br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class=""><br class="">_______________________________________________<br class="">Spce-user mailing list<br class=""><a href="mailto:Spce-user@lists.sipwise.com" class="">Spce-user@lists.sipwise.com</a><br class="">https://lists.sipwise.com/listinfo/spce-user<br class=""></blockquote><br class=""><br class=""></div></div></blockquote></div><br class=""></div></body></html>