CODEWITHSUNDEEP
mysql
Julian
Julian

Reputation: 115

Does MySQL have improved performance when inserting into two different tables?

I'm using a program which has multiple nodes (servers) inserting rows to a single MySQL server table.

Since they are writing to the same table, locks will obviously be employed and each node will have to wait until it can insert.

I felt that each node could write to a separate table to speed up operations, and later we could consolidate rows from all tables into a single table, after the program has finished.

My supervisor says that won't work because it'll take the same amount of time for MySQL to write to multiple tables.

Is that true? Won't there be a performance/time difference if I write to separate tables? I thought MySQL would use separate threads. Or is it because there would be a delay because multiple processes inserting would mean more load on the hard disk?

Upvotes: 0

Views: 289

Answers (1)

Shadow
Shadow

Reputation: 34232

In general, MySQL does not use separate threads to write to separate tables. However, the way writes are managed depends on the table engine in the end, not strictly by MySQL server.

MySQL documentation describes the threads used by MySQL on different platforms:

  • for various connections
  • replication
  • signals
  • disk flushes
  • event scheduler
  • innodb specific threads

But you can see from the list that there is no separate thread per writing to tables.

You also cannot generally say that writing to several tables at the same time is faster than writing the same data to the same table. First of all, you need to write the same data regardless how many tables you store it.

If you have a single table, then obviously locks on that table may slow inserts into that table down, but MySQL's default innodb table type handles such scenarios quite well - it was designed for highly concurrent use.

If you write the same data to multiple tables, then you have to issue multiple sql statements, you also need to add extra reference data to make sure that the data in various tables can be joined together. You do not have this overhead with a single table approach.

To sum up: you cannot assume that a multi-table insert approach is faster. You can do detailed testing to analyse and prove that a multi-table approach in you particular case works better, than a single table approach, but the result will have to be very convincing to change the design even in this particular case, and still some config changes to MySQL may offer a better way out than a design change.

Upvotes: 1

Related Questions