5.0 -> 6.0 DB upgrade for primary keys: partitioned and performance advice

[LenR]

Environment is RHEL based, Percona mysql 8, partitioned history using stored procedures. This is a 8400 host server at 250 nps, 30 days history kept.

Followed the instructions in the doc thru running history_pk_prepare.sql.

Used the partition definitions in the latest "configuration backup" to rebuild the partition tables. Normal partition maintenance would only build current and future partitions, not those for the prior 30 days. That would work, but retain more history (using more space) than necessary.

Started with the mysqlsh copy process, the first table worked OK, history_uint was slowly progressing, then filled disk overnight. The disk space problem was binlogs, the slowness was lack of tuning, mysql was running with defaults. Steps to make things much faster:

1. Skip bin logs for the duration of this procedure. They will be turned back on later, we are not replicating the data.
2. Enable large pages and set vm.nr_hugepages=1000
3. Set innodb_buffer_pool_size=2G (Mysql and all zabbix stack is on the same 8Gb VM)

About 30% of history_unit had been processed before in about 20 hours. With these changes restarting the imporTable for history_uint completed in about 1:15.

This emphasizes the rule "TUNE MYSQL"!

[cyber]

Hello
Did you have a fix on this issue? Facing same issue but no response from anyone and couldn't find this topic troubleshooting in google.

I guess reading the topic is for wussies... There is no "issue", just a friendly reminder to tune your things properly...

And somehow I have already seen that same wording in random posts... Just feeding the forum with posts to edit later and distribute spam links?

[Melissa25]

When upgrading a database from version 5.0 to 6.0 and considering partitioning and performance improvements for primary keys, here are some recommendations:

Partitioning: Partitioning can help improve query performance for large tables. Consider partitioning tables based on the primary key if there is a large volume of data. For example, if a table has a date-based primary key, consider partitioning the table by date ranges.

Primary key changes: In version 6.0, the primary key format has changed from a 32-bit integer to a 64-bit integer. This means that when upgrading, the primary key column should be changed to the new format to take advantage of the increased capacity.

Indexes: Make sure all primary keys have an associated index. Primary keys are used to uniquely identify rows in a table, and having an index on the primary key can help improve query performance.

Constraints: Ensure that all primary keys have constraints to ensure data integrity. Constraints can prevent the insertion of duplicate or null values in the primary key column.

Performance tuning: Consider tuning the database server for optimal performance, including increasing memory and disk space, and configuring caching and buffer pools.

Overall, upgrading from 5.0 to 6.0 and implementing partitioning and performance improvements for primary keys requires careful planning and testing to ensure optimal performance and data integrity.

[Melissa25]

When upgrading a database from version 5.0 to 6.0 and considering partitioning and performance improvements for primary keys, here are some recommendations:

Partitioning: Partitioning can help improve query performance for large tables. Consider partitioning tables based on the primary key if there is a large volume of data. For example, if a table has a date-based primary key, consider partitioning the table by date ranges.

Primary key changes: In version 6.0, the primary key format has changed from a 32-bit integer to a 64-bit integer. This means that when upgrading, the primary key column should be changed to the new format to take advantage of the increased capacity.

Indexes: Make sure all primary keys have an associated index. Primary keys are used to uniquely identify rows in a table, and having an index on the primary key can help improve query performance. ForemostPayOnline

Constraints: Ensure that all primary keys have constraints to ensure data integrity. Constraints can prevent the insertion of duplicate or null values in the primary key column.

Performance tuning: Consider tuning the database server for optimal performance, including increasing memory and disk space, and configuring caching and buffer pools.

Overall, upgrading from 5.0 to 6.0 and implementing partitioning and performance improvements for primary keys requires careful planning and testing to ensure optimal performance and data integrity.

Hello, Can you tell me my suggestion is work for you?.

Best Regard
Melissa

[cyber]

ooh.. talking to self ... slight schizophrenia ? Or we have another bot here?