Often aborted connections to db (MariaDB)

Question

I have a Windows server running a MySQL server. There are two databases for different applications on the MySQL server.

In total, around 25 users access the database server every day.

The database server is not in the user's local network (sometimes not even in the same country).

Unfortunately, we often have problems with users not being able to connect to the server despite a stable internet connection. Only after several attempts does the user manage to connect.

When I look in the log files on the database server, I see a lot of entries like this:

1. 2024-03-19 14:08:42 2378 [Warning] Aborted connection 2378 to db: 'CMS_DB' user: 'JOHN' host: 'bba-83-130-102-145.alshamil.net.ae' ( Got an error reading communication packets)

2. 2024-03-19 13:44:45 1803 [Warning] Aborted connection 1803 to db: 'CMS_DB' user: 'REMA' host: '188.137.160.92' (Got timeout reading communication packets)

3. 2024-03-19 11:51:08 1526 [Warning] Aborted connection 1526 to db: 'unconnected' user: 'unauthenticated' host: '92.216.164.102' (Got an error reading packet communications)

4. 2024-03-19 11:51:08 1526 [Warning] Aborted connection 1526 to db: 'unconnected' user: 'unauthenticated' host: '92.216.164.102' (This connection closed normally without authentication)

5. 2024-03-19 11:55:26 1545 [Warning] IP address '94.202.229.78' could not be resolved: No such host is known.

My my.ini file looks like this:

[mysqld]
datadir = C:/Program Files/MariaDB 10.5/data
port = 3306
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections = 600

wait_timeout = 3600
interactive_timeout = 3600

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server = utf8

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=50M

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=256M

slow_query_log = ON
slow_query_log_file = C:/Program Files/MariaDB 10.5/data/slow_query_log.log
general_log = ON
general_log_file = C:/Program Files/MariaDB 10.5/data/general_log.log
log_error = C:/Program Files/MariaDB 10.5/data/error_log.log

innodb_page_size = 65536
innodb_buffer_pool_size = 4085M
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M


[client]
port = 3306
plugin-dir = C:/Program Files/MariaDB 10.5/lib/plugin

My c3p0 settings in the hibernate.cfg.xml looks like this:

<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_size">7</property>
<property name="hibernate.c3p0.timeout">120</property>
<property name="hibernate.c3p0.max_statements">20</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">20</property>

It was difficult for me to see through all the settings.

I want users to be able to connect to the database server easily without annoying me every time :D

What could be the problem? Do my settings match? Can someone who knows about this help me?

I am very grateful for any helpful answer, as I have been struggling with this for almost 5 weeks, but to no avail. Thanks in advance

If I should post any other settings like server variables, let me know.

Additional information

• OS Version --> Microsoft Windows Server 2022 Standard (version 10.0.20348)
• RAM --> 32 GB
• CPU --> Intel(R) Xeon(R) E-2314 CPU @ 2.80GHz 4 Cores
• Disk --> 1 TB SSD

Link of the my.ini file : https://jpst.it/3DRYr

I should also note that last night around 9:00 p.m. One user was no longer able to connect at all. I simply restarted the Windows server and after that it worked. After the restart, no users complained about connection problems (yet).

Answer 1

Suggestions to consider for your my.ini [mysqld] section.

Zaki,

General Log is normally NOT ON.

Until actually needed and will be used,

general_log=OFF

When needed from MySQL Command Prompt,

SET GLOBAL general_log=ON

then when you have sufficient logging completed, usually less than one minute - unless you have a VERY LONG executing process,

SET GLOBAL general_log=OFF

to avoid filling your storage with information you will NEVER look at. Trust me a full storage device is NO FUN to deal with.

Do you have a REAL good reason for setting innodb_page_size anything other than the default of 16384?

Consider editing your my.cnf to get back to the default from 64K This has helped the few instances where they have attempted anything other than 16384 and been troubled with incidents out of nowhere.

Additional suggestions will be provided after posting the additional information requested in a comment above.

Answer 2

Rate Per Second = RPS

Suggestions to consider for your my.ini [mysqld] section

innodb_page_size=16384  # from 65536 for the default 
thread_pool_size=3  # from 4 you only have 4 CPU's and need 1 free
innodb_io_capacity=500  # from 200 to use more of the available SSD IOPS
read_rnd_buffer_size=16384  # from 262144 to reduce handler_read_rnd_next RPS of 237

There are many more opportunities to improve performance. View my profile.

Observations: com_rollback reported 9,229 in 1.6 days of uptime. Rollbacks need to be investigated and prevented, if possible. General Log Analysis may be required. For replication durability, binlog_format is usually ROW, not MIXED. innodb_secondary_index_triggered_cluster_reads reported 25,512,913 (RPS of 183) and is usually caused by tables with NO Primary Key. For a query to find your tables with No Primary Key, provide an email address, please.