I think it is very important that DBAs (especially new ones) do sufficient planning, physical design, risk analysis and backup/recovery planning before installing MySQL. MySQL is an easy database to install and configure on a wide range of platforms.
However if sufficient planning is not performed up front, a DBA is going to have problems as their databases grows, issues come up and more users start accessing the system. A top 10 list of things to do before installing a MySQL database include:
- Scalability: Understand the purpose and potential growth of your database.
- Physical Design: Determine the appropriate physical layout of your database. The defaults are not always the best long term layout.
- Storage Engines: Select the appropriate storage engines and how they impact performance and your backup/recovery strategies.
- Backup and Recovery: Layout your backup and recovery strategy. Pick the appropriate backup/recovery tools for your environment. Then test your backup and recovery strategies sufficiently. Test recovery as well as backups. Understand your recovery times.
- Database Environment: Understand the number of databases that will be created and the maintenance ramifications of the database layout.
- Disaster Recovery: What are the business costs and ramifications of this environment (database server, host server, site, network, ...) being down? Perform appropriate risk analysis.
- Security: Understand how security is being managed and maintained for your users and your databases. Make sure you secure your userids and passwords.
- SQL_MODE and Isolation Level: Set the appropiate SQL_MODE and Isolation Level and make sure you understand the ramifications of the these selections. If running an OLTP environment I highly recommend setting the SQL_MODE to a minimum of traditional.
- Storage: Understand your physical storage layout. Make sure you know the ramifications, limitations, scalability and performance of using separate disks, storage arrays, etc.
- Best Practices: Define a set of best practices that determine your physical layouts, naming conventions, management tools, infrastructure management and document everything.
A few areas that did not make my top ten but I would give honorable mention.
- Make sure and secure the hardware platforms, networks and operating systems where your databases will be running. Along with that take out operating system features you should not be using on a database server. This includes features such as Telnet, web services, print services, etc. These unnecessary OS features have security issues to watch out for and add additional weight (impact performance) to the operating system.
- Read the README files and research the release you are moving to.
- Determine what software you are going to use to manage your MySQL database environment. If you have multiple MySQL databases you should consider the MySQL Network Monitoring and Advisory Service Advisors.
- Administration Advisor
- Schema Advisor
- Performance Advisor
- Security Advisor
- Memory Usage Advisor
- Replication Advisor
- Custom Advisor
The five P's apply to database configurations. Proper Planning Prevents Pitiful Performance. I had a higher pain threshold when I was younger versus today. I have found that proper planning can help you avoid a lot of pain, anguish and late evenings as a DBA.
4 comments:
Dude, do yourself a good and stay with Oracle, which is a REAL database.
Looks like you really like toying, wich is fine. Cross the line and put MySQL in an enterprise environment and that's where trouble starts.
Too funny. Thanks for reading the post.
On a serious note, most shops I go into are running software from multiple database vendors. I rarely go into a shop that is running just Oracle solutions.
Back in the old days it was Sun, HP, Pyramid, Sequent. When Linux came out people laughed at it. It's now a very strong platform for running Oracle. Oracle releases now come out in Linux before any other release. Who would have thought that?
As a DBA you are aware of all the ways companies are looking at reducing costs. I go into companies that are using JBoss on smaller projects where in the past they were using WebSphere, WebLogic, etc. There are now places where MySQL can save a company tons of money. MySQL needs to be used where it makes sense. My point is MySQL and Oracle can exist in harmony where they each make sense.
Also if you look at the success of MySQL, it has forced Microsoft, Oracle and DB2 to come out with their own open source solution or their own lightweight free version. A lot of people are playing with Oracle Express. The success of MySQL has forced the major database vendors to look at this side.
Anyway, thanks for reading the blog. I have lived the religion of Oracle for the last 18 years. I just spent the previous two months working 18 hour days focused on the Oracle BPEL Process Manager, Oracle Business Rules and the Oracle Application server. These are great products, not now every new project needs that level of enterprise solution.
Hello friend,
That was quite ponderable aricle for me,i think Mysql is fine to go in any case (except some special requirements). If companies Like Google and Yahoo is totaly relying upon Mysql for Daa storage then i think it is realy awsome,
Thanks once again for your article,
Mysql Examples
http://mysqlexamples.blogspot.com
Ahsan,
Thanks for reading the article. I do want to make sure you understand that MySQL is not as robust as Oracle nor does it have all the functionality of an Oracle database.
However on projects where MySQL does make sense it can be a strong solution with an incredibly low relative cost.
George T.
Post a Comment