MySQL vs MSSQL

[geelwortel]

Hi Everyone

I’m new to DBA so I want to ask a couple of questions about MySQL vs MSSQL.

1. In MSSQL you have something called checkpoints. Checkpoints is used by the server as a maintanance tool to apply data and tidy up the database. Do MySQL have an equivelant and can you switch this off?
2. MySQL V4 vs V5. Wich of these servers are the fastest and most reliable.
3. MySQL V4/5 vs MSSQL 2000/2005. Which of these server will be the best to run a local server with ± 100 user with ± 7 db and a total of 10 - 15 Gigs of data(Cost should not influence the answer).
4. Skip Locking. I see this in all the my.cnf files. What does it do?
5. ODBC Drivers. On version 3.51.12 of the MySQL ODBC Drivers connecting to a V5 server the wait time for a connection is ± 10 - 20 seconds. On V3 it’s immediate. Why would this happen and what can we do to fix this?

Thank you for your help and this cool site.
Andrew

[Peter]

1. In MySQL Innodb storage engine also has checkpoints, which are fuzzy checkpoints however and done automatically so you normally do not need to care about them.

2. Both 4.1 and 5.0 are marked as Stable by MySQL. In My oppinion 4.1 is still a bit more stable at this point but typically both would work for you. Depending on your workload 4.0, 4.1 or 5.0 will be fastest.

3. Question is too vague. If you mean “fastest” by best you would need to run benchmarks. A lot depends on application here. Of course I’m biased towards MySQL )

4. skip-locking applies to MyISAM tables and should be used if you run several MySQL servers on same data files. You rarely need to change it.

5. This should not happen. It might be some bug with software or configuration.

[geelwortel]

Thanx Peter

1. We want to run V5 with MyISAM tables will this checkpoints still apply. Can you elaborate a bit about checkpoints, I don’t fully understand the concept?
   2 and 3) Our Server have 42 databases. Of these 42 only 7 are used extencively, avg. of 350 - 400 queries per second and 1 - 3 MB of data per second is transferred from this server(depends on the day of the week(Thursdays and Fridays are wage days and it can go up to 500 - 700 qps and 4 - 7 mbps)). The server is a 3.4 Ghz dual core with 4 GB of RAM. We now need something that is FASTER but will still give us the stability of V3. The main application that will run on this system wil be an in-house wage application developed in C/C++. This application captures and calculates the times and creates a payroll that is sent out to the branches to pay the workers. Other online applications also access these databases and the other ones on the server. These applications where developed in .net and Java. they provide for ± 15% of the server load per week. What would you suggest we move to V4.1/V5/MSSQL
   4)What does skip locking do? How do you stop queries from locking a database/table?
   5)What can I do to stop this from happening or where can I read about this?

[Peter]

1. No MyISAM doesn not have checkpoints. Checkpoints are simply form of flushing some of the dirty buffers and logs and appropriate mark so database servers know how to recover in case of crash. Again it does not apply to MyISAM.

2. This is still to broad details. It is important up to query level. Certain queries are faster on 4.1 certain on 5.0 depending on which optimizations apply etc.

If you’ve been running with MySQL I would suggest you to have a closer look at what are your problems and how they can be fixed with MySQL. Moving to other database is serious step.

4. skip-locking simply means MySQL does not use OS file locking functions while locking the tables. MyISAM Still has table clocks and you can’t do much with it. If you want to run long running queries and do updates at the same time you may found Innodb working better for you.

5. I can’t tell from glance view. I would check if it is only ODBC issue or if it happens during standard connection as well and when look at process list during this stalling connection. Good chances are you need to enable --skip-name-resove option.

[geelwortel]

Thanx Peter

Everything you’re saying helps and makes alot of sence. I can see this is not your first time you’re working with MySQL .

After what you said in 3 about Innodb I went to go and read about the difference between Innodb and MyISAM. It looks like Innodb works better, gives more performance and also is safer to use with the rollback and crash recovery options “build” into it. Do you think this will give us a big performance improvement and stability/safety net to use this? Will this be a big job to move over to MySQL using Innodb?

[Peter]

I specialize in MySQL Consulting, especially in Performance consulting, so no it is not my first time )

Innodb may boost performance quite a lot, but in needs to be tuned accurately, for example if you move from MyISAM you might want to set innodb_flush_logs_at_trx_commit=2

Conversion from MyISAM to Innodb is often as easy as altering the tables, in some cases however you also need to add deadlock handling to your code etc.

[geelwortel]

If I understand you correctly you are saying that you don’t even have to reinstall your server, you just change a couple of “settings” and your tables if you need to and then you can have your mysql running on Innodb? Do you think it will be a better option to change this engine now(on V3) or with the upgrade to 4.1/5(after we fix ALL our problems)? Is the administration alot different on Innodb?

What does this mean:

1. innodb_flush_logs_at_trx_commit=2
2. deadlock handling

[Peter]

In MySQL 3.23 you need to use MySQL-Max to get Innodb.

You also need to adjust configuration settings.

innodb_flush_logs_at_trx_commit is one of such settings.

Deadlock handling means - with any transaction engine there is chance of deadlocks which do not happen with MyISAM so you might need to handle these in your application.