{"id":2584,"date":"2015-08-20T21:11:19","date_gmt":"2015-08-20T21:11:19","guid":{"rendered":"https:\/\/qbytes.cloud\/?p=2584"},"modified":"2015-08-20T21:11:19","modified_gmt":"2015-08-20T21:11:19","slug":"mysql-optimization","status":"publish","type":"post","link":"https:\/\/www.qbytes.cloud\/index.php\/2015\/08\/20\/mysql-optimization\/","title":{"rendered":"Mysql optimization"},"content":{"rendered":"<p>From MySQL Large Configuration.<\/p>\n<p>MySQL Large Original<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n\n# The MySQL server\n&#x5B;mysqld]\nport            = 3306\nsocket          = \/var\/lib\/mysql\/mysql.sock\nskip-locking\nkey_buffer_size = 256M\nmax_allowed_packet = 1M\ntable_open_cache = 256\nsort_buffer_size = 1M\nread_buffer_size = 1M\nread_rnd_buffer_size = 4M\nmyisam_sort_buffer_size = 64M\nthread_cache_size = 8\nquery_cache_size= 16M\n# Try number of CPU&#039;s*2 for thread_concurrency\nthread_concurrency = 8\n\n<\/pre>\n<p>After Optimization<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\n# The MySQL server\n&#x5B;mysqld]\nport            = 3306\nsocket          = \/var\/lib\/mysql\/mysql.sock\nskip-locking\nkey_buffer_size = 384M\nmax_allowed_packet = 1M\ntable_open_cache = 512\nsort_buffer_size = 2M\nread_buffer_size = 2M\nread_rnd_buffer_size = 8M\nmyisam_sort_buffer_size = 64M\nthread_cache_size = 8\nquery_cache_size = 32M\n# Try number of CPU&#039;s*2 for thread_concurrency\nthread_concurrency = 8\n<\/pre>\n<p>Other ideas&#8230;<\/p>\n<p>mysql optimize<\/p>\n<p>innodb_buffer_pool_instances = 2<br \/>\nreduced the number of max connections to 500 (which is still high)<br \/>\nsyn error<\/p>\n<p>adjusting \/proc\/sys\/net\/ipv4\/tcp_max_syn_backlog<\/p>\n<p>nf_conntrack: table full, dropping packet.<\/p>\n<p>network and ulimit settings<\/p>\n<p>number of connections in time_wait\u00a0&#8211; how to contrl:<\/p>\n<p>set both tcp_tw_recycle and lowered tcp_fin_timeout to 30<\/p>\n","protected":false},"excerpt":{"rendered":"<p>From MySQL Large Configuration. MySQL Large Original # The MySQL server &#x5B;mysqld] port = 3306 socket = \/var\/lib\/mysql\/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M # Try number of CPU&#039;s*2 for thread_concurrency thread_concurrency = 8 &#8230; <a title=\"Mysql optimization\" class=\"read-more\" href=\"https:\/\/www.qbytes.cloud\/index.php\/2015\/08\/20\/mysql-optimization\/\" aria-label=\"Read more about Mysql optimization\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[70],"tags":[],"class_list":["post-2584","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2584","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=2584"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/2584\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=2584"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=2584"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=2584"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}