major connection spike during "source dumpfile.sql"

[RichardBronosky]

I have a master server (with one slave) that I have noticed this on. When I connect to the master from my workstation via:

mysql -h master

and then try to load a 80MB to 500MB SQL file generated from mysql dump, like so:

source dumpfile.sql

It takes several minutes to load because of the effected indexes. But during that time I see my connection go from in the 20’s to in to 250’s even 500’s at one point. (According to the MySQL Network Monitoring and advisory server tool.)

Has anyone ever seen anything like this?

[Peter]

Well it is the extra load to import the data which can be the reason.

If you’re importing the same tables which are being accessed it also may be table locks issue.

Run SHOW PROCESSLIST and see what those connections are doing.

[RichardBronosky]

As an enterprise customer, I have been discussing this with MySQL AB also.

Here is what I have come up with:

[LIST=1]
[] It turns out that there is no problem with my mysql client.[LIST=1]
[] It is only making one connection.
[] The server is only creating one thread for the connection.
[/LIST]
[] My server receives about 50 connections per second.
[] It generally only ever has 20 connections open at one time, because the client gets on, does its select, and leaves so fast.
[] The duration of these other connections goes way up during the bulk loading process due to Disk I/O, CPU for indexing, and RAM for statement buffering. The number of concurrent connections maxes out when the duration of each goes from hundredths of a second to multiple seconds each.
[/LIST]

This is all very logical. Now I have to decide what to do about it. I’d like to have an app that can take a dump file and break the extended inserts up into a configurable number of records per statement, then execute the statements with a configurable delay in between. Does something like this exist?

I thought about using a proxy to do traffic shaping to limit the rate at which the server receives the SQL statements, but extended inserts won’t get broken up using this technique. Hmmm. I wonder how hard it would be to write a SQL insert aware proxy service. That way, even if the data imports are done using MySQL GUI Tools, Navicat, or MS SQL/SSIS via linked tables… It all get throttled if you connect to the proxy instead of directly to the server.

Peter, am I missing something?

BTW, I really enjoyed seeing you at MySQL Conf '07

[Peter]

Are you loading the same tables as those which accessed or completely different ones ?

If it is different set of tables you just need some throttling on servers so it does not take so much resources.

One idea is to load table by table if they are small with sleep in between.

If that does not work you can either patch mysqldump or write little sql-dump post processor which will add MySQL sleep() function between the lines so it can be spread over longer time.

You can change number of inserts in the bulk by max_allowed_packet changing ad you also can set mysqldump to use single row inserts which makes it slower but you can spread it to have very low side load