Scaling MySQL A Case Study of Hyperic HQ A Case Study of Hyperic HQ

1. Scaling MySQLA Case Study of Hyperic HQA Case Study of Hyperic HQ Scott Feldstein, Senior Software Engineer S297257

2. 2 What is Hyperic HQ? Hyperic HQ provides a single remote console that allows operation teams to track performance/event data, create complex alerts and escalations, run diagnostics, and issue control actions. Translation: HQ collects and transactionally read and write a lot of metric dataTranslation: HQ collects and transactionally read and write a lot of metric data

3. 3 HQ Database Support MySQL Oracle PostgreSQL (current embedded database solution)? Best performance on MySQLBest performance on MySQL

4. 4 Performance Bottleneck Dependent on hardware performance (CPU, memory, etc)? I/O Disk latency Network latency (remote database)? Slow queries

5. 5 How Much Data 300 Platforms (300 remote agents collecting data)? 2,100 Servers 21,000 Services 468,000 metrics enabled (20 metrics per resource)? 20,000 metric data points per minute (average)? 28,800,000 metric data rows per day platforms == H/W + OS peak metric data points/min >> 20K large deployment up to 50K/minplatforms == H/W + OS peak metric data points/min >> 20K large deployment up to 50K/min

6. 6 Metric Data Flow Agent collects data and sends reports to server with multiple data points Server batch inserts metric data points If network connection fails, agent continues to collect, but server �backfills� unavailable When agent reconnects, spooled data overwrite backfilled data points

7. 7 MySQL Batch Insert Statement Syntax INSERT INTO TABLE (a,b,c) values (0, 0, 0), (1,1,1),(2,2,2),(3,3,3),...,... Extremely fast since there is only one round trip to the database for a batch of inserts Only limitation on statement size is determined by server configuration variable "max_allowed_packet" Other options for increasing insert speed Set unique_checks=0, insert, set unique_checks=1 Set foreign_key_checks=0, insert, set foreign_key_checks=1 PreparedStatement on other databases and batch inserts on other DBs On MySQL, batch insert improves upon PreparedStatement by 30% max_allowed_packet doesn�t max out, Oracle maxes at 1000 (problem for us)? Can use add�l options if data integrity guaranteedPreparedStatement on other databases and batch inserts on other DBs On MySQL, batch insert improves upon PreparedStatement by 30% max_allowed_packet doesn�t max out, Oracle maxes at 1000 (problem for us)? Can use add�l options if data integrity guaranteed

8. 8 INSERT ... ON DUPLICATE KEY UPDATE Application sensitive to time. In some circumstances, this will result in duplicate data rows (by primary key), and row values have to be updated. When batch insert fails, retry batch with INSERT ON DUPLICATE KEY syntax Compared to other databases, HQ iteratively updates failed rows and attempts batch insert on rest. Retry process until batch has completed. Equivalent to Oracle merge statement (>= 9i)? Much faster in MySQL than other DBsEquivalent to Oracle merge statement (>= 9i)? Much faster in MySQL than other DBs

9. 9 Batch Aggregate Inserter Queue metric data from separate agent reports Minimize number of insert statements, connections, and CPU load Maximize workload efficiency Optimal configuration for 700 agents Workers: 3 BatchSize: 2000 QueueSize: 4000000 Peak at 2.2 million metric data inserts per minute 8 way / 16 GB HQ Server Box and an 8 way / 8 GB MySQL Box on CentOS 5.Fewer workers, but large queue size Cruise control, load not too high, so MySQL may peak higher with even more workers. Other concerns.8 way / 16 GB HQ Server Box and an 8 way / 8 GB MySQL Box on CentOS 5.Fewer workers, but large queue size Cruise control, load not too high, so MySQL may peak higher with even more workers. Other concerns.

10. 10 Data Consolidation Lower resolution tables track min, avg, and max Table storing all collected data points (most activity) capped at 2 days worth Data compression runs hourly Originally storing all metric data in one big table User configurable data storage windowOriginally storing all metric data in one big table User configurable data storage window

11. 11 Limit Table Growth Roll up and reduce size of raw measurement data table Contention still on MEASUREMENT_DATA table to insert, delete, and update. Still problems with table fragmentation, CPU and I/O loadRoll up and reduce size of raw measurement data table Contention still on MEASUREMENT_DATA table to insert, delete, and update. Still problems with table fragmentation, CPU and I/O load

12. 12 Software Partitioning MEASUREMENT_DATA split into 18 tables, representing 9 days (2 per day)? Application calculates which table to insert into/select from Tables truncated after roll-up rather than delete rows Did look into database partitioning schemes in various databases, but we are database agnostic. Main problem because we store date as long to be portable. Also, not static dates/tables, need to have round robin and reuse tables. 18 MEASUREMENT_DATA tables to represent 9 days (keep up to 7 days with 2 days buffer)?Did look into database partitioning schemes in various databases, but we are database agnostic. Main problem because we store date as long to be portable. Also, not static dates/tables, need to have round robin and reuse tables. 18 MEASUREMENT_DATA tables to represent 9 days (keep up to 7 days with 2 days buffer)?

13. 13 Truncation vs. Deletion Deletion causes contention on rows in table, impacting any concurrent SQL operation Truncation reallocates space for the table object, instead of fragmentation Truncation drops and recreates the table - faster operation (DDL operation)? Truncation drops and recreates the table - faster operationTruncation drops and recreates the table - faster operation

14. 14 Indexes Every InnoDB table has a special index called the clustered index (based on primary key) where the physical data for the rows is stored Advantages Selects faster - row data is on the same page where the index search leads Inserts in (timestamp) order - avoid page splits and fragmentation Fewer Indexes - less space, less maintenance overhead If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution When inserting in ascending order of primary key, InnoDB guarantees no fragmentationIf a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution When inserting in ascending order of primary key, InnoDB guarantees no fragmentation

15. 15 Non-Clustered Index Pages scattered throughout the disk Selects go across non-contiguous pages Leaf nodes on right - index pointing to random leaf nodes Typical BTree viewLeaf nodes on right - index pointing to random leaf nodes Typical BTree view

16. 16 Clustered Index InnoDB by default creates clustered index based on the primary key Physically ordered pages on disk Selects have advantage of fewer I/O operations InnoDB clustered index to Oracle�s Index Organized Tables Important to design the primary index based on utilization. We use timestamp as leading edge due to ascending nature of time plus metric ID as composite primary key.InnoDB clustered index to Oracle�s Index Organized Tables Important to design the primary index based on utilization. We use timestamp as leading edge due to ascending nature of time plus metric ID as composite primary key.

17. 17 Anatomy of a Data Query Query to select aggregate data from metric tables for a specific metric EAM_MEASUREMENT_DATA is a union view of all metric tables Notice the order of query clause and group by/order by are based on order of primary keyNotice the order of query clause and group by/order by are based on order of primary key

18. 18 MySQL View Shortcomings Query optimizer does not apply where condition to inner select, causing entire tables to be selected serially before query condition applied Sequential table scan Temp table space unreasonably large Performance suffers The view gets completely resolved into temp table before query condition appliedThe view gets completely resolved into temp table before query condition applied

19. 19 Fewer Tables Explicitly select only on tables based on time range Where clause still not applied to individual selects in union, but less data selected All rows from tables selected into tmp tableAll rows from tables selected into tmp table

20. 20 Best Performance Temp table as small as possible Using clustered index on timestamp as leading edge - ordering matters in the where clauseTemp table as small as possible Using clustered index on timestamp as leading edge - ordering matters in the where clause

21. ID Generator Requirements Rows need to be populated in schema initialization with hard-coded IDs Start sequential IDs at 10001 to reserve space for hard-coded IDs MySQL�s auto-incrementing does not allow either

22. Sequences Table and Function CREATE TABLE `hq_sequence` ( `seq_name` char(50)? NOT NULL PRIMARY KEY, `seq_val` int(11)? DEFAULT NULL ); Table just has name and current sequence value Function increments and stores new value, as well as return new value Function allows it to be nested in SQL queries as a valueTable just has name and current sequence value Function increments and stores new value, as well as return new value Function allows it to be nested in SQL queries as a value

23. 23 Using Sequences in MySQL Original Solution - InnoDB Sequence Table results in lock timeout and deadlock issues from contention Buffer Using In-Memory/Heap Table - locking issues Sequence table problem stems from multiple threads, long running tx, and scale In-Memory Table - non-transactional, but volatile (non-persisted)? buffer up 1000 ids at a time & increment in-memory, but still have locking issues and deadlocks when ids increment in bulkSequence table problem stems from multiple threads, long running tx, and scale In-Memory Table - non-transactional, but volatile (non-persisted)? buffer up 1000 ids at a time & increment in-memory, but still have locking issues and deadlocks when ids increment in bulk

24. 24 MyISAM Sequence Table Change HQ_SEQUENCE to MyISAM rather than InnoDB MyISAM - non-transactional database table Inconsistent state resulting from server crashes No locking issue because MyISAM is non-tx MyISAM flushing issues when server crashed, resulting in incremented values not persistedNo locking issue because MyISAM is non-tx MyISAM flushing issues when server crashed, resulting in incremented values not persisted

25. 25 Hibernate Hi-Lo Hibernate Hi-Lo sequence generator Back to using HQ_SEQUENCE with InnoDB Hibernate buffers in memory a block of 100 IDs (Low value) and increment when reaches High value Uses separate connection that does not participate in transactions Big performance benefit PostgreSQL & Oracle use native sequence generators, so more roundtrips to database HQ startup time cut down up to 30% (1 min)? Based on Oracle sequence generator. Abstraction can be used with any database InnoDB no flushing problem Key is separate connection (not Hibernate magic), in-memory buffering Worst case - gaps in IDs.Based on Oracle sequence generator. Abstraction can be used with any database InnoDB no flushing problem Key is separate connection (not Hibernate magic), in-memory buffering Worst case - gaps in IDs.

26. 26 Performance Statistics HQ Hardware 2 Quad Core 2 GHz CPUs, 16 GB RAM, 4GB JVM Heap MySQL Hardware 2 Quad Core 1.6 GHz CPUs, 8 GB RAM, 4.5 GB InnoDB Buffer Pool Both on CentOS 5.x Sustained Load Between 200,000 - 300,000 metrics / min, peaked at 2.2 million metrics / min Load Avg HQ ~ 2, Peaked at 8 MySQL ~ 1.5, Peaked at 2.5 CPU Usage HQ and MySQL 10 - 20 % innodb_buffer_pool_size 70-80% of physical memory (standalone DB)? flush log: 0 = per sec (fast), 1 = system decides (no good), 2 = per tx (safest)? A lot of subselects, group by�s, and distincts, which require temp tables to stay on memory & not page on disk flush method (if beefy SAN device) then use O_DIRECT (direct I/O) bypasses O/S buffers, use default otherwise query cache size may lock up < 5.0.50 during refresh cycle, so make 0 (no query caching)?innodb_buffer_pool_size 70-80% of physical memory (standalone DB)? flush log: 0 = per sec (fast), 1 = system decides (no good), 2 = per tx (safest)? A lot of subselects, group by�s, and distincts, which require temp tables to stay on memory & not page on disk flush method (if beefy SAN device) then use O_DIRECT (direct I/O) bypasses O/S buffers, use default otherwise query cache size may lock up < 5.0.50 during refresh cycle, so make 0 (no query caching)?

27. 27 Recommended Server Options innodb_buffer_pool_size innodb_flush_log_at_trx_commit tmp_table_size, max_heap_table_size, and max_tmp_tables innodb_flush_method query_cache_size More information at http://support.hyperic.com innodb_buffer_pool_size 70-80% of physical memory (standalone DB)? flush log: 0 = per sec (fast), 1 = system decides (no good), 2 = per tx (safest)? A lot of subselects, group by�s, and distincts, which require temp tables to stay on memory & not page on disk flush method (if beefy SAN device) then use O_DIRECT (direct I/O) bypasses O/S buffers, use default otherwise query cache size may lock up < 5.0.50 during refresh cycle, so make 0 (no query caching)?innodb_buffer_pool_size 70-80% of physical memory (standalone DB)? flush log: 0 = per sec (fast), 1 = system decides (no good), 2 = per tx (safest)? A lot of subselects, group by�s, and distincts, which require temp tables to stay on memory & not page on disk flush method (if beefy SAN device) then use O_DIRECT (direct I/O) bypasses O/S buffers, use default otherwise query cache size may lock up < 5.0.50 during refresh cycle, so make 0 (no query caching)?