Page MenuHomePhabricator

Database inserts are slow at the replicated databases
Closed, ResolvedPublic

Description

This weekend I played around with the databases on Toollabs. I'm using the commonswiki.labsdb server. I compared the speed with the Toolserver databases.

SELECTs on commonswiki_p are very fast. I created a databases p50380g50518__heritage_p on this server. INSERTs seem to be very slow and sometimes seem to get stuck. I think some performance tuning needs to happen here. The program I'm using tries to do about 1.000.000 inserts. The whole program takes about an hour to complete on the Toolserver (when I last checked), on labs it's several times slower.

Ps. Now is probably a good time to add a database component.


Version: unspecified
Severity: normal

Details

Reference
bz48851

Event Timeline

bzimport raised the priority of this task from to Medium.Nov 22 2014, 1:33 AM
bzimport added a project: Toolforge.
bzimport set Reference to bz48851.

Is anyone working on this? This blocks us from setting up the monuments database at toollabs.

The issue is known, and a fix is forthcoming shortly (basically, user databases will be moved to a different filesystem)

Apparently this will be fixed when the database is migrated to run on a native filesystem in a week or so, which should apparently fix this issue.

afeldman wrote:

I noticed that the tables in p50380g50518__heritage_p were all myisam. No effort had been made to tune for myisam performance. I just increased the bulk_insert_buffer_size and key_buffer_size which should be relevant to your workload. Can you report on any improvement?

(In reply to comment #0)

This weekend I played around with the databases on Toollabs. I'm using the
commonswiki.labsdb server. I compared the speed with the Toolserver
databases.

SELECTs on commonswiki_p are very fast. I created a databases
p50380g50518__heritage_p on this server. INSERTs seem to be very slow and
sometimes seem to get stuck. I think some performance tuning needs to happen
here. The program I'm using tries to do about 1.000.000 inserts. The whole
program takes about an hour to complete on the Toolserver (when I last
checked), on labs it's several times slower.

Ps. Now is probably a good time to add a database component.

I fired up the program that harvests all the tables. Takes about an hour to complete on the toolserver (maybe two).

It crashed at some point because I forgot to create a table, these are the times:

real 265m22.993s
user 11m30.679s
sys 1m4.912s

That's much slower than the Toolserver.

After three months nothing changed. I'm going to stop wasting time on this.

Oh, it uses MyISAM because that's the default engine and it supports full text search (something I don't believe InnoDB supports)

Marc suggested I used commonswiki.labsdb, switched to tools-db and that one is much faster. Changed the subject to reflect that.

Adding a note: apparently the replica databases are on a different data center than toollabs, with a ping time of 26ms. This probably explains the slow inserts, if they were not batched.

26ms? Wow, can you get coffee on the way back?

Should be fixed now, since everything is in eqiad. Do re-open if this still exists.