Finding your MySQL High-Availability solution
2011/05/17 17:05 | by ipaddr ]
How to determine which MySQL High-Availability solution is best?
What is really tricky with MySQL is the number of possible HA solutions. From the simplest the most complex let’s list the most common ones:
- MySQL replication with manual failover
- Master-Master with MMM manager
- Heartbeat/SAN
- Heartbeat/DRBD
- NDB Cluster
These technologies are by far, not a one size fits all and many deployments use combination of solutions. I will not cover ScaleDB and Continuent because I know almost nothing of these solutions. There are many more questions you need to ask yourself before being able to pick the right one. Below, I listed the most common questions, I might have missed some.
1. What level of HA do you need?
Since all the technologies do not offer the same level of availability, this is a first important sorting factor. Here are estimates of the level of availability offered by the various solutions.
| Level of availability | |
| Simple replication | 98 to 99.9+% |
| Master-Master with MMM manager | 99% |
| Heartbeat/SAN (depends on SAN) | 99.5% to 99.9% |
| Heartbeat/DRBD | 99.9% |
| NDB Cluster | 99.999% |
From the table, if your requirements are for 99.99%, you are restricted to NDB Cluster while if it is only 99% you have more options. I recall that the level of availability is hard to estimate and subject to debate. These are the usually accepted level of availability for these technologies.
2. Can you afford to lose data?
Obviously, if you are concerned about loss of data, you are most likely using the InnoDB storage engine, since MyISAM is not transactional and do not sync data to disk. Similarly, MySQL replication is an asynchronous process and although it is fairly fast at transferring data between the master and the slaves, there is a window of time where data loss is possible.
If you can afford to lose some data, you can consider “MySQL replication” and “Master-Master with MMM manager” otherwise, you can only consider the other three solutions.
| Data 100% safe | |
| MySQL replication | no |
| Master-Master with MMM manager | no |
| Heartbeat/SAN (depends on SAN) | yes |
| Heartbeat/DRBD | yes |
| NDB Cluster | yes |
3. Does your application use MyISAM only features?
There are some features like Full text indexes and GIS indexes that are supported only by MyISAM. The HA solutions that work well with MyISAM are “MySQL replication” and “Master-Master with MMM manager”. Depending on the application, the MyISAM Full text indexes might be replaced by another search engine like Sphinx in order to remove the restriction. There is no HA solution other than the ones based on replication that handles GIS indexes.
| HA solutions | |
| Need MyISAM Full text or GIS indexes | “MySQL replication” and “Master-Master with MMM manager” |
| Don’t use any special MyISAM feature | All |
| Can change MyISAM Full text to Sphinx | All |
4. What is the write load?
The HA solutions we present are not equal in term of their write capacity. Due to the way replication is implemented, only one thread on the slave can handle the write operations. If the replication master is multi-cores servers and is heavily writing using multiple threads, the slaves will likely not be able to keep up. Replication is not the only technology that put a strain on the write capacity, DRBD, a shared storage emulator for Linux, also reduce by about 30% (very dependent on hardware) the write capacity of a database server. In term of write capacity here are you choices.
| Write capacity | |
| MySQL replication | Fair |
| Master-Master with MMM manager | Fair |
| Heartbeat/SAN (depends on SAN) | Excellent |
| Heartbeat/DRBD | Good |
| NDB Cluster | Excellent |
5. For what level of growth are you planning?
Since NDB Cluster is an integrated sharding environment, if you are planning for a growth that will need sharding (splitting the database over multiple servers), then you might need to take a serious at that solution. If not, then, apart from the write capacity, all the solutions are approximately equal.
6. How qualified is your staff or support company?
There is a quite direct relationship between the level of availability and the complexity of the solution. In order to reach the promised level of availability, the staff maintaining the HA setup, either internal or external, must have the required level of expertise. The required expertise level is summarized in the table below.
| Expertise level | |
| MySQL replication | Typical, average MySQL DBA + some Sysadmin skills |
| Master-Master with MMM manager | Good, average MySQL DBA + good Sysadmin skills |
| Heartbeat/SAN (depends on SAN) | High, Good MySQL DBA + strong Sysadmin skills |
| Heartbeat/DRBD | High, Good MySQL DBA + strong Sysadmin skills |
| NDB Cluster | Very high, Specific NDB knowledge, strom MySQL skills and strong Sysadmin skills |
7. How deep are your pocket?
The last aspect that needs to be considered is the budget, complexity is expensive. We will consider two types of setup. The first one is a basic proof of concept of the technology with the hardware tested, the data imported and basic testing and documentation. A proof of concept setup is a good way to get used to a technology and experiment with it in a test environment. The other type of setup we will consider is a full production setup that includes extensive testing, fire drills, full documentation, monitoring, alerting, backups, migration to production and post migration monitoring. Of course, it is the safest way to migrate an HA solution to production. All the times here are estimates based on field experience, the values presented here are fairly typical and contains some buffers for unexpected problems. Although an HA solution can be built remotely through a KVM over IP and adequate remote power management, an on site intervention with physical access to the servers is the preferred way, especially for the most complex solutions.
| Proof of concept | Migration to Production | |
| MySQL replication | 4 hours | 12 hours |
| Master-Master with MMM manager | 8 hours | 24 hours |
| Heartbeat/SAN (depends on SAN) | 32 hours | 120 hours |
| Heartbeat/DRBD | 40 hours | 120 hours |
| NDB Cluster | 40 hours | 120 hours+ |
Editor’s Note: We’ve gotten many questions about the time estimates mentioned here. The above estimates shouldn’t be used to compare against any specific situation. Time will vary greatly depending on your project. For example, “setting up replication” can be as simple as CHANGE MASTER TO, and can take as little as a few minutes in some circumstances. Yves’s estimate is for a project to create a replication slave for HA purposes, not for “setting up replication.” There is a big difference between an HA project and a DBA task. – Baron Schwartz
转:推荐Web开发人员应有的7款免费的Android应用
2011/05/09 18:44 | by ipaddr ]
作为一个Web开发人员，有时候也需要关注一下你的网站在移动设备上的表现，或者需要通过移动设备来管理网站。下面为你推荐7款免费的Android应用，有助于你更好的工作。
2.AndFTP
AndFTP是Android手机上的支持FTP,SFTP,SCP,FTPS的客户端软件，可管理多个FTP服务器，自带文件浏览器和FTP文件浏览器，提供下载和上传的功能，可对文件进行重命名、删除、复制以及权限设置、创建目录等操作。
Android CodePad是一个简单的源码浏览器，支持语法高亮显示，支持所有类C、类Bash和类XML的语言的语法高亮，可自动根据文件来选择不同的语法。
该软件为开发者提供了在手机上直接浏览各种开发工具的api手册，包括：php, python, ruby on rails, jquery, css, seo, apache, regular expressions, svn, bash, c++, drupal, gcc, gdb, html5, javascript, microformats, mysql, postgreesql, unix, wordpress.
这是一个用来学习CSS所有的属性、包括语言、示例以及浏览器限制说明，提供CSS1和CSS2的属性列表，同时还包含了可直接浏览效果的例子：border-style, cursor, font-style, font-variant, font-weight, text-decoration and text-transform等属性.
W3C Cheatsheet提供快速访问HTML5的有用信息。
该工具可让你测试JavaScript的执行情况，在编辑框中输入JavaScript代码，然后运行来获取结果。不过这工具我相信用起来非常费劲，在手机上输入JavaScript代码？别逗了！
原文出处：开源中国社区
