Upgrade MySQL with millions tables

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hello,

I want to upgrade Percona Server for MySQL from 8.0.20 to 8.0.21. My server contains 3000 databases with 1000 tables in each database. Yes, I know this is a rare configuration. It is a bit like shared hosting.

After starting mysqld on the new version, I see in the log for about 20 minutes:

2020-11-25T18:27:27.571354Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version ‘80017’ to ‘80021’.

And then all the available memory and swap on the server ends, and finally the mysqld process was killed by OOM Killer.

Then everything starts all over again.

How can I upgrade a server with millions of tables?

2

Hi @demidov I am sorry to hear you are running into issues. I suggest you open a bug in our issue tracker so https://jira.percona.com/projects/PS about this

3

Hi @igroene !

I took a bigger server - with 128 GB of memory. And tryed one more time.

At its peak, the mysqld process took about 106 GB. And in the end it was completed in about 1 hour.

2020-11-26T17:38:46.113695Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version ‘80017’ to ‘80021’.

2020-11-26T18:41:38.549123Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version ‘80017’ to ‘80021’ completed.

Is this normal? If ok, then ok.

If not, should I open a bug?

4

@demidov

This is probably normal given amount of tables you have, but I understand the inconvenience.

I probably would try to play with table_definition_cache

What happens if set it to 10000. Would upgrade perform normally? How much memory it would take in this case, etc…

5

@vadimtk

In my example above it was:

table_definition_cache = 30000

I could try to increase this value.

6

actually I would try to decrease that value if the memory consumption is still big

7

Interesting if table_definition_cache changes it or if data dictionary upgrade operation is not memory efficient with large number of tables.

If it were to be the case it is likely upstream MySQL issue.

8

@vadimtk , @Peter

table_definition_cache = 5000

Nothing has changed with this value. Memory consumption is about the same.

9

@demidov

what type of storage you are using ssd vs hdd ?

what the total startup time for mysqld in general ?

I’m testing it with ~ 2M innodb tables ( 2000 database with 1000 tables in each)

10

@lalit.choudhary

SSD. And normal startup time is about 1 minute.

11

@demidov

Thank you for the details.

I see issue in my test (1M tables,1000dbs with 1000 tables in each)) after upgrading from PS 8.0.20 to 8.0.21. Using SSD storage.

after upgrade 1st Startup in progress and with time it’s consuming more memory. I can see memory reducing on OS while Data dictionary upgrading from version ‘80017’ to ‘80021’. in progress.

I will test with upsteam as well and report this issue. I will update Bug# here later.

12

Here are the bug report for reference.

https://jira.percona.com/browse/PS-7446

https://bugs.mysql.com/bug.php?id=101818 Looks like you already reported this to upstream.

My upstream version test still running i will update upstream bug with my test results.

13

@lalit.choudhary

Thank you!