Po testach wiem że to przez wolne zapytanie UPDATE do mysql. Czytałem wiele forum, próbowałem wielu konfiguracji, bez skutku. Config mysql jest prawie domyślny więc nie rozumiem czemu baza nie działa jak należy. To nie przeciążenie ponieważ wyłączyłem na chwilę serwis, zrobiłem zapytania przez phpmyadmin i ten sam problem.
Tak więc mam to zapytanie w phpmyadmin na obu serwerach. Tablela (InnoDB) i zawartość takie same więc to nie kwestia struktury.
UPDATE `user` SET `last_date` = "2017-02-05 03:40:50" WHERE `id`="3";
Czasy na nowym serwerze:
Cytat
0.0553 s
0.0265 s
0.0229 s
0.0369 s
0.1579 s <---
0.0191 s
0.0265 s
0.0229 s
0.0369 s
0.1579 s <---
0.0191 s
Czasy na starym serwerze:
Cytat
0.0024 s
0.0042 s
0.0015 s
0.0015 s
0.0015 s
0.0012 s
0.0042 s
0.0015 s
0.0015 s
0.0015 s
0.0012 s
Software na nowym serwerze: Debian 8, Mysql 5.5.31, Apache 2.2.16, PHP 7.1
Software na starym serwerze: Debian 6, Mysql 5.5.31, Apache 2.2.16, PHP 5.3
Próbowałem przenieść cały my.cnf ze starego na nowy i taki sam wynik. Dlaczego ta sama konfiguracja działa dobrze na starym serwerze, ale na nowym świeżym już nie?
my.cnf nowego:
Cytat
port = 3306
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
nice = 0
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
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 0
general_log_file = /var/log/mysql/mysql.log
general_log = 0
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 16G
max_connections = 2048
skip_name_resolve = 1
quick
quote-names
max_allowed_packet = 16M
key_buffer = 16M
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
nice = 0
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
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 0
general_log_file = /var/log/mysql/mysql.log
general_log = 0
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 16G
max_connections = 2048
skip_name_resolve = 1
quick
quote-names
max_allowed_packet = 16M
key_buffer = 16M
my.cnf starego:
Cytat
port = 3306
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
nice = 0
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /dev/shm
bind-address = 0.0.0.0
key_buffer = 128M
max_allowed_packet = 40M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 1000
table_cache = 4096
thread_concurrency = 10
max_heap_table_size = 48M
tmp_table_size = 48M
general_log_file = /var/log/mysql/mysql.log
general_log = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 4500M
quick
quote-names
max_allowed_packet = 16M
key_buffer = 16M
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
nice = 0
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /dev/shm
bind-address = 0.0.0.0
key_buffer = 128M
max_allowed_packet = 40M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 1000
table_cache = 4096
thread_concurrency = 10
max_heap_table_size = 48M
tmp_table_size = 48M
general_log_file = /var/log/mysql/mysql.log
general_log = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days = 10
max_binlog_size = 100M
innodb_buffer_pool_size = 4500M
quick
quote-names
max_allowed_packet = 16M
key_buffer = 16M
Dlaczego tak się dzieje? Coś z systemem? Jakieś pomysły?