4

I have a large MySQL table, with proper indices etc. I run a simple select * query from a remote machine and I expect a large result set.

My problem is that when I run the query, the result set returns at a maximum data transfer speed of ~300 KBytes/sec. I created the same table, and run the same query on SQLServer Express 2008 R2, and the results returned at a transfer speed of 2MBytes/second (my line limit).

The server machine is Windows Server 2008 R2 x64, Quad core, 4GB RAM and the the MySQL version is 5.6.2 m5 64-bit. I tried disabling the compession in the communication protocol but the results where the same.

Does anyone have an idea as to why this is happening ?

--theodore

Theodore Zographos
  • 2,215
  • 1
  • 24
  • 23
  • A whole query process usually contains of alot of individual steps, p.e. preparing, checking permissions, lockings, sorting the results etc ... and ofc sending the data back to the client. Are you sure that you're only talking about this data transfer, or could it be the query has another bottleneck? – Bjoern Jul 29 '11 at 09:07
  • 1
    That's probably because it's remote... maximum throughput reached. Exactly how much large is this result set? – Dor Jul 29 '11 at 09:09
  • The only thing that occurs to me is how much memory have you made available to MySQL in the configuration file my.ini. If MySQL is having to cache a lot of stuff on disk them the transmission speed may be effected. – Jaydee Jul 29 '11 at 09:17
  • Would fit better on http://serverfault.com/ ? – Mchl Jul 29 '11 at 09:19
  • @Dor: Maximum throughput is 100Mbps. On SQLServer 2008 R2 Express, I am getting maximum throughput. – Theodore Zographos Aug 01 '11 at 12:21
  • @Jaydee: MySQL is allocated 2.5 GB RAM for it's query buffer. – Theodore Zographos Aug 01 '11 at 12:22
  • @Bjoern: It's a simple SELECT * query. As I explained in the question text, the SAME query on SQLServer is returning data with the maximum throughput. – Theodore Zographos Aug 01 '11 at 12:24
  • Try profiling the query, here's a good place to start how to do this: http://webmonkeyuk.wordpress.com/2011/04/18/mysql-servers-built-in-profiling-support/ – Bjoern Aug 10 '11 at 10:09
  • how many rows are we talking about here? have you seen something like: http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/ –  Aug 12 '11 at 09:22

1 Answers1

1

You might be comparing apples to oranges.

I'd run SELECT * on the MySQL server, and see what kind of data rate you get for retrieving data on the server locally -- without the additional constraint of a network.

If that's slow also -- then it isn't the network's fault.

When the MySQL setup program runs, it asks the person setting up MySQL what role MySQL is going to play on the hardware -- i.e., Development Server, Shared Server, Dedicated.

The difference in all of these is how much memory MySQL will seek to consume on the Server.

The slowest setting is Development (use the least memory), and the fastest one is Dedicated (attempt to use a lot of memory). You can tinker with the my.ini file to change how much memory MySQL will allocate for itself, and/or google 'my.ini memory' for more detailed instructions.

The memory that MySQL is using (or isn't, as the case may be), will make a huge difference on performance.

First, check to see what the speed is retrieving data locally on the MySQL server is. If it's slow, the network isn't the problem -- check MySQL's memory usage -- ideally give it as much as possible. And of course, if it's fast, then either the network and/or some piece of database middleware (ODBC?) or tool-used-to-display-the-data -- is slow...

One more thing -- try the SELECT * TWICE... why? The second time some or all of the results (again, depending on memory) should be cached... the second time it should be faster...

Also, don't forget to restart MySQL when changing the my.ini file (and create a backup before you make any changes...)

Peter Sherman
  • 304
  • 2
  • 3