We just upgraded our database from mariadb 5.5.56 to MySQL 8. We are facing issue that some update queries to an InnoDB takes a long time randomly. Please note that 99% of the time, query is fast but then randomly suddenly it takes huge time and then again back to normal. We DO NOT face this issue in mariadb 5.5.56
CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL DEFAULT '0',
`ipaddr` int(10) unsigned DEFAULT '0',
PRIMARY KEY (`uid`,`pid`),
KEY `pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=161089 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (`pid`)
PARTITIONS 101
mysql> update users set ipaddr=2148888835 where uid=1 limit 1;
Query OK, 1 row affected **(39.51 sec)**
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update users set ipaddr=2148888835 where uid=1 limit 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
query is very simple and it uses primary key as shown below
mysql> explain update users set ipaddr=123 where uid=1;
+----+-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | users | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36,p37,p38,p39,p40,p41,p42,p43,p44,p45,p46,p47,p48,p49,p50,p51,p52,p53,p54,p55,p56,p57,p58,p59,p60,p61,p62,p63,p64,p65,p66,p67,p68,p69,p70,p71,p72,p73,p74,p75,p76,p77,p78,p79,p80,p81,p82,p83,p84,p85,p86,p87,p88,p89,p90,p91,p92,p93,p94,p95,p96,p97,p98,p99,p100 | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
I enabled profiling and it shows updating takes long time.
mysql> SHOW PROFILE; +--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000045 |
| Executing hook on transaction | 0.000005 |
| starting | 0.000005 |
| checking permissions | 0.000004 |
| Opening tables | 0.000017 |
| init | 0.000005 |
| System lock | 0.000193 |
| updating | 39.470708 |
| end | 0.000009 |
| query end | 0.000004 |
| waiting for handler commit | 0.038291 |
| closing tables | 0.000039 |
| freeing items | 0.000024 |
| cleaning up | 0.000028 |
+--------------------------------+-----------+
14 rows in set, 1 warning (0.00 sec)
Although the tables were freshly created using mysqldump, I even tried optimising the table but it didn't help.
mysql> optimize table users;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+-------------------------------------------------------------------+
| mesibo.users | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| mesibo.users | optimize | status | OK |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (2 min 3.07 sec)
Below is config file. Our server has 64GB ram, we allocated 16G to innodb, however changing this value or removing it from configuration does not help.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
innodb_buffer_pool_size=16G
max_connections = 1024
Any clue what might be wrong here?
