innodb – MySQL performance degraded after database migration?


I migrated my MySQL database from GCP to Azure (both 5.7), but it seems to have affected performance.

Server before migration: 2 VCPUS with 7.5GB memory
Server after migration: 2 VCPUS with 8GB memory

Both servers run / ran version 5.7 of the MySQL server. My database is currently around 6GB in size, growing 100MB+ a day. It only consists of 32 tables, although a fraction of them tables enter the millions of rows category.

I read up on innodb_buffer_pool_size, GCP apparently sets it to around 80% of the memory, which would make it 6GB. I have set the innodb_buffer_pool_size on the new server to the same value.

Before updating this value (when I first noticed decreased performance), innodb_buffer_pool_size was set to 0.1 GB on the new server, I then decided to update this to the value the GCP server was set at hoping it would help.

Following this documentation I was able to update the buffer pool size.

How did I check the innodb_buffer_pool_size initially?

-- returned 0.111...
SELECT @@innodb_buffer_pool_size/1024/1024/1024;

How did I update innodb_buffer_pool_size?

SET GLOBAL innodb_buffer_pool_size=6442450944;

I checked the resize status with this query,

-- returned 'Completed resizing buffer pool at 200920 13:46:20.'
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

I execute around 2 queries a second, peaking at 250k a day spread out. I can’t be certain but this usage shouldn’t be enough to halt performance?

How am I checking performance?

I have shown a list of queries ran, and the times it takes for the server to respond. I have tested these queries in Navicat, Datagrip, and CLI with similar results.

I wasn’t sure what queries to include here to give as much information as possible, so if I haven’t included anything useful I can update it upon request.

-- Fetching 100k rows from a 3.1m rows table
-- Time took: 21.248s
SELECT * FROM `profile_connections` LIMIT 100000;

-- (SECOND TIME) Fetching 100k rows from a 3.1m rows table
-- Time took: 1.735s
SELECT * FROM `profile_connections` LIMIT 100000;

- Fetching a random row from a 3.1m row table 
-- Time took: 0.857s
SELECT * FROM `profile_connections` WHERE `id` = 2355895 LIMIT 1;

-- (SECOND TIME) Fetching a random row from a 3.1m row table 
-- Time took: 0.850s
SELECT * FROM `profile_connections` WHERE `id` = 2355895 LIMIT 1;

-- Fetching all rows from a 20 row table
-- Time took: 40.010s
SELECT * FROM `profile_types`

-- (SECOND) Fetching all rows from a 20 row table
-- Time took: 0.850s
SELECT * FROM `profile_types`

But at times, I can run all of the above queries and get a response in 2 – 5 seconds. Performance seems to be hit or miss, there are huge differences in times taken for the same query, depending on when it is run which I am currently struggling to diagnose.

I ran mysqltuner and got these performance metrics back:

(--) Up for: 47m 39s (38K q (13.354 qps), 1K conn, TX: 403M, RX: 63M)
(--) Reads / Writes: 50% / 50%
(--) Binary logging is disabled
(--) Physical Memory     : 7.8G
(--) Max MySQL memory    : 146.8G
(--) Other process memory: 0B
(--) Total buffers: 6.0G global + 954.7M per thread (151 max threads)
(--) P_S Max memory usage: 72B
(--) Galera GCache Max memory usage: 0B
(!!) Maximum reached memory usage: 21.9G (281.61% of installed RAM)
(!!) Maximum possible memory usage: 146.8G (1888.34% of installed RAM)
(!!) Overall possible memory usage with other process exceeded memory
(OK) Slow queries: 3% (1K/38K)
(OK) Highest usage of available connections: 11% (17/151)
(OK) Aborted connections: 0.67%  (9/1342)
(!!) name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
(OK) Query cache is disabled by default due to mutex contention on multiprocessor machines.
(OK) Sorts requiring temporary tables: 0% (0 temp sorts / 41 sorts)
(OK) No joins without indexes
(OK) Temporary tables created on disk: 4% (82 on disk / 1K total)
(OK) Thread cache hit rate: 98% (17 created / 1K connections)
(OK) Table cache hit rate: 63% (667 open / 1K opened)
(OK) table_definition_cache(1400) is upper than number of tables(302)
(OK) Open file limit used: 1% (55/5K)
(OK) Table locks acquired immediately: 100% (1K immediate / 1K locks)

Slow query logs
I run a lot of the same queries, so I’ve truncated it to include just a few.

# Time: 2020-09-20T16:45:04.230173Z
# User@Host: root(root) @  (51.132.38.176)  Id:     7
# Query_time: 1.022011  Lock_time: 0.000084 Rows_sent: 1  Rows_examined: 1058161
SET timestamp=1600620304;
SELECT @id := `id`,`item`
                    FROM `queue_items`
                    WHERE `processed_at` IS NULL AND `completed_at` IS NULL AND `confirmed` = '1'ORDER BY `id` ASC
                    LIMIT 1
                    FOR UPDATE;
# Time: 2020-09-20T16:45:09.676613Z
# User@Host: root(root) @  (51.132.38.176)  Id:     5
# Query_time: 1.198063  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1600620309;
COMMIT;
# Time: 2020-09-20T16:45:22.938081Z
# User@Host: root(root) @  (51.105.34.135)  Id:     4
# Query_time: 5.426964  Lock_time: 0.000133 Rows_sent: 0  Rows_examined: 1
SET timestamp=1600620322;
UPDATE `queue_items` SET `completed_at` = '2020-09-20 16:45:17', `updated_at` = '2020-09-20 16:45:17' WHERE `id` = 1818617;