L2JMobius

Other => Knowledge Base => Topic started by: viracocha on March 28, 2022, 01:07:41 PM

Title: MySQL Connections Optimization
Post by: viracocha on March 28, 2022, 01:07:41 PM

Hello everyone, in my case I had problems with the Login Server where users could not log in.
With a volume of approximately 200 accounts, it generated thousands of connections and these were not closed due to a high timeout in the Mysql configuration.

I optimized it and the connections are already keep clean. This setting kills SLEEP processes greater than 15 seconds.

Add in my.cnf this configurations inside [mysqld] and restart your MySQL Service.

Code: [Select]

wait_timeout=15
connect_timeout=10
interactive_timeout=100

You can check your connections inside mysql console excecuting this command: show processlist;


MariaDB [(none)]> show processlist;
+-------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+
| Id    | User        | Host            | db        | Command | Time | State                    | Info             | Progress |
+-------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+
|     2 | system user |                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|     4 | system user |                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|     3 | system user |                 | NULL      | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|     1 | system user |                 | NULL      | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|     5 | system user |                 | NULL      | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 60306 | root        | localhost:61003 | l2jmobius | Sleep   |    0 |                          | NULL             |    0.000 |
| 60307 | root        | localhost:61004 | l2jmobius | Sleep   |    0 |                          | NULL             |    0.000 |
| 60308 | root        | localhost:61005 | l2jmobius | Sleep   |    0 |                          | NULL             |    0.000 |
| 60309 | root        | localhost:61006 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60310 | root        | localhost:61007 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60311 | root        | localhost:61008 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60312 | root        | localhost:61009 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60313 | root        | localhost:61010 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60314 | root        | localhost:61011 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60315 | root        | localhost:61012 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60316 | root        | localhost:61013 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60317 | root        | localhost:61014 | l2jmobius | Sleep   |   21 |                          | NULL             |    0.000 |
| 60318 | root        | localhost:61015 | l2jmobius | Sleep   |    0 |                          | NULL             |    0.000 |
| 60319 | root        | localhost:61016 | NULL      | Query   |    0 | Init                     | show processlist |    0.000 |
+-------+-------------+-----------------+-----------+---------+------+--------------------------+------------------+----------+
19 rows in set (0.000 sec)

MariaDB [(none)]>


:)

Title: Re: MySQL Connections Optimization
Post by: Mobius on July 07, 2023, 06:39:02 PM

I wonder if this can be done to avoid cnf file changes.

Code: (diff) [Select]

Index: dist/game/config/Server.ini
===================================================================
--- dist/game/config/Server.ini (revision 12198)
+++ dist/game/config/Server.ini (working copy)
@@ -42,7 +42,7 @@
 
 # Database URL
 # Default: jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false
-URL = jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false
+URL = jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false&connectTimeout=10000&interactiveClient=true&sessionVariables=wait_timeout=15,interactive_timeout=100
 
 # Database user info (default is "root" but it's not recommended)
 Login = root
Index: dist/login/config/LoginServer.ini
===================================================================
--- dist/login/config/LoginServer.ini (revision 12198)
+++ dist/login/config/LoginServer.ini (working copy)
@@ -42,7 +42,7 @@
 
 # Database URL
 # Default: jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false
-URL = jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false
+URL = jdbc:mariadb://localhost/l2jmobius?useUnicode=true&characterEncoding=utf-8&useSSL=false&connectTimeout=10000&interactiveClient=true&sessionVariables=wait_timeout=15,interactive_timeout=100
 
 # Database user info (default is "root" but it's not recommended)
 Login = root