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