Memperbaiki “too many connection” Mysql/MariaDB, Ketika ada klien mencoba masuk ke Mysql/MariaDB biasanya akan bisa langsung masuk, namun bagaimana jika banyak sekali klien yang sebelumnya telah terkoneksi dan masuk ke dalam Mysql/MariaDB, dan ketika jumlah koneksi masuk telah mencapai batas akan memunculkan pesan “too many connections” dan klien tidak dapat masuk karena koneksi masuk telah mencapai batas, dan jika klien ingin masuk harus menunggu pengguna lain keluar dari Mysql/MariaDB atau administrator harus menambahakn jumlah maksimum koneksi masuk yang di ijinkan.
jika anda ingin melihat informasi berapa koneksi yang digunakan saat ini gunakan perintah dibawah
MariaDB> SHOW STATUS LIKE 'max_used_connections';
SOLUSI
solusi untuk masalah ini adalah menaikan jumlah koneksi yang di ijinkan yang ada pada variabel max_connection, secara baku nilai dari max_connection adalah 151, untuk melihat jumlah koneksi yang diijinkan sat ini gunakan perintah berikut
MariaDB> SHOW VARIABLES LIKE 'max_connections';
Menaikan maksimal koneksi sementara
untuk menaikan jumlah koneksi secara sementara hanya harus menambahkan nilai dari variable max_connection anda bisa menggunakan perintah dibawah ini
MariaDB> SET GLOBAL max_connections = 512;
lalu cek apalah nilai dari variable max_connection telah berubah
MariaDB> SHOW VARIABLES LIKE 'max_connections';
namun dengan cara diatas hanya akan menaikan jumlah maksimal koneksi hanya sementara, jika layanan Mysql/MariaDB berhenti atau di jalankan ulang, akan kembali berubah ke nilai bakunya yaitu 151, untuk menaikan secara permanen gunakan cara dibawah.
Menaikan maksimal koneksi permanen
Cara yang paling baik adalah menggunakan cara ini, agar ketika layanan Mysql/MariaDB Berhenti atau dijalankan ulang kita tidak perlu mengkonfigurasinya kembali, kita hnaya perlu mengkonfigurasi my.cnf. untuk mengkonfigurasinya ikuti tutorial dibawah.
tambahkan variabel max_connection di my.cnf
user $ sudo vim /etc/my.cnf
tambahkan
max_connections = 512
lalu jalankan ulang layanan MySQL/MariaDB
user $ sudo systemctl restart mysqld
mysql -u root -p Table of Contentsmysql max connections limit check query commanding
mysql connection limit allowed
Password:
mysql> SHOW VARIABLES LIKE “max_connections”;
SHOW VARIABLES LIKE “max_connections”;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 | default
+—————–+——-+
1 row in set [0.01 sec]
To view max Reached Mysql Connections since the Server start
SHOW GLOBAL STATUS LIKE ‘max_use%’;
+—————————+———————+
| Variable_name | Value |
+—————————+———————+
| Max_used_connections | 38 |
| Max_used_connections_time | 2021-06-08 02:39:27 |
+—————————+———————+
mysql> SHOW STATUS WHERE `variable_name` = ‘Max_used_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Max_used_connections | 107 |
+———————-+——-+
1 row in set [0.00 sec]
I don’t need to worry right now because 60+live visitors handled by page cache plugins.
To check active connections in mysql concurrent
SHOW STATUS WHERE `variable_name` = ‘Threads_connected’;
Mysql threads variable settings ideal
to list all connections
show status like ‘%onn%’;
mysql max connections recommended settings [how to choose]
In digital ocean manged sql limits
1 GB RAM | 75 | 250MB |
2 GB RAM | 150 | 350MB |
4 GB RAM | 225 | 350MB |
8 GB RAM | 525 | |
16 GB RAM | 1,050 | |
32 GB RAM | 2,175 | |
64 GB RAM | 4,425 |
350 MB used Operating system.
750MB = 100MB per connection
4GB RAM Mysql dedicated machine 3.6GB usable and 3Gb*75=225
mysql max connections calculator
max_connections = [Available RAM – Global Buffers] / Thread Buffers
available ram =MySQL ram
Global buffers
SHOW VARIABLES LIKE ‘%buffer%’;
innodb_buffer_pool, key_buffer_size, query_cache_size, etc
Available RAM = Global Buffers + [Thread Buffers x max_connections]
max_connections = [Available RAM – Global Buffers] / Thread Buffers
global buffers
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool
net_buffer_length
total size of global buffers
thread buffers
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_mid_buffer_size
total threaded buffer size
generally 10% global buffers 1gb vs 100Mb.
max connections 151 default.
Ram= 1000+ [100*151]
1000+15100
1010+[112*27]
3024+1010=4GB.
otherway
check previous max used connections
increase
check mysql error logs.
Update max connection limit in mysql.conf
my.cnf
max_connections = 500
mysql> SHOW VARIABLES LIKE “max_connections”;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 |
+—————–+——-+
1 row in set [0.03 sec]
Check max used connections by mysql previous
maximum number of connections that have been in use simultaneously since the server started.
mysql> SHOW GLOBAL STATUS LIKE ‘max_use%’;
+—————————+———————+
| Variable_name | Value |
+—————————+———————+
| Max_used_connections | 26 |
| Max_used_connections_time | 2021-02-12 02:39:15 |
+—————————+———————+
2 rows in set [0.02 sec]
Variable_name | Value |
+————————+——-+
| max_connections | 151 |
| mysqlx_max_connections | 100 |
+————————+——-+
he maximum number of concurrent client connections X Plugin can accept. This is the X Plugin equivalent of max_connections;
mysql> SHOW GLOBAL VARIABLES LIKE ‘max_user_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| max_user_connections | 0 |
+———————-+——-+
1 row in set [0.00 sec]
Aborted connection mysql
mysql> show global status like “%Aborted%”;
+————————+——-+
| Variable_name | Value |
+————————+——-+
|
Aborted_clients | 0 |
| Aborted_connects | 47073 |
| Mysqlx_aborted_clients | 0 |
+————————+——-+
3 rows in set [0.00 sec]
//journeyontux.files.wordpress.com/2011/12/max_connection_measurements.xls
mysql> SHOW STATUS LIKE ‘Threads_connected’;
+——————-+——-+
| Variable_name | Value
|
+——————-+——-+
| Threads_connected | 1 |
+——————-+——-+
1 row in set [0.00 sec]
also follow mysql memory allocation & optimization
Show threads currently running on MySQL DB instance
SHOW FULL PROCESSLIST\G
increasing thread_cache_size to allow more simultaneous users
max_used_connections is 151, and your current value for thread_cache_size is 10,
then you have a problem
thread_cache_size should be greater than Max_used_connections.
SHOW GLOBAL STATUS LIKE ‘Connections’;
SHOW GLOBAL STATUS LIKE ‘Threads_created’;
SHOW GLOBAL STATUS LIKE ‘Max_used_connections’;
Threads_created / Connections : If this is over 0.01, then increase
4/427=0.001
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Connections | 427 |
+—————+——-+
1 row in set [0.01 sec]
mysql> SHOW GLOBAL STATUS LIKE ‘Threads_created’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| Threads_created | 4 |
+—————–+——-+
1 row in set [0.00 sec]
mysql> SHOW GLOBAL STATUS LIKE ‘Max_used_connections’;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Max_used_connections | 4 |
+———————-+——-+
1 row in set [0.00 sec]
default mysql thread cache size is = 9 or 10
max connecton limit 151
Max_used_connectionsprevipous restrt 47
so i set thread cache size to 50
show global variables like ‘innodb_buffer_pool_size’;
thread_cache_size | 9
set global thread_cache_size = 50;
mysql> set global thread_cache_size = 50;
Query OK, 0 rows affected [0.00 sec]
my.cnf
thread_cache_size = 50;
Data API HTTP request body size | 4 Megabytes | No |
Data API maximum concurrent cluster-secret pairs | 30 | No |
Data API maximum concurrent requests | 500 | No |
Data API maximum result set size | 1 Megabytes | No |
Data API requests per second | 1,000 | No |
mysql too many connections fix
ERROR 1040[]: Too many connections error in the MySQL error log.
when it appears you need to increase the mysql max connections per second. it needs extra memory like memory per connection. more at amz rds
What is maximum connection?
Max Connections controls the maximum number of keep-alive connections the server maintains. The possible range is zero to 32768, and the default is 256.
How does MySQL determine max connections?
There's a MySQL Calculator you can use for this..
Thanx Omesh for your good suggestion. ... .
5GB is okay under normal load but if at a point if you get all the database requests equal to max number of connections then MySQL might get crashed so try to set these variables below 2GB. ... .
Hi Omesh I'll try this..
How do I fix MySQL max connections?
Solution.
Identify the max_connections variable value: mysql --user="root" --password="PASSWORD" --execute='SHOW VARIABLES LIKE "max_connections"; ... .
Use your favorite text editor to change /etc/mysql/my.cnf and set the following values: max_connections = 500. ... .
Restart the MySQL service and to apply the changes..
What is total connections in MySQL?
The active or total connection can be known with the help of threads_connected variable. The variable tells about the number of currently open connections. mysql> show status where `variable_name` = 'Threads_connected'; Here is the output.