Terrible performance when importing database of ZBX 5.0 from MySQL 5.7 to MySQL 8.0.

[zaicnupagadi]

Hi,

Got 5.0 Zabbix server on Ubuntu 18.04, with MySQL 5.7. The new server is already fully configured, with all the configuration moved and Zabbix 6.0 running and tested.

I just thought moving the would be the last final piece, cherry on pie - I was so wrong.

The database has 100GB in size

Size checked with command:
mysql> SELECT table_schema "zabbix",ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
+--------------------+---------------+
| zabbix | DB Size in MB |
+--------------------+---------------+
| information_schema | 0.2 |
| mysql | 3.9 |
| performance_schema | 0.0 |
| sys | 0.0 |
| zabbix | 103325.4 |
+--------------------+---------------+
5 rows in set (1.07 sec)

Made a dump with command - when the zabbix service was stopped (just in case):
mysqldump -uroot -p --single-transaction --quick --lock-tables=false zabbix | gzip > /dbdump/dumps/ZabbixDB_Backup.gz

The dump has about 6GB.

Tried to do a dump import on the new machine, but it takes like hours to import like, the whole database I think would be importing for 2 days.

I am doing a test and made a machine with MySQL 5.7 - the import goes much faster!

So I have read a bit about it, and performed following changes on the target machine:

In SQL:
SET @OLD_AUTOCOMMIT=@@AUTOCOMMIT, AUTOCOMMIT = 0;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

And

create /etc/mysql/conf.d/disable_binary_log.cnf with the following contents:

[mysqld]
skip-log-bin

+ restart of MySQL.

Still no changes. Import takes ages. Am I the only person struggling with such issue or it is simply like this and nothing can be done?

Any ides much appreciated as I am out of ideas currently. The only idea maybe is to import tht database to the "middle server" with MySQL 5.7 where import goes much faster, upgrade the database to 8.0 and move database files to another server, or - if that fails - try to import the mysqldump from 8.0 to 8.0 (might be faster).

J

[tim.mooney]

Skipping binary logging during the import is a good idea, so you're on the right trail there.

You don't say anything about any MySQL tuning on the new system. Have you done any? How big is your innodb_buffer_pool_size? How much RAM does the new system have, and how much were you planning on devoting to MySQL?

[zaicnupagadi]

Hi Tim,

Thank you for reply!

Well I haven't done any, VM has 4 vCPU, 8GB RAM (dynamic). The innodb_buffer_pool_size is :

+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728 |
+---------------------------+

I assume this is in bytes.

Checked and things that I've configured on the old system, like innodb_file_per_table is here set by default, some parameters are no longer there etc. so I just left the default.

Do you recommend some changes?

[Markku]

In the Zabbix blog: MySQL performance tuning 101 for Zabbix

(Yeah increasing innodb_buffer_pool_size makes wonders, not aware how your tested 5.7 was configured though)

Markku

[zaicnupagadi]

Markku The 5.7 one was as much default as possible - also changed nothing. Today started the import at 9:00, after 2 hours 135 MB (yup, megabytes) got imported:

mysql> SELECT table_schema "zabbix",ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;
+--------------------+---------------+
| zabbix | DB Size in MB |
+--------------------+---------------+
| information_schema | 0.0 |
| mysql | 2.7 |
| performance_schema | 0.0 |
| sys | 0.0 |
| zabbix | 135.8 |
+--------------------+---------------+
5 rows in set (0.08 sec)

Noice.

EDIT:
Appeared that somehow when checked the files, history table files were GB in size, closed the mysql session, opened once again and proper value of 28GB was reported by the SQL command. So seems I need to look at the DB files instead of this SQL command.

Anyway it is slow, maybe it should be like this...

[Markku]

Yes I guess we agree that there is something wrong. Check also with your VM infrastructure admins what kind of storage there is, how the capacity is guaranteed for your VM, and so on.

If I remember correctly, I last time imported a ~50 GB (systemwise, don't remember the gzipped dump size) Zabbix database on a new MariaDB 10.5 server (VM, no guaranteed CPU/disk I/O capacity, local SSD storage in mirrored RAID, 16 GB RAM with innodb_buffer_pool_size set to 75% = 12 GB) in about/max two hours. I think I disabled bin-log, otherwise nothing much.

There are some performance hint tools for MySQL, you could search for those as well.

Markku

[tim.mooney]

Well I haven't done any, VM has 4 vCPU, 8GB RAM (dynamic).

Do you recommend some changes?

As Markku has said, being able to increase innodb_buffer_pool_size will likely be the biggest performance improvement for the import.

Since it's a VM, will your VM admins allow you to temporarily give the system more RAM? What I'm thinking is

1. On the new system:
   1. shut down Zabbix
   2. shut down mysql
   3. modify the MySQL config to set innodb_buffer_pool_size=8G (or whatever value is half of the system RAM you temporarily assign to the VM in the next step)
   4. shut down the VM so you can change its RAM config
2. in your VM software, temporarily give the VM 16G of RAM
3. boot up the system
4. verify MySQL now has 8 Gig for the innodb_buffer_pool_size
5. do the database import
6. start up Zabbix and let it upgrade the database to the 6.2.x version
7. shut down Zabbix & MySQL
8. revert your MySQL config so innodb_buffer_pool_size is no longer 8G
9. shut down the system
10. revert the VM RAM to 8 G
11. resume normal operation

You basically make the system "bigger" just for the duration of the import and upgrade, then return it to the size you want.

[LenR]

You can probably do this:

1. Stop the old zabbix server and mysql
2. delete everything from the new /var/lib/mysql (after a backup)
3. setup root ssh key from old server to new server
4. rsync /var/lib/mysql from old to new
5. start mysql on the new server and let it upgrade from 5.7 to 8.0

It's probably 10x faster....

If you aren't using partitioning for space management, check for tables needing OPTIMIZE