Sql Dump Slow On Tables With Many Rows

[Zaph]

I’ve reported this before, but can’t find it again, so here goes – when creating an SQL dump from a remote database, the export gradually slows down more and more when exporting a table with many rows. At first every 100 rows exported take a fraction of a second, but after 6 hours now it’s down to 3 seconds per 100 rows, having exported 2mio out of 2.8mio rows. The table consists of 5 ints – see anonymized CREATE TABLE query below.

 

Preferences -> Export options are Custom size=1 and Chunk size=25

 

I’m running Windows 7 with the latest SQLYog version.

 

What I noticed today is that at the same time Windows’ Resource Monitor shows a lot of activity in one AppDataRoamingSQLYogconnrestore.db (over 100.000 B/sec, file size is 1.5G:cool: and several instances of AppDataRoamingSQLYogconnrestore.db-journal (around 100.000 B/sec) – writes only, for all of them. This is most likely not related, but struck me as odd nonetheless.

 

If you need any more info, please let me know!

 

 

CREATE TABLE `mytable` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `remoteid1` int(11) NOT NULL,

  `remoteid2` int(11) NOT NULL,

  `remoteid3` int(11) NOT NULL,

  `remoteid4` int(11) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `u1` (`remoteid1`,`remoteid2`,`remoteid3`),

  KEY `r1` (`remoteid1`),

  KEY `r2` (`remoteid2`),

  KEY `r3` (`remoteid3`),

  KEY `r4` (`remoteid4`)

) ENGINE=InnoDB AUTO_INCREMENT=5361018 DEFAULT CHARSET=latin1

[peterlaursen]

Please tell: do you experience this with more than one server (in case you have more)?

 

I wonder if it could besome memory exhaustion on the server causing swapping or some user quota being exceeded.. Please try to reduce the CHUNK setting – refer http://faq.webyog.com/content/24/101/en/about-chunks-and-bulks.html

[Zaph]

Yes, I experience this on all servers I work with – therefore I suspect it’s not a server issue. edit: it even happens when I dump the same table from my local MySQL database, so it’s also not network-related.

 

Additional info – as I was curious I let it run: SQLYog memory usage remained a very decent 50MB (out of 16G:cool: and CPU usage was next to 0%. The entire database was dumped in 13,5 hours with this one table accounting for 13,25 hours. After that table was finally done, the next ones were dumped with the same speed as one would expect, so it’s a per-table and not a per-connection performance issue.

[Zaph]

Hmm…another table containing 850K rows and a spatial column slows down even faster, making it practically impossible to download – I guesstimate that that one would take more than 24 hours.

[peterlaursen]

OK .. we will try hard to reproduce this.

 

Are you wiling to have a shared session sometime next week? We may need that and we may even need to run some special debug build on your environment to figure out why it is happening.

[Zaph]

Yes, I’d be happy to. Next tuesday, wednesday and thursday afternoons (CET) would be fine, so just let me know.

[peterlaursen]

In between, could you please try to REPAIR TABLE or OPTIMIZE TABLE (REPAIR for MyISAM tables, OPTIMIZE for innoD:cool: for affected table(s). This will defragment table files and rebuild indexes. In particular with MyISAM you should expect to do this from time to time. I have seen recommendations to REPAIR MyISAM tablse once per week (though this is properly ‘overkill’). You may also try to back up the table(s) wiht another tool (mysqldmp or whatever) and see if it makes any dfference. 

 

In order to communicate details for the shared session privately, please send a mail to [email protected] with a reference to this discussion. 

[Zaph]

Tried both repair and optimize on myisam and innodb tables, to no avail. Email sent!

[Zaph]

I wonder if it could besome memory exhaustion on the server causing swapping or some user quota being exceeded.. Please try to reduce the CHUNK setting – refer http://faq.webyog.com/content/24/101/en/about-chunks-and-bulks.html

 

Actually it turned out that raising the CHUNK setting is the solution. Queries using LIMIT [offset], [count] get progressively slower as [offset] increases. Larger CHUNKs means less searching, means faster execution.

 

And when I say faster, I mean faster – where the original attempt with CHUNK=25 took almost 14 hours, a test today with CHUNK=10000 dumped the same database (resulting in a 1.25GB .sql-file) in a mere 14 minutes (!).

 

So, as far as I’m concerned: problem solved. Still a little puzzled why no one said “Oh, that’s easy – just increase CHUNK!”

[ioriten]

Hello

I’m new to databases and having an issue with an application i’m using which seems to get slower as the size of the database grow. I’ve tried changing the chunk size but does not seem to change everything though it seems the problem i have is similar to what ZAPH had. Please kindly assist.

[Author]

Posts