Sziasztok,
Az alábbi problémával fordulnék hozzátok, melyre nem találom a jól működő megoldást:
Adott egy MySQL szerver rajta egy nem túl bonyolult 100 táblás adatbázis MySQL alapon. Az adatbázist egy szoftveren keresztül 60 ember használja egyszerre. Ez alapvetően nem sok, de mégis nagyon gyakori az alábbi két hibaüzenet:
- Deadlock found when trying to get lock; try restarting transaction
- Lock wait timeout exceeded; try restarting transaction
Illetve egy ráadás üzenet, ami bár nem biztos a konkurens kérésekkel függ össze, de mégis gyakori és látszólag semmi nem indokolja:
- Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding
Az üzenet arról árulkodik, hogy gond lenne az adatbázis szerver válaszadásával, vagy elérhetetlen lenne, vagy bármi hasonló probléma. De ez nem igaz, mert a mellette ülő (programot használó) gond nélkül használja a rendszert.
Végül pedig a log fájl, amit a MySQL szerver vezet az alábbi sorokkal van tele folyamatosan:
2018-11-21T07:41:39.653890Z 123605 [Note] Aborted connection 123605 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
2018-11-21T07:42:01.564092Z 123894 [Note] Aborted connection 123894 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
2018-11-21T07:42:06.961086Z 123786 [Note] Aborted connection 123786 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
2018-11-21T07:42:17.027146Z 124046 [Note] Aborted connection 124046 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
2018-11-21T07:42:29.759601Z 122790 [Note] Aborted connection 122790 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
2018-11-21T07:43:15.211959Z 121478 [Note] Aborted connection 121478 to db: '' user: '' host: '192.168.1.252' (Got timeout reading communication packets)
Próbáltam már program kód szinten megoldani, mert sok helyen lehet arról olvasni, hogy a programba a lekérdezéseket kell kicsit másképp megfogalmazni, de ez mind mese habbal. Olyan nyaka tekert megoldásokat javasolnak, ami abszolút nem életszerű.
Nem tudom merre keressem a hibát. Másra már nem tudok gondolni, mint hogy a szerver beállításain kellene csiszolnom. Találkozott már valaki hasonlóval? Oldott már meg valaki hasonló problémát? Előre is köszönöm az építő jellegű hozzászólásokat.
MySQL szerver konfig beállításai:
[mysqld]
#
# * Basic Settings
#
character-set-server = utf8
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
max_connections = 100
skip-external-locking
connect_timeout = 10
interactive_timeout = 100
wait_timeout = 100
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size = 128M
join_buffer_size = 16M
max_allowed_packet = 128M
table_open_cache = 128M
sort_buffer_size = 1024K
net_buffer_length = 64K
read_buffer_size = 1024K
read_rnd_buffer_size = 1024K
myisam_sort_buffer_size = 8M
thread_stack = 192K
thread_cache_size = 8
tmp_table_size = 24M
max_heap_table_size = 24M
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options = BACKUP
thread_cache_size = 16K
#table_cache = 64
#
# * Query Cache Configuration
#
query_cache_type = 1
query_cache_size = 32M
query_cache_limit = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysqld_slow_queries.log
long_query_time = 1
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 128M
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 48M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = 0
innodb_file_per_table
# Uncomment this to get FEDERATED engine support
#plugin-load=federated=ha_federated.so
loose-federated
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem