- Know where the bottleneck is on your database server.
- Examine server bottlenecks as well as bottlenecks inside the database application. If the OS is slow then the DB will be slow as well. Examples of typical server bottlenecks are CPU, disk I/O, RAM and network I/O.
- On Unix/Linux use top, iostat, vmstat and netstat to show you such metrics. In our case, Hyperic HQ is also capable of retrieving these stats and we used this to help graphically trend them over time.
- Tune Your Queries
- Run "explain" on your slow queries.
- Make sure to use indexes where applicable and avoid table scans if you can help it.
- Avoid creating tmp tables where you can in queries. If you cannot avoid them due to complex queries, make sure to structure your queries to minimize tmp table size as much as possible.
- Watch out when using views. MySQL will create a temp table from the SQL statement and then apply the statement's where clause to that temp table.
- For more information check out MySQL documentation on speeding queries
- Use of Indexes
- Make sure you use your clustered index / primary key wisely. This is probably have the biggest performance impact on the table. Try to make sure the leading edge of the index has high cardinality, and if possible try to make sure it is ascending (e.g. timestamps or sequence ids)
- For a table which is used for many writes, do not create too many indexes, due to the overhead of CRUD operations
- For a static table, make sure it has plenty of index access points and use covering indexes where applicable.
- Know the MySQL server options and know how to monitor them to make sure MySQL vitals are in check.
- Pay attention to innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, tmp_table_size, max_heap_table_size, max_tmp_tables, and innodb_flush_method
- For slow query management, make sure you monitor the tmp table status from "show global status" Created_tmp_files, Created_tmp_tables and especially Created_tmp_disk_tables
MySQL has established itself as the most popular database in the world, powering more web applications than any other database. Since many companies have standardized on MySQL and gained much technical expertise in running it, it makes sense that web shops demand their monitoring and management tool vendor also run on the standard, MySQL. We have built Hyperic HQ to manage web applications, the same ones which developers and operations staff are building and running on MySQL. However, until recently, MySQL was not a supported database backend for HQ. Early this year – we delivered a highly scalable solution backed by MySQL. Through diligent efforts, we were able to tune MySQL to far exceed our already high scalability requirements. In fact, we found that we were able to scale MySQL to peak at an impressive 2.3 million metric insertions per minute into HQ’s datastore without stressing the hardware. This is the reason that legions of MySQL fans have pushed application vendors, like Hyperic, to support MySQL. When properly engineered, MySQL is clearly an industrial strength, high-performance database. To achieve these results, we had to employ some good basic DBA performance guidelines and MySQL knowledge. Most people know that performance tuning is full of tradeoffs. For instance, adding an index to a table will typically slow down insert/update/delete operations. However, if the table is not updated often, it does not matter as reads will be faster. These tradeoffs are considerations for the specific needs of the application. Tuning MySQL for Hyperic HQ was no different. The key areas that we identified are:
- Posted by: Scott Feldstein
- Posted on: May 15 2008 14:45 EDT
- A Year Later by Charles Lee on May 15 2008 14:58 EDT
- MySQL was already 'fast' by Matt Shaw on May 16 2008 07:37 EDT
- Performance tuning and scaling are two different things by Nati Shalom on May 16 2008 17:50 EDT
- Clustered indexes by Morten Olsen on May 19 2008 05:46 EDT
A year late, but better late than never: http://www.theserverside.com/news/thread.tss?thread_id=44918 At least the results exceeded our expectations!
Now if they could only learn to do the right thing with nested transactions and data validation!
Hmmm, since we do validate our data and mysql doesn't support nested transactions I do not know how to reply to this. :@(
I would categories this article as Performance tuning excersize for a certain scenario (monitoring in this case) using MySQL. It has very little to do with scalability so i think that the title is misleading. To clarify my point i would like to quote Steve Haines on the difference between Performance and Scalability:
"The terms “performance” and “scalability” are commonly used interchangeably, but the two are distinct: performance measures the speed with which a single request can be executed, while scalability measures the ability of a request to maintain its performance under increasing load. For example, the performance of a request may be reported as generating a valid response within three seconds, but the scalability of the request measures the request’s ability to maintain that three-second response time as the user load increases."I recently published a post discussing two options for Scaling out MySQL: 1. Using database clustering 2. Putting In-Memory-Data-Grid infront of MySQL I believe that reading through this content will highlight the type of things that i expect will be covered when were talking on scalability. The most interesting of them are the tradeoffs between performance, latency, reliability,scalability and complexity and how we balance between those requirements in a generic way.
The definition I have seen used most for Scalability in any type of environment is "allowing for future growth" In terms of HQ that simply means our users may grow well past there needs at the moment and we'll be able to handle that using MySQL as the DB backend. thanks for the post!
Could you elaborate what you mean about using clustered indexes wisely? The thing about using timestamps or sequence ids seems contrary to some performance advices, as you will end up inserting new records in the same pages creating a hotspot. Other advices therefore suggest you ensure distribution of consecutive inserts, when you have a lot of inserts. I'm not familiar with the inner workings of MySQL/InnodDb or your scenario, so I'd appreciate if you could explain why this approach is good in your scenario.
Interesting point. The unique thing about MySQL InnoDB v other DBs (e.g. Oracle or ASE) is that when you define a primary key (or don't define it and allow innodb to make one up itself) it will always cluster that index (you don't have a choice). So while this is the case it is better to use a key that is ascending rather than inserting in the middle which causes page splits. Your point about the hotspots I have only really seen while working with ASE and that is due to their page level locking. A way to alleviate that is to start partitioning the table to avoid such hot spots or use row level locking (which could cause a lot of fragmentation) read: http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html