[ZBX-5359] Database migration to V2 schema too lengthy for preexisting databases and production environments Created: 2012 Jul 23 Updated: 2017 May 30 Resolved: 2012 Aug 07 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Installation (I) |
Affects Version/s: | 2.0.1 |
Fix Version/s: | 2.0.3rc1, 2.1.0 |
Type: | Incident report | Priority: | Critical |
Reporter: | Ken Smith | Assignee: | Unassigned |
Resolution: | Fixed | Votes: | 0 |
Labels: | database, performance, usability | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
Production 1.6 system on hosted virtual machine running Centos5. Version 2.0.1 test system on VM with Centos 5 and a MySQL 5.5 database. Migration of the data attempted on an i7 on FC13 with MySQL 5.1. The aim being, once migrated, to make the database available to the 2.0.1 system on the VM for testing prior to a "live" migration of a production environment. |
Issue Links: |
|
Description |
Have taken a copy of the production database (14G) and successfully migrated it to the 1.8 schema. On migrating it to the 2.0 schema, the script reaches this section of SQL in upgrade.sql SELECT e1.eventid,(SELECT e2.value At that point the process takes an eternity to run. I have let it run for 5 days and abandoned it. I have heard of others (see the Forum) running this bit of SQL for 11days. This length of database migration is not practical for a production environment. See my comments in the users forum. (http://www.zabbix.com/forum/showthread.php?t=27486) I see three potential resolutions:- (1) Revise the code so it runs in a realistic time on sensible hardware. |
Comments |
Comment by richlv [ 2012 Jul 24 ] |
not really a bug. note that small innodb buffer pool size will result in a long upgrade. |
Comment by richlv [ 2012 Aug 03 ] |
supposedly increased innodb buffer pool does not help - could you please dump the events table, compress it and somehow deliver it ? attaching here might not be the best as there might be sensitive data - but the method will depend on the size of the resulting archive |
Comment by Oleksii Zagorskyi [ 2012 Aug 06 ] |
Russian zabbix forum has also several topics about the hard "events" table upgrade. |
Comment by Alexander Vladishev [ 2012 Aug 07 ] |
Fixed in the development branch svn://svn.zabbix.com/branches/dev/ZBX-5359 |
Comment by Alexander Vladishev [ 2012 Aug 07 ] |
Only MySQL patches are improved. After improving the upgrade of the 'events' table took 50 minutes (~2000 triggers and ~8500000 events). |
Comment by Ken Smith [ 2012 Aug 07 ] |
I will download the new patch.sql and test it |
Comment by dimir [ 2012 Aug 08 ] |
Successfully tested, took 47 minutes in my case. Please review the small change in r29461. <Sasha> Thanks! |
Comment by Alexander Vladishev [ 2012 Aug 08 ] |
Fixed in pre-2.0.3 r29465 and pre-2.1.0 (trunk) r29466. |