MySql from Linux 64bit to Windows 32Bit - Slow queries?

Question

We have a database that was backed up from a Linux 64 bit version of MySql that we have restored onto a Windows 32bit version of MySql.

We have a table with about 4.5 gig of data - the main space being consumed by a BLOB field containing file data. The table itself only has about 6400 records in it.

The following query executes on the Linux box in no time at all; but on the windows box, it takes about 5 minutes to run and in the process, the server is unresponsive to anything else:

select id from fileTable where cid=1234

Is there some sort of optimization we need to do? Is there some sort of special considerations that need to be met when going from Linux to Windows or from 64 bit to 32 bit?

Answer 1

If you copied over the binary database files *.frm *.MYD *.MYI

Then you should check this out: http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

If you exported the DBs content then:

Check the version numbers and changelogs.

Also consider if there are performance differences between the two systems! (maybe this is normal)

Answer 2

I'd say the 4.5 Gig of data will be easier accessible via a 64bit number space (which has a upper limit of 17.2 gigabyte in directly addressable memory). 32 bit addressable memory is 4Gb, so if your database is bigger than this then possibly MySQL cannot handle the indexing as quickly.

Is there a way of re-indexing or optimising your database so that its paged in a more 32bit friendly way? Altering the largest table to a different storage engine may do this (use ALTER or phpMyAdmin).

You may also have lost some indexing on the ID field after the move. try running your select again, with EXPLAIN at the front;

EXPLAIN select id from filetable where cid=1234

if the table produced after the query contains mosty NULLs then you need to re-configure the keys in your table.

Alternatively, check how much RAM is on your 32bit box, is it comparable to the 64 bit box?

Obviously, back it all up and do tests on a non-live server first ;)

I hope this helps you get on the right track.

Answer 3

Just do OPTIMIZE on all your tables and check if you (still) have an index on cid.