MariaDb servers below 10.2.6 (all EoL)
MySql servers in general (tested in 9.1.0)
Description
A recent change has caused a performance degradation on loads to older MariaDb servers (below 10.2.6), which are out of support, but maybe more relevant also to mySql servers (servers that lack the MARIADB_CLIENT_STMT_BULK_OPERATIONS feature flag).
Maybe I'm missing the context on why this was needed, but in case this is an unintended change:
In our setup, for those servers, the flow in executeInternalPreparedBatch() would eventually call executeBatchPipeline(), now, given the change, it's calling executeBatchStd().
I didn't dig into why, but in my testing that causes a load to take substantially longer. The whole process of extracting and loading a small amount of data jumps from 8s to over 1 min with just the update from 3.5.0 to 3.5.1.
For the loading into the dabase, we are calling Statement.executeBatch()
Attachments
Issue Links
is caused by
CONJ-1208permit bulk for INSERT ON DUPLICATE KEY UPDATE commands
A recent change has caused a performance degradation on loads to older MariaDb servers (below 10.2.6), which are out of support, but maybe more relevant also to mySql servers (servers that lack the MARIADB_CLIENT_STMT_BULK_OPERATIONS feature flag).
Maybe I'm missing the context on why this was needed, but in case this is an unintended change.
In our setup, for those servers, the flow in executeInternalPreparedBatch() would eventually call executeBatchPipeline(), now, given the change, it's calling executeBatchStd().
I didn't dig into why, but in my testing that causes a load to take substantially longer. The whole process of extracting and loading a small amount of data jumps from 8s to over 1 min with just the update from 3.5.0 to 3.5.1.
For the loading into the dabase, we are calling Statement.executeBatch()
A recent change has caused a performance degradation on loads to older MariaDb servers (below 10.2.6), which are out of support, but maybe more relevant also to mySql servers (servers that lack the MARIADB_CLIENT_STMT_BULK_OPERATIONS feature flag).
Maybe I'm missing the context on why this was needed, but in case this is an unintended change:
In our setup, for those servers, the flow in executeInternalPreparedBatch() would eventually call executeBatchPipeline(), now, given the change, it's calling executeBatchStd().
I didn't dig into why, but in my testing that causes a load to take substantially longer. The whole process of extracting and loading a small amount of data jumps from 8s to over 1 min with just the update from 3.5.0 to 3.5.1.
For the loading into the dabase, we are calling Statement.executeBatch()
executeBatchPipeline is for sending PREPARE + EXECUTE immediatly ( in same TCP-IP packet if possible). this can only be done when having mariadb 10.2+, not with mysql server or server before that. The reason is since mariadb 10.2, prepare statement value -1 for EXECUTE can be used to indicate previous prepared statement.
If MARIADB_CLIENT_STMT_BULK_OPERATIONS, pipeline or bulk cannot be used, resulting, in just a loop of insert.
There was an rewrite query feature in 2.x version of the connector (option ``rewriteBatchedStatements`) that did have good performance, but less than the current bulk and was limited to INSERT commands only, while bulk permit any INSERT/UPDATE/DELETE queries.
Since 10.2 is EOL, this feature has been removed from 3.x version, Bulk being better for the job.
I don't know if you were using that option, but everything seems as expected. If you have more info, please tell.
Diego Dupin
added a comment - executeBatchPipeline is for sending PREPARE + EXECUTE immediatly ( in same TCP-IP packet if possible). this can only be done when having mariadb 10.2+, not with mysql server or server before that. The reason is since mariadb 10.2, prepare statement value -1 for EXECUTE can be used to indicate previous prepared statement.
If MARIADB_CLIENT_STMT_BULK_OPERATIONS, pipeline or bulk cannot be used, resulting, in just a loop of insert.
There was an rewrite query feature in 2.x version of the connector (option ``rewriteBatchedStatements`) that did have good performance, but less than the current bulk and was limited to INSERT commands only, while bulk permit any INSERT/UPDATE/DELETE queries.
Since 10.2 is EOL, this feature has been removed from 3.x version, Bulk being better for the job.
I don't know if you were using that option, but everything seems as expected. If you have more info, please tell.
Thanks for the quick reply. Yes, initially I was not going to open an issue because we observed this behaviour on unsupported MariaDb servers, but then I decided to test MySQL and also obeserverd the same. Will do a more thorough testing with MySQL now. But yes, in that case we are using just insert statements on MySQL.
Lucas Collares Favaron Galvao
added a comment - - edited Thanks for the quick reply. Yes, initially I was not going to open an issue because we observed this behaviour on unsupported MariaDb servers, but then I decided to test MySQL and also obeserverd the same. Will do a more thorough testing with MySQL now. But yes, in that case we are using just insert statements on MySQL.
docker run -p 3309:3306 --name mysql -e MYSQL_ROOT_PASSWORD=admin -d mysql:latest
Lucas Collares Favaron Galvao
added a comment - Here is a reproduction project, just changing the driver version increases the insert time almost ten fold.
https://github.com/lcgal/Reproduction-CONJ-1216
I've been using a mySql container in DD:
docker run -p 3309:3306 --name mysql -e MYSQL_ROOT_PASSWORD=admin -d mysql:latest
ok, thanks for this project,
That show clearly the problem. Multiple execution pipeline was used and is not not, and that is clearly something that can be done with old versions.
This is clearly an issue and will be corrected
Diego Dupin
added a comment - ok, thanks for this project,
That show clearly the problem. Multiple execution pipeline was used and is not not, and that is clearly something that can be done with old versions.
This is clearly an issue and will be corrected
Bug
Major
CONJ-1208 permit bulk for INSERT ON DUPLICATE KEY UPDATE commands
executeBatchPipeline is for sending PREPARE + EXECUTE immediatly ( in same TCP-IP packet if possible). this can only be done when having mariadb 10.2+, not with mysql server or server before that. The reason is since mariadb 10.2, prepare statement value -1 for EXECUTE can be used to indicate previous prepared statement.
If MARIADB_CLIENT_STMT_BULK_OPERATIONS, pipeline or bulk cannot be used, resulting, in just a loop of insert.
There was an rewrite query feature in 2.x version of the connector (option ``rewriteBatchedStatements`) that did have good performance, but less than the current bulk and was limited to INSERT commands only, while bulk permit any INSERT/UPDATE/DELETE queries.
Since 10.2 is EOL, this feature has been removed from 3.x version, Bulk being better for the job.
I don't know if you were using that option, but everything seems as expected. If you have more info, please tell.