MySQL performance - 100Mb ethernet vs 1Gb ethernet

views:

246

answers:

+1 Q:

MySQL performance - 100Mb ethernet vs 1Gb ethernet

Hi All

I've just started a new job and noticed that the analysts computers are connected to the network at 100Mbps. The ODBC queries we run against the MySQL server can easily return 500MB+ and it seems at times when the servers are under high load the DBAs kill low priority jobs as they are taking too long to run.

My question is this... How much of this server time is spent executing the request, and how much time is spent returning the data to the client? Could the query speeds be improved by upgrading the network connections to 1Gbps?

(Updated for the why): The database in question was built to accomodate reporting needs and contains massive amounts of data. We usually work with subsets of this data at a granular level in external applications such as SAS or Excel, hence the reason for the large amounts of data being transmitted. The queries are not poorly structured - they are very simple and the appropriate joins/indexes etc are being used. I've removed 'query' from the Title of the post as I realised this question is more to do with general MySQL performance rather than query related performance. I was kind of hoping that someone with a Gigabit connection may be able to actually quantify some results for me here by running a query that returns a decent amount of data, then they could limit their connection speed to 100Mb and rerun the same query. Hopefully this could be done in an environment where loads are reasonably stable so as not to skew the results.

If ethernet speed can improve the situation I wanted some quantifiable results to help argue my case for upgrading the network connections.

Thanks Rob

+3 A:

Benchmark. MySQL has many tools for determining how long queries take. Odds are you have really bad queries. Use the slow query log.
Why are you transmitting/storing 500MB of data from/in MySQL?

Ryan Doherty 2010-04-16 22:04:06

+1 for asking why.

TheJacobTaylor 2010-04-16 22:04:34

OP here. Thanks for the suggestions. Updated the original post to address your 2 points.

Rob Penridge 2010-04-17 00:31:06

+1 A:

My suspicion is yes. It should be.

In the MySQL shell, I would run:

show full processlist

on the machine and check out the state of the queries. If you see any states similar to: "reading from net" or "writing to net" that would imply that network transmission is directly impacting MySQL. You can also look at IOStat results to see how much IO the system is using. If the system is on a managed switch, you might also want to check the load there.

Ref: show processlist

Ref: Status definitions

TheJacobTaylor 2010-04-16 22:04:11

OP Here. Thanks. I ran a simple select returning 1 million rows. The first the query executed the status alternated between "Writing to Net" and "Sending Data" for 30 secs and then to "sleep" for 30 secs while it wrote to the local HDD (I could hear it thrashing). During "sleep" stage, the process was still visible and until the HDD write finished the process remained. So step 1 for me will be to improve the write time on the local machine which is not a problem as I can compress the file to save on I/O time. It did seem to spend a bit more time on the "Writing to Net" then "Sending Data".

Rob Penridge 2010-04-17 00:52:44

Have you tried ensuring that you have client compression turned on? This will zip up the response automatically before transmission. Can you add more network cards to the machine? Bonded network cards can give you much higher throughput. You can even segregate the loads based on network connection if you would like.

TheJacobTaylor 2010-04-18 04:09:56

+2 A:

Divide the amount of data by the time of your query, you'll get your answer. If you're nearing the capacity of 100Mbps , you'll have IO problems.

nos 2010-04-16 22:05:17

ansaurus

tags:

views:

answers:

MySQL performance - 100Mb ethernet vs 1Gb ethernet

related questions