mysql数据迁移性能_数据库迁移后MySQL性能下降了吗？

我将MySQL数据库从GCP迁移到了Azure(都是5.7)，但是它似乎影响了性能。

Server before migration: 2 VCPUS with 7.5GB memory

Server after migration: 2 VCPUS with 8GB memory

两台服务器都运行/运行MySQL服务器的5.7版本。我的数据库目前约为6GB，每天增长100MB以上。它仅包含32个表，尽管其中的一部分表进入了数百万行的类别。

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.

我最初如何检查innodb_buffer_pool_size？

-- returned 0.111...

SELECT @@innodb_buffer_pool_size/1024/1024/1024;

如何更新innodb_buffer_pool_size？

SET GLOBAL innodb_buffer_pool_size=6442450944;

我通过此查询检查了调整大小状态，

-- returned 'Completed resizing buffer pool at 200920 13:46:20.'

SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

我每秒执行大约2个查询，每天最多传播25万个查询。我不确定，但是这种用法不足以阻止性能吗？

我如何检查性能？

我已经显示了运行查询的列表，以及服务器响应所花费的时间。我已经在Navicat，Datagrip和CLI中测试了这些查询，结果相似。

我不确定要在其中包括哪些查询以提供尽可能多的信息，因此，如果我没有包括任何有用的信息，我可以根据要求进行更新。

-- 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`

但有时，我可以运行上述所有查询，并在2到5秒内得到响应。性能似乎受到打击或错过，同一查询所花费的时间差异很大，具体取决于我目前难以诊断的运行时间。

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)

慢查询日志

我运行了很多相同的查询，因此我将其截短了一部分。

# 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;