Scaling MySQL Beyond LAMP Websites


News: Scaling MySQL Beyond LAMP Websites

  1. Scaling MySQL Beyond LAMP Websites (7 messages)

    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:
    1. 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.
    2. 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
    3. 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.
    4. 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
    Fortunately for us, we were aided in this development by eating our own dog food. Hyperic HQ offers an easy and effective way to facilitate monitoring of MySQL instances. If the status variables grow uncontrollably, make sure to turn on your slow query log and debug the slow queries. There is usually always something that can be re-architected to improve performance. The results speak for themselves - in a performance study published last week, we showed that using reasonably modest hardware, Hyperic HQ using MySQL as a database backend scaled impressively. The actual performance load demonstrated in a test over 5 days with two 2 quad core machines (one for Hyperic HQ, the other for MySQL), the system sustained 220,000 metrics per minute with no degradation. Hyperic HQ currently monitors a large percentage of the web’s top sites, from CNET to hi5 – none of them are currently tracking more than 100,000 metrics per minute. Our results clearly illustrate that the combined Hyperic HQ and MySQL solution more than handles the monitoring needs of some of the biggest websites in the world. For more information on the engineering effort involved in providing MySQL support, check out the MySQL Tuning presentation that was delivered at CommunityOne and MySQL User Conference. For our recommended options, see the Hyperic MySQL Database Preparation and Tuning Guide. We are not claiming that we have a one size fits all solution, but it’s a good place to start in order to tune MySQL server options.

    Threaded Messages (7)

  2. A Year Later[ Go to top ]

    A year late, but better late than never: At least the results exceeded our expectations!
  3. MySQL was already 'fast'[ Go to top ]

    Now if they could only learn to do the right thing with nested transactions and data validation!
  4. Re: MySQL was already 'fast'[ Go to top ]

    Hmmm, since we do validate our data and mysql doesn't support nested transactions I do not know how to reply to this. :@(
  5. 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.
  6. tuning v. scaling[ Go to top ]

    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!
  7. Clustered indexes[ Go to top ]

    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.
  8. Re: Clustered indexes[ Go to top ]

    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: