DB Writer writing only 25 rows per second to MySQL database

[gilbert12]

Hi there,

After experiencing sluggish DB Writer behaviour, I have created a simple workflow to test the speed at which a table is written into an MySQL database. The process is very simple. It reads a table from a DB and then writes back a copy of the table.

I have tried both the MySQL Connector and the DB Connector. Here are the processes.

I am on Knime 4.0.2 on an iMac.

The table has 2,200 rows and 11 columns (6 strings, 4 integers and one long). The strings and numbers are small.

The speed by which this table is written to the MySQL database is only 25 rows per second.

Is this what I should expect? If so, is there any reason why this is so slow? And is there a way to speed this up?

I have made no changes to the defaults in the advanced settings of the connector node.

Thanks in advance for your help!

Gilbert

[mlauber71]

Could you try and increase the batch size in the DB Writer node

DB Writer — NodePit

[gilbert12]

I am using Knime 4.0.2 where the batch size in the DB Writer node is set to 1000 by default. With that setting, it writes 25 rows per second.

I have set the batch size to 100 and re-run the test. This gives me roughly the same performance, 25 rows per second.

[gilbert12]

UPDATE:

I just ran a test outside of Knime with the database tool DBeaver, doing the same operation (adding the 2200 rows from the CSV file to the MySQL table). It took the same time than in Knime: 25 rows per second.

So, there seems to be an issue here with filling up a / my database table with records from an external data source. I am not a database expert so I did some googling. It seems that there are slow and fast ways of getting your external data into a database table. I will need to check this out further.

Thanks!

Gilbert

[gilbert12]

UPDATE 2:

I ran the same test with a different database tool (Sequel Pro), where the import of 2200 rows into the MySQL table only took 3 seconds!

Hence it seems there are big performance differences in the way these tools (Knime, DBeaver, Sequel Pro, etc.) write data into a MySQL table.

The solution when using Knime would be to use a “DB SQL Executor” node that runs whatever query Sequel Pro is running in the background to get the data into the database. Could that work?

[gilbert12]

SOLVED:

[UPDATE: SEE WARNING BELOW by user “beginner”]

Use the “DB Loader” node instead of the “DB Writer” node. With “DB Loader”, writing the 2200 lines to the MySQL table took less than a second.

The same Knime table with a million rows now writes to the MySQL table in 15 seconds.

[beginner]

I would be extremely careful using the loader with MySQL:

Most databases do not perform data checks when loading the data into the table which might lead to a corrupt data table . The node does some preliminary checks such as checking that the column order and column names are compatible. However it does not check the column type compatibility. So before using this node please make sure that the column types of the KNIME columns and the database columns are compatible.

And MySQL is known to do weird stuff with coercing data types so they fit. IMHO this is not a solution as 25 rows / second tells me there simply is something wrong with the database. Be it configuration or broken indexes. I would rather fix that than use this dangerous crutch.

InnoDB doesn’t cope well with ‘random’ primary keys. Try a sequential key or auto-increment

What’s the primary key of that table? That alone could explain the issue. Again this is for sure not a KNIME issue but a huge red flag that something is off with your DB.

[gilbert12]

Thank you for your feedback. The primary key is sequential and an INT of length 11.

[beginner]

Was just one of many possible reasons. 25 rows per second simply is too slow so something weird must be going on and the fact it’s fast with the loader tells you it’s not your hardware. Another option are constraints / indexes as loader deactivates these.

[gilbert12]

Thanks very much for the tips! You probably guessed I am not a DB expert , so I’ll ask my work colleagues for help to find the culprit!