Mysql – Any open source software for measuring & monitoring MySQL performance

Here are my recommendations (your millage may vary)

• Use hardware RAID. This goes contrary to my recommendations to use software RAID in other posts, however this is a specific situation where you want the hardware RAID card. Specifically you want the battery backed NVRAM on the RAID card to reduce the time to takes the fsync the log file to disk.
• Use ONLY RAID 1 or RAID 10 volumes. The cost of RAID 5 or 6 writes is too high to tolerate in a mixed read/write workload.
• Use separate LUNs for the data, log and tmp volumes. These should all be separate from the OS and swap volumes.
• Use InnoDB.
• Use innodb_file_per_table
• Use a 64-bit OS
• Set your InnoDB buffer pool to ~ 80% of your available RAM
• Set your log files to 1/4 of your buffer pool size, you between 2 to 4 log files. Larger log files mean slower shutdown and recovery times, but allow you to restore large database dumps faster.
• log_slow_queries, log-queries-not-using-indexes, set-variable = long_query_time=1, investigate every query in that log, refactor your schema to avoid table scans and tmp tables whenever possible.

First, you need to know which DBMS features you want and how well the systems do this. For example:

• MySQL is fast for certain types of applications so you can service a high transaction volume on modest hardware

• SQL Server comes with a good set of reporting tools, so you may not need to purchase third party tooling for this. However, it only runs on Windows.

• Oracle has a JVM built into the server (do you really want to pay per-CPU Oracle licensing to run a java app?), has good support for large databases through good table partitioning, bitmap indexes and a variety of features that facilitate data warehouse applications.

• Various database systems may or may not support the XA protocol for distributed transactions

• Postgres has a spatial index and support for extensions and stored procedures in a variety of languages.

• Teradata has a shared-nothing architecture with no central bottlenecks so it can scale out to an arbitrarily large data set.

• Various SQL dialects supported by different system have greater or lesser feature sets or particular strengths.

Once you know which of the various proprietary or open source DBMS platforms can support your application and how well they do it you can decide which you want to use.

All of the open-source DBMS platforms have credible support offerings available either through the vendor or third parties. Needless to say these support offerings are commercial so they are not free. If you really do not need vendor support you could view an open-source system as free, but this is going to be an unusual situation. There is one corner case of particular interest, which is discussed below.

Open-source systems also de-couple support from the vendor - credible third-party support offerings are available for most if not all major open-source DBMS products.

Various DBMS platforms allow extensions to be developed - in fact, this technology was first pioneered on Postgres by Stonebreaker et. al. and was the main driver for the development of that system. Different platforms have greater or lesser support for this -

• Oracle and SQL Server have some limited support.

• Postgres has extensive support for extension right throughout the system.

• Informix Online havs support for extensions known as 'blades' derived from Illustra (which was itself an early commercialised version of Postgres).

• MySQL has a plug-in architecture that supports third-party storage engines.

If you have this particular requirement you may find that the open-source systems offer more flexibility. For example, there are several data warehousing products based on modified versions of Postgres.

Thus, open-source vs. proprietary is not a choice between free (as in beer) and paid for, rather is a matter of features, cost, confidence in the support options and control.