win vs. nix performance

[mufka]

I'm running vanilla installs of Mysql on both Fedora 9 and Windows XP. (The Fedora is running in Vmware on the XP host) I notice significant performance differences in importing data into MySql. I'm using phpMyAdmin.

When I import tables into the Fedora install it takes 18 seconds. When I attempt to import the same script into XP, it times out at 5 minutes.

Is there something that I can look for to see why this is the case?

 

[Kozusnik]

I'm not familiar with Windows, but there should be MySQL logs somewhere.

As far as backup/restores, I've found that using mysqldump seems to work better and faster than phpMyAdmin. Plus, you don't have to worry about the php.ini settings timing you out. I would suggest mysqlhotcopy, but I don't think it works on Windows and you need to shutdown the MySQL server to import.

EXPORT
mysqldump --add-drop-table --extended-insert --quick --host=localhost(or IP) --user=username --password=password database_name > ./some_file_name.sql

IMPORT
mysql -uusername -ppassword database_name < ./some_file_name.sql

Mark

 

[ingresman]

how does everything else perform under vmware ?
Have you tried to it on a real XP box ?
What kind of disc technolgy fo you have on both boxes ?

 

[Kozusnik]

ingresman,
He said that Fedora is running as a VM on the XP host. So it sounds to me like he's only using one box.

Mark

 

[DonQuichote]

Search the manual for the "slow query log" and enable it to see which queries are problematic. Although I also find that MySQL on Linux is generally faster, the differences are not that extreme. In your case, it may just be a missing index on one installation that causes the problem.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[JJSRich]

I'm not sure what your end goal is, if you are benchmarking for deployment, doing development work, or just tinkering, but I thought I'd weigh in on a few other issues.

Your main areas of impact is the fact that you are not running your *nix server on bare metal. I won't say anything about Fedora 9 as a server, as in your instance, it is actually the working server. In order to do a proper comparison with only one box, you would also need to install XP into a VMWARE instance and benchmark the virtualised Fedora against virtualised XP.

Having installed MySQL on our Debian servers here (gotta love netinstall) and onto XP laptops for disconnected access, I can say that it took me a lot longer to get MySQL working on Windows than on *nix. Lack of capitalisation in table names on XP forced me to re-name all my tables, and re-write all views and stored procedures to recognise the changes.

Also for importing data, it is better IMO to use either the GUI Query Browser>new script tab>load script or on the command line "mysql -u root -p < import.sql". Works on XP and *nix. CLI is fastest in my experience, but you can't edit the import file as easily as with the GUI browser in XP. I'll leave the use of awk, sed, cat, grep, vi, emacs, etc to the other console warriors out there.

 

[mufka]

I am using one box. Windows XP with VMware running Fedora.
I have MySQL running well on the Fedora under VM. My goal is to get it running well under just XP so that I don't have to have the VM running all the time.

I've enabled the slow query log and it is showing nothing.

I am using phpMyAdmin for the imports. My import script is dropping and recreating all of the tables in the database so it shouldn't be anything with indexes.

I even copied the my.ini from XP to the my.cnf of the Fedora and the Fedora still works just fine.

 

[DonQuichote]

I can say that it took me a lot longer to get MySQL working on Windows than on *nix. Lack of capitalisation in table names on XP forced me to re-name all my tables, and re-write all views and stored procedures to recognise the changes.

So true. I still don't grasp why this is the default for Windows. And it is so easily solved:
1. Install MySQL. Do not create any databases yet. The system databases will be created, which is just fine.
2. add the following line to my.cnf or my.ini:

lower_case_table_names=2

3. restart the database server

That's it. Now, MySQL behaves case-insensitively and will not molest the existing case either, which is always wrong on any system. The MySQL help file literally says "Should be set to 2 if you are using a case insensitive file system". Beats me why 1 is still the default for Windows.


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[guelphdad]

Beats me why 1 is still the default for Windows.

BECAUSE windows knows what's best for you.

 

[JJSRich]

I must admit, with what I know about VM tech, the FC9 install should be slower. The failure to import hints at a damaged install on the XP box.

Follow DonQuichote's advice when reinstalling MySQL on the XP box, and make sure to optimise for your own system. It would not surprise me in the slightest if your XP my.ini was worlds apart from your FC9 my.cnf.

 

[ingresman]

The reason what table names vary in case is driven by how the operating system support the physical files that support the tables (!)
Windows is not case sensitive so FILEA and FILEa are the same Unix has a bit more picky and file FILEA is different from FILEa, great fun trying to debug scripts on unix.
MYSQL can fix this so don't be dising 'da windows !
Seriously though if you want to have portability as a goal stick to single consistent case for table names.
The manual says

To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

 

[mufka]

As I mentioned before, I copied the my.ini from XP to the my.cnf of FC9 (changing paths, etc.) and it worked fine.

One thing that I am thinking is the versions. phpMyAdmin is 3.1.1 on both. MySQL is 5.0.45 on FC9 and 5.1.30 on XP. I wanted to try to downgrade the XP to 5.0.45 but can't find the source.

One other thing is that on XP, the MySQL client version is 5.0.51a and I can't figure out how to update it to match the server version.

 

[mufka]

The following change to the my.ini ended with the import on XP finishing in 16 seconds rather than timing out at 5 minutes. This is actually better than the 22 seconds on FC9.

innodb_flush_log_at_trx_commit = 0

Is this a safe thing to change? This is just a dev server and will never be public or production.