mysql 5.6 Linux vs windows performance

Question

The below command takes 2-3 seconds in a Linux MySQL 5.6 server running Php 5.4

exec("mysql --host=$db_host --user=$db_user --password=$db_password $db_name < $sql_file"); 

On windows with similar configuration it takes 10-15 seconds. The windows machine has a lot more ram (16gb) and similar hard drive. I installed MySQL 5.6 and made no configuration changes. This is on windows server 2012.

What are configurations I can change to fix this?

The database file creates about 40 innodb tables with very minimal inserts.

EDIT: Here is the file I am running:

https://www.dropbox.com/s/uguzgbbnyghok0o/database_14.4.sql?dl=0

UPDATE: On windows 8 and 7 it was 3 seconds. But on windows server 2012 it is 15+ seconds. I disabled System center 2012 and that made no difference.

UPDATE 2:

I also tried killing almost every service except for mysql and IIS and it still performed slowly. Is there something in windows server 2012 that causes this to be slow?

Update 3 I tried disable write cache buffer flush and performance is now great.

I didn't have to do this on other machines I tested with. Does this indicate a bottleneck With how disk is setup?

https://social.technet.microsoft.com/Forums/windows/en-US/282ea0fc-fba7-4474-83d5-f9bbce0e52ea/major-disk-speed-improvement-disable-write-cache-buffer-flushing?forum=w7itproperf

Answer 1

That is why we call it LAMP stack and no doubt why it is so popular mysql on windows vs Linux. But that has more to do more with stability and safety. Performance wise the difference should be minimal. While a Microsoft Professional can best tune the Windows Server explicitly for MySQL by enabling and disabling the services, but we would rather be interested to see the configuration of your my.ini. So what could be the contributing factors w.r.t Windows on MySQL that we should consider

1. The services and policies in Windows is sometimes a big impediment to performance because of all sorts of restrictions and protections.

2. We should also take into account the Apache(httpd.conf) and PHP(php.ini) configuration as MySQL is so tightly coupled with them.

3. Antivirus : Better disable this when benchmarking about performance

4. Must consider these parameters in my.ini as here you have 40 Innodb tables

   innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, query_cache_size, innodb_flush_method, innodb_log_file_size, innodb_file_per_table

For example: If file size of ib_logfile0 = 524288000, Then 524288000/1048576 = 500, Hence innodb_log_file_size should be 500M

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT 

https://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html

5. When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert SET autocommit=0;

6. Most importantly innodb_flush_log_at_trx_commit as in this case it is about importing database. Setting this to '2' form '1' (default)hm can be a big performance booster specially during data import as log buffer will be flushed to OS file cache on every transaction commit

For reference :

https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html

https://dba.stackexchange.com/a/72766/60318

http://kvz.io/blog/2009/03/31/improve-mysql-insert-performance/

---

Lastly, based on this

mysql --host=$db_host --user=$db_user --password=$db_password $db_name < $sql_file

If the mysqldump (.sql) file is not residing in the same host where you are importing, performance will be slow. Consider to copy the (.sql) file exactly in the server where you need to import the database, then try importing without --host option.

Answer 2

Windows is slower at creating files, period. 40 InnoDB tables involves 40 or 80 file creations. Since they are small InnoDB tables, you may as well set innodb_file_per_table=OFF before doing the CREATEs, thereby needing only 40 file creations.

Good practice in MySQL is to create tables once, and not be creating/dropping tables frequently. If your application is designed to do lots of CREATEs, we should focus on that. (Note that, even on Linux, table create time is non-trivial.)

If these are temporary tables... 5.7 will have significant changes that will improve the performance (on either OS) in this area. 5.7 is on the cusp of being GA.

(RAM size is irrelevant in this situation.)