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] @ [18.104.22.168] 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] @ [22.214.171.124] 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] @ [126.96.36.199] 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;
Since asking, I have added the following variables to my config, although it doesn't seem to have fixed any of the performance issues.
slow_query_log=1 long_query_time=1 log_output=FILE slow_query_log_file=/var/lib/mysql/slow.log innodb_buffer_pool_size=6442450944 innodb_buffer_pool_instances=6 innodb_log_file_size=780MB query_cache_size=0 query_cache_limit=1M