I’m new to DBA so I want to ask a couple of questions about MySQL vs MSSQL.
- 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?
- MySQL V4 vs V5. Wich of these servers are the fastest and most reliable.
- 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).
- Skip Locking. I see this in all the my.cnf files. What does it do?
- 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.
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.
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.
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.
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.
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?
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.
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:
- deadlock handling
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.