Baron Schwartz, Chief Performance Architect at Percona, gives a quick analysis of the causes of downtime in MySQL, breaking the causes down into four main categories: operating environments, performance, replication, and data loss and corruption. The operating environment, the primary cause of downtime, is independent of MySQL; this category primarily covers problems of storage, init scripts, and network and power failures.
Performance problems, on the other hand, is an issue of MySQL itself, with bad SQL and indexing, as well as idle transactions, being the main suspects for MySQL downtime. Regardless of the reasons for downtime being external or internal to MySQL, there are several solutions that can reduce or even eliminate the problem.
Prevention of downtime, according to Baron Schwartz, can be had be using bad upgrades that slow down systems without any real benefit. Ensuring a stable environment is also key: should storage systems, DNS, Init scripts, or anything that a component of MySQL relies on fail, then MySQL will correspondingly slow or fail completely. The most basic of the solutions Schwartz presents is to reboot servers every so often, which refreshes the system and allows for recovery opportunities.
Baron Schwartz is the Chief Performance Architect at Percona. He is the lead author of High Performance MySQL, and creator of Maatkit.
This free podcast is from our MySQL Conference series.