The basic formulas are:<\/p>\n
Available RAM = Global Buffers + (Thread Buffers x max_connections)
\nmax_connections = (Available RAM – Global Buffers) \/ Thread Buffers<\/p>\n
To get the list of buffers and their values:<\/p>\n
\n\nSHOW VARIABLES LIKE '%buffer%';\n\n<\/pre>\nHere’s a list of the buffers and whether they’re Global or Thread:<\/p>\n
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
\nThread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack<\/p>\n\nMariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';\n+---------------------------------------+-----------+\n| Variable_name | Value |\n+---------------------------------------+-----------+\n| aria_pagecache_buffer_size | 134217728 |\n| aria_sort_buffer_size | 134217728 |\n| bulk_insert_buffer_size | 8388608 |\n| innodb_blocking_buffer_pool_restore | OFF |\n| innodb_buffer_pool_instances | 1 |\n| innodb_buffer_pool_populate | OFF |\n| innodb_buffer_pool_restore_at_startup | 0 |\n| innodb_buffer_pool_shm_checksum | ON |\n| innodb_buffer_pool_shm_key | 0 |\n| innodb_buffer_pool_size | 134217728 |\n| innodb_change_buffering | all |\n| innodb_log_buffer_size | 8388608 |\n| join_buffer_size | 131072 |\n| join_buffer_space_limit | 2097152 |\n| key_buffer_size | 16777216 |\n| mrr_buffer_size | 262144 |\n| myisam_sort_buffer_size | 8388608 |\n| net_buffer_length | 8192 |\n| preload_buffer_size | 32768 |\n| read_buffer_size | 262144 |\n| read_rnd_buffer_size | 524288 |\n| sort_buffer_size | 524288 |\n| sql_buffer_result | OFF |\n+---------------------------------------+-----------+\n\n<\/pre>\nLets find out the RAM:<\/p>\n
\n# free -b\n total used free shared buff\/cache available\nMem: 3975184384 978608128 1691045888 9445376 1305530368 2661937152\n\n<\/pre>\nLets get our data together.<\/p>\n
RAM = 3975184384
\nGlobal Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_length, query_cache_size
\nor, from above…
\nGlobal Buffers: 16777216 + 134217728 + 8388608 + 0 + 8192 + 0 = 159391744<\/p>\nThread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
\nor, from above…
\nThread Buffers: 524288 + 8388608 + 262144 + 131072 + 524288 + 0 = 9830400<\/p>\nWith this information, the following is the calculation:
\nmax_connections = (Available RAM – Global Buffers) \/ Thread Buffers
\nmax_connections = (3975184384 – 159391744) \/ 9830400<\/p>\nSo the formula shows 378 Max Connections on this machine<\/p>\n
Test info with mysqltuner<\/p>\n
Log into your server with a root or sudo user via SSH.
\nDownload MySQLTuner by executing the following command:<\/p>\n\n\nwget -O mysqltuner.pl https:\/\/raw.githubusercontent.com\/major\/MySQLTuner-perl\/master\/mysqltuner.pl --no-check-certificate\n\n<\/pre>\nGive the script 775 permissions:<\/p>\n
\n\nchmod 775 mysqltuner.pl\n\n<\/pre>\nRun the script with the following command:<\/p>\n
\n\nperl mysqltuner.pl\n\n<\/pre>\nResources:
\nHandy Calculator Download: https:\/\/journeyontux.wordpress.com\/2011\/12\/22\/calculate-number-of-connections-for-mysql-server\/
\nAnother Calculator: http:\/\/www.mysqlcalculator.com\/<\/p>\n","protected":false},"excerpt":{"rendered":"The basic formulas are: Available RAM = Global Buffers + (Thread Buffers x max_connections) max_connections = (Available RAM – Global Buffers) \/ Thread Buffers To get the list of buffers and their values: SHOW VARIABLES LIKE '%buffer%'; Here’s a list of the buffers and whether they’re Global or Thread: Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, … 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":[2,70],"tags":[],"class_list":["post-3842","post","type-post","status-publish","format-standard","hentry","category-administration","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/3842","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=3842"}],"version-history":[{"count":0,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/posts\/3842\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=3842"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=3842"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.qbytes.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=3842"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}