| major connection spike during "source dumpfile.sql" [message #1547] |
Mon, 23 July 2007 17:23  |
 RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
I have a master server (with one slave) that I have noticed this on. When I connect to the master from my workstation via:
and then try to load a 80MB to 500MB SQL file generated from mysql dump, like so:
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?
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
|
| Re: major connection spike during "source dumpfile.sql" [message #1633 is a reply to message #1547 ] |
Thu, 16 August 2007 15:10  |
RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
As an enterprise customer, I have been discussing this with MySQL AB also.
Here is what I have come up with:
- It turns out that there is no problem with my mysql client.
- It is only making one connection.
- The server is only creating one thread for the connection.
- 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.
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
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
| Re: major connection spike during "source dumpfile.sql" [message #1641 is a reply to message #1633 ] |
Fri, 17 August 2007 06:22 |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
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
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
| Re: major connection spike during "source dumpfile.sql" [message #1645 is a reply to message #1641 ] |
Sat, 18 August 2007 01:42 |
RichardBronosky
Messages: 20
Registered: June 2007 |
Junior Member |
|
|
- Most of the time new ones. But some times existing, currently used ones.
- Yes, this would help for most of my needs.
- Actually, the tables are very huge. And often the entire problematic load in only one table.
- I did not know about sleep(). Thank you for this gem!
- I've been looking into max_allowed_packet, I think I will have to use it. I don't like the single inserts because they are not just slower, but are slower due to overhead that is avoided by using extended-inserts.
I think I will patch mysqldump to add an option like --sleep-between-inserts=<duration>
.+# Richard Bronosky #+.
MySQL DBA, MythTV User
|
|
|
Members
Search
Help
Register
Login
Home
