Windows – MySQL 5.5 on Windows server is horribly slow

MySQLwindows

I have had no luck getting MySQL 5.5 to be as fast as 5.1 or MariaDB on the exact same hardware/database/environment under Windows server 2003R2 or 2008R2.

My benchmarks from our application:

MySQL 5.5 + CentOS 5.2 (XenServer Virtual) = 28 seconds (box is "busy" not buried)
MariaDB (5.1) + Windows 2003 (Physical box) = 130 seconds (box is 2% busy)
MySQL 5.1 + Windows 2003 (Physical box) = 170 seconds (box is 2% busy)
MySQL 5.5 + Windows 2003 (Physical box) = 305 seconds (As high as 600 seconds...) (box is 2% busy)

The only difference between these runs is the removal of skip-locking and the running of mysql_upgrade.exe to update some tables for stored procs on 5.5.

Yes, I know it's a release candidate, I'm feeding that back to MySQL as well.

No slow queries are logged, it doesn't think it's being slow, it just is.

I'm going to start tearing into the queries themselves to see if the INSERT/SELECT plans have gone buggo on 5.5.

Best Answer

Take a look at http://bugs.mysql.com/59133 . It could be flushing to disk most of the times (box is 2% busy is a strong hint for it) You might after careful consideration want to set a combination of

innodb_support_xa=0

innodb_flush_log_at_trx_commit = 0 # or 2?

sync_binlog=0

and see what happens.

Once you get your load CPU-bound, then it makes sense to use Win2008R2, some improvements specifically address Vista+

Related Solutions

Windows – How to find what process is holding a file open in Windows

I've had success with Sysinternals Process Explorer. With this, you can search to find what process(es) have a file open, and you can use it to close the handle(s) if you want. Of course, it is safer to close the whole process. Exercise caution and judgement.

To find a specific file, use the menu option Find->Find Handle or DLL... Type in part of the path to the file. The list of processes will appear below.

If you prefer command line, Sysinternals suite includes command line tool Handle, that lists open handles.

Examples

c:\Program Files\SysinternalsSuite>handle.exe |findstr /i "e:\" (finds all files opened from drive e:\"
c:\Program Files\SysinternalsSuite>handle.exe |findstr /i "file-or-path-in-question"

MySQL 5.1 or 5.5

These days, you can use MySQL 5.5 now. I have 5.5.9 in production right now and it's running great.

I have successfully scaled MySQL 5.5 with simple circular replication amonst 3 DB Servers. Here is the layout of each DB server and what's running

MySQL 5.5.9
2TB RAID10 SAS
192GB RAM
One monolithic 162GB InnoDB Buffer Pool
Dual HexaCore (that's right, 12 CPUs)

/etc/my.cnf has the following

[mysqld]
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=65536
innodb_buffer_pool_instances=1
innodb_bufer_pool_size=162G

Each MySQL Server contains

942 client (tenant) databases
935GB total data
95% of the buffer pool is full on all three servers

After 1 year, it still runs like a dream because of using MySQL 5.5 and getting InnoDB to engage all 12 CPUs.

So, I am quite happy with its performance thus far and so it the client.

Best Answer

Related Solutions

Windows – How to find what process is holding a file open in Windows

MySQL 5.1 or 5.5

Related Topic