Bug #46712 | Auto_increment work incorrectly when using triggers and NDB Cluster | ||
---|---|---|---|
Submitted: | 14 Aug 2009 9:16 | Modified: | 14 Sep 2009 11:09 |
Reporter: | chris lin | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
Version: | mysql-5.1-telco-6.2 | OS: | Any |
Assigned to: | Martin Skold | CPU Architecture: | Any |
Tags: | auto_increment, NDB Cluster, ndb-6.2.15 ndb7.0.6, trigger |
[14 Aug 2009 9:16]
chris lin
[17 Aug 2009 13:22]
Jørgen Austvik
Could be related to bug#38051
[24 Aug 2009 2:01]
chris lin
Could this bug be fixed?
[31 Aug 2009 5:41]
chris lin
Bug 38051 is duplicate of bug#38034, and bug38034 is fixed now, so I push the patch of bug38034 to mysql-5.1-telco-6.2.15 and test again, unfortunately, I found bug46712 is the same.
[31 Aug 2009 9:47]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/81989 2968 Martin Skold 2009-08-31 Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, changed prefetch cache to only use thd->variables.ndb_autoincrement_prefetch_sz, added test case modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test sql/ha_ndbcluster.cc
[31 Aug 2009 13:38]
chris lin
Thanks, Martin. I have tested this patch on mysql-5.1-telco-6.2.15, it works correctly when ndb_autoincrement_prefetch_sz=1. From the patch, what I understand is that ndb_autoincrement_prefetch_sz must be set to 1 to keep the auto_increment column increase one by one on each sql node. Is it right? When i set ndb_autoincrement_prefetch_sz more than 1, the patch is noneffective. To evaluate the performance, I insert 25,000 rows into a table, the table has no trigger on it. When ndb_autoincrement_prefetch_sz=1, it needs about 33.3 seconds with this patch, and about 18.1 seconds without this patch; when ndb_autoincrement_prefetch_sz=16, it needs about 20.3 seconds whih patch, and about 17.5 without patch. We can find that without this patch, no matter how to set ndb_autoincrement_prefetch_sz, the INSERT performance is little difference. But there is much different with this patch. For batch insert, there is little difference. Is this correctly?
[31 Aug 2009 14:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82022 2969 Martin Skold 2009-08-31 Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, increasing prefetch when batch size is known to not degrade table load performance modified: sql/ha_ndbcluster.cc
[31 Aug 2009 14:56]
Martin Skold
Yes, your analyses seems correct. The patch corrects the faulty code that sets the auto_increment prefetch to the internal Ndb default (32) when triggers are executed. The auto_increment prefetch is an optimization that the user can control. Setting ndb_autoincrement_prefetch_sz=32 should give you about the same performance as before. The reason why the mysqld default setting for ndb_autoincrement_prefetch_sz is one is that is what users expect, but experienced cluster users should know to increase this for better performance. Note, however, the second commit which ensures the performance when batching inserts with known number of rows is not decreased.
[1 Sep 2009 2:17]
chris lin
Setting ndb_autoincrement_prefetch_sz=1, and then execute INSERT ... SELECT ... to insert 10,000 records, it needs about 5.88 seconds with second patch, and about 3.17 seconds with first patch. The performance of batching inserts is decreased.
[1 Sep 2009 6:38]
Martin Skold
Yes, this is expected, the old code guessed a prefetch size based on the internal default for INSERT ... SELECT ..., because the exact number of rows is not known. Guessing a value will sometimes cause holes, if one wants total predictability based on the ndb_autoincrement_prefetch_sz setting the performance will suffer, unless one increases it's value.
[1 Sep 2009 8:04]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82087 2970 Martin Skold 2009-09-01 Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, added same optimization as prior for increasing prefetch for batched inserts modified: sql/ha_ndbcluster.cc sql/ha_ndbcluster.h
[1 Sep 2009 8:05]
Martin Skold
Added similar optimization as previously for batched inserts.
[1 Sep 2009 9:40]
chris lin
Great! The optimization is efficient. According to my testing, it seems that the batching insert performance is better than previously. Everything works fine now. Thanks, Martin.
[1 Sep 2009 10:11]
Martin Skold
Total patch, for review
Attachment: patch.txt (text/plain), 11.24 KiB.
[1 Sep 2009 10:24]
Martin Skold
Total patch, for review (fixed comment)
Attachment: patch.txt (text/plain), 11.29 KiB.
[3 Sep 2009 7:35]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82279 2975 Martin Skold 2009-09-03 Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, defining m_rows_to_insert=1 as default, even when start_bulk_insert is nor called, simplified code in get_auto_increment. modified: sql/ha_ndbcluster.cc
[3 Sep 2009 7:49]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82282 2976 Martin Skold 2009-09-03 Bug #46712 Auto_increment work incorrectly when using triggers and NDB Cluster, added test case. modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/t/ndb_auto_increment.test
[3 Sep 2009 9:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82295 3028 Martin Skold 2009-09-03 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_trigger.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_trigger.test sql/ha_ndbcluster.cc
[3 Sep 2009 9:55]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82310 2982 Martin Skold 2009-09-03 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_trigger.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_trigger.test sql/ha_ndbcluster.cc
[3 Sep 2009 11:48]
Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (version source revid:martin.skold@mysql.com-20090903095452-7ukx3yiaeltk2mgy) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[3 Sep 2009 12:02]
Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:martin.skold@mysql.com-20090903115020-aqt0bnf1s1m458c3) (version source revid:martin.skold@mysql.com-20090903091350-l7if6pbajxsawq9z) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[3 Sep 2009 12:50]
Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:martin.skold@mysql.com-20090903125016-bd6hb0ukbdasnj23) (version source revid:martin.skold@mysql.com-20090903074950-p8ejthvvbbatmv0g) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[3 Sep 2009 15:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82339 2982 Martin Skold 2009-09-03 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_trigger.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_trigger.test sql/ha_ndbcluster.cc
[3 Sep 2009 15:43]
Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (version source revid:martin.skold@mysql.com-20090903152059-p5762oa1wy9b6o6f) (merge vers: 5.1.35-ndb-7.1.0) (pib:11)
[7 Sep 2009 9:31]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/82563 2973 Martin Skold 2009-09-07 [merge] Merge modified: mysql-test/suite/ndb/r/ndb_auto_increment.result mysql-test/suite/ndb/r/ndb_trigger.result mysql-test/suite/ndb/t/ndb_auto_increment.test mysql-test/suite/ndb/t/ndb_trigger.test sql/ha_ndbcluster.cc storage/ndb/include/kernel/signaldata/CloseComReqConf.hpp storage/ndb/src/kernel/blocks/ERROR_codes.txt storage/ndb/src/kernel/blocks/cmvmi/Cmvmi.cpp storage/ndb/src/kernel/blocks/dbtc/Dbtc.hpp storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp storage/ndb/src/kernel/blocks/dbtup/DbtupDiskAlloc.cpp storage/ndb/src/kernel/blocks/dbtup/DbtupMeta.cpp storage/ndb/src/kernel/blocks/qmgr/Qmgr.hpp storage/ndb/src/kernel/blocks/qmgr/QmgrMain.cpp storage/ndb/test/ndbapi/testNdbApi.cpp storage/ndb/test/run-test/autotest-boot.sh storage/ndb/test/run-test/daily-basic-tests.txt
[14 Sep 2009 11:09]
Jon Stephens
Documented bugfix in the NDB-6.2.19, 6.3.27, 7.0.8 changelogs, as follows: Using triggers on NDB tables caused ndb_autoincrement_prefetch_sz to be handled as the NDB kernel's internal default value (32) and the value for this variable as set on the cluster's SQL nodes to be ignored. Closed.