Measuring My SQL Server Performance for Sensor Data

Measuring My. SQL Server Performance for Sensor Data Stream Processing Jacob Nikom MIT Lincoln Laboratory The My. SQL Users Conference 2006 27 April 2006 MIT Lincoln Laboratory My. SQL Users Conf. -1 This 04 -27 -2006 work was sponsored by the U. S. Army Space and Missile Defense Command under Air Force Contract# FA 8721 -05 -C-0002. Opinions, interpretations, recommendations and conclusions are that of the author and are not necessarily endorsed by the United States Government.

Outline • Introduction • Benchmark for real-time streamed data recording • Benchmark for real-time streamed events processing • Summary My. SQL Users Conf-2 04 -27 -2006 MIT Lincoln Laboratory

Outline • Introduction – Reagan Test Site (RTS) and its instrumentation – RTS Operations Coordination Center (ROCC) – ROCC architecture and real-time data flow – ROCC backend design • Benchmark for real-time streamed data recording • Benchmark for real-time streamed events processing • Summary My. SQL Users Conf-3 04 -27 -2006 MIT Lincoln Laboratory

Reagan Test Site (RTS) and its Instrumentation – Multiple RF sensors collecting data in several regions of electromagnetic spectrum – Multiple optical sensors collecting objects’ metric and spectral characteristics – Telemetry systems capable of tracking multiple targets – Mobile and fixed ground safety instrumentation My. SQL Users Conf-4 04 -27 -2006 MIT Lincoln Laboratory

RTS Operations Coordination Center (ROCC) ROCC controls RTS instrumentation ROCC Data Analysis Algorithms Activity Simulation Displays Database Network Sensors Primary operations: – – – My. SQL Users Conf-5 04 -27 -2006 Manages data flow from multiple sensors Analyses the acquired data Displays tracks and predicts the path of space objects Stores acquired data for later analysis and reporting Simulates past and future activities MIT Lincoln Laboratory

ROCC Architecture and Real-Time Data Flow Sensors ROCC Publish-subscribe middleware ROCC Interface Box_1 Historian Alg_1 My. SQL server ROCC Interface Box_N My. SQL Users Conf-6 04 -27 -2006 Operational Data Store (ODS) Alg_M MIT Lincoln Laboratory

ROCC Backend Design Network/middleware Queries Messages Queries Historian subscription threads My. SQL server ODS Questions for benchmark to answer (then) • How well Historian/My. SQL server pair could satisfy current performance requirements for particular hardware platform and operating system? • How well it could satisfy future requirements (how scalable is the solution)? • How server performance depends upon specific hardware? • How server performance depends upon specific software? My. SQL Users Conf-7 04 -27 -2006 MIT Lincoln Laboratory

Outline • Introduction • Benchmark for real-time streamed data recording – Insertion measurement benchmark (IMB) – Testing system configurations – Analysis of IMB results Comparing one and multiple tables performance Comparing local and remote servers performance Comparing My. SQL 4. 1 and 5. 0 servers performance Comparing My. SQL 4. 1 and 4. 0 servers performance Comparing My. SQL 4. 0, 4. 1 and 5. 0 servers performance Comparing Inno. DB engine vs. My. ISAM engine performance Comparing JDK 1. 5. 0 64 -bit vs. JDK 1. 4. 2 32 -bit performance • Benchmark for real-time streamed events processing • Summary My. SQL Users Conf-8 04 -27 -2006 MIT Lincoln Laboratory

Insertion measurement benchmark My. SQL Users Conf-9 04 -27 -2006 MIT Lincoln Laboratory

Testing system configurations Hardware tested Quantity 2 CPU Memory Quad CPU Opteron, 64 -bit, 8 GB 1 MB Cache 4 Network RAID 5 SCSI Ultra 320, 300 GB 1 Gb/sec 144 GB, 10 K rpm, 4. 7 ms Dual CPU Opteron, 64 -bit, 4 - 8 GB RAID 5, SATA, 250 GB, 80 GB, 7. 2 K rpm, 10. 9 ms, Ultra. ATA-100, 7. 2 K rpm, 11. 5 ms 1 Gb/sec 2 - 3 GB Ultra SCSI 320, 146 GB, 15 K rpm, 3. 5 ms 1 Gb/sec 1 MB Cache 2 Disks Single and dual CPU Intel(R) Xeon(TM) CPU, 2. 0 GHz, SCSI Ultra 160, 73 GB, 10 K rpm, 7. 8 ms 2 MB Cache Software tested # Operating System My. SQL Server Java Client 1 RHEL 4 AS, kernel 2. 6. 9 -5. ELsmp, 64 -bit 4. 0. 18 -standard 32/64 -bit build 1. 4. 2_03, 32 -bit 2 RHEL 4 ES, kernel 2. 6. 9 -11. ELsmp 32 -bit 4. 1. 11 -standard 32/64 -bit build 1. 4. 2_10, 32 -bit 6 SLES 8 kernel 2. 4. 21, 64 -bit 5. 0. 16 -standard 32/64 -bit build 1. 5. 0_01, 64 -bit My. SQL Users Conf-10 04 -27 -2006 MIT Lincoln Laboratory

Outline • Introduction • Benchmark for real-time streamed data recording – Insertion measurement benchmark (IMB) – Testing system configurations – Analysis of IMB results (My. ISAM tables) Comparing one and multiple tables performance Comparing local and remote servers performance Comparing My. SQL 4. 1 and 5. 0 servers performance Comparing My. SQL 4. 1 and 4. 0 servers performance Comparing My. SQL 4. 0, 4. 1 and 5. 0 servers performance Comparing Inno. DB engine vs. My. ISAM engine performance Comparing JDK 1. 5. 0 64 -bit vs. JDK 1. 4. 2 32 -bit performance • Benchmark for real-time streamed events processing • Summary My. SQL Users Conf-11 04 -27 -2006 MIT Lincoln Laboratory

Comparing One and Multiple Tables Performance (My. ISAM) 4 -CPU Opteron –— local client and server (the same machine) My. SQL Users Conf-12 04 -27 -2006 MIT Lincoln Laboratory

Comparing Local and Remote Servers Performance (My. ISAM) 2 -CPU server; 2 -CPU remote client/2 -CPU local client and server 2 -CPU server; 4 -CPU remote client/2 -CPU local client and server My. SQL Users Conf-13 04 -27 -2006 MIT Lincoln Laboratory

Comparing My. SQL 4. 1 and 5. 0 Servers Performance (My. ISAM) 1 -CPU Intel Xeon—local client and server My. SQL Users Conf-14 04 -27 -2006 MIT Lincoln Laboratory

Comparing My. SQL 4. 0 and 4. 1 Servers Performance (My. ISAM) 1 -CPU Intel Xeon—local client and server My. SQL Users Conf-15 04 -27 -2006 MIT Lincoln Laboratory

Comparing My. SQL 4. 0, 4. 1, and 5. 0 Servers Performance (My. ISAM) 1 -CPU Intel Xeon—local client and server My. SQL Users Conf-16 04 -27 -2006 MIT Lincoln Laboratory

Comparing Inno. DB vs My. ISAM and JDK 1. 4. 2 32 -bit vs JDK 1. 5. 0 64 -bit 2 -CPU Opteron client; 4 -CPU Opteron server; My. SQL 4. 0. 18 My. SQL Users Conf-17 04 -27 -2006 MIT Lincoln Laboratory

Outline • Introduction • Benchmark for real-time streamed data recording • Benchmark for processing real-time streamed events – Client/Server (CS)–based ROCC architecture – Using indexes for real-time data storing and retrieving – Records retrieval acceleration using secondary query and indexes – Event measurement benchmark (EMB) – Analysis of EMB Results – Comparing My. SQL 4. 1 and 5. 0 servers performance (My. ISAM) • Summary My. SQL Users Conf-18 04 -27 -2006 MIT Lincoln Laboratory

Client/Server (CS)–based ROCC architecture Sensors ROCC Interface Box_1 Alg_1 My. SQL server ROCC Interface Box_N ODS Alg_M CS advantages • “Don’t need special “recording” component (Historian) • Pull” model—all components get the data when they want them • The same API for the current and historical data • Excellent filtering capabilities (SQL) • High throughput, very simple settings, no load balancing • Very standard, very low cost, low demand for resources CS drawbacks • • Data centralization More workload for the network – UDP vs. TCP/IP, no multicast More workload for the database server Require indexes and multilevel queries—more complex requests My. SQL Users Conf-19 04 -27 -2006 MIT Lincoln Laboratory

Using Indexes for Storing and Retrieving Real-Time Data Traditional (interactive and batch) approach 1. Store data into the relational table 2. Add indexes to the table once the data accumulation is finished 3. Retrieve the data using added indexes Advantages • Simplicity • Independence of indexes addition from data creation Drawbacks • Adding indexes to all data in the table is a slow process • Indexes will be recalculated every time a new record is stored Real-time (continuous) approach 1. 2. 3. 4. 5. Store data into the relational table using primary key with AUTO_INCREMENT option Retrieve the latest data using primary key, ORDER and LIMIT query parameters Store the latest retrieved data in the temporary derived table Make the search of the record using all available fields only in the derived table Advantages • Very fast! Index creation time is spread over all insertion operations (10% overhead) • LIMIT parameter defines the width of the window (number of records for the second SELECT statement) • Primary key is indexed automatically Drawbacks • Primary key has to be numeric to allow AUTO_INCREMENT option • Only one index per table could be used for selecting the derived table My. SQL Users Conf-20 04 -27 -2006 MIT Lincoln Laboratory

Records Retrieval Acceleration Using Inner Query and Indexes Table for record insertion and retrieval Primary autoincrement key (indexed) record. ID Column 1 column 1 Last record in the table N 301 Last record in the second query window N-1 299 N-2 303 Found record First record in the second query window First record in the table ……. N-M 400 N-M-1 ……. 1 ……. ………. second query window ……. window width = M<<N SELECT * FROM ( SELECT * FROM table 1 ORDER BY record. ID DESC LIMIT M ) t WHERE t. column 1< 300 My. SQL Users Conf-21 04 -27 -2006 MIT Lincoln Laboratory

Event Measurement Benchmark (EMB) Goals and Design • Goals – Measure the latencies between freshly stored and retrieved events – Demonstrate that more than 2 MB/sec throughput could be achieved – Demonstrate the sustainable latencies were less that 300 msec • Design – Multiple writing clients write into multiple tables time-stamping each record – Multiple reading clients read from into different tables while the writing clients continue to write into the tables – Reading clients compare each record time stamp with current time and calculate the latency – Reading clients write the results into result tables My. SQL Users Conf-22 04 -27 -2006 MIT Lincoln Laboratory

EMB Goals and Design Computer 0 Writing. Client_3 Writing. Client_2 Writing. Client_1 Computer 1 Reading Client_6 Reading Client_5 Reading Client_4 Reading Client_3 Reading Client_2 Reading Client_1 Computer 2 Reading Client_6 Reading Client_5 Reading Client_4 Reading Client_3 Reading Client_2 Reading Client_1 My. SQL server My. SQL database Writing tables 0 Result tables 1 My. SQL Users Conf-23 04 -27 -2006 Result tables 2 MIT Lincoln Laboratory

Analysis of EMB Results Database server— 4 CPU Opteron, writing/reading clients— 2 CPU Opterons My. SQL server version 4. 1. 11–standard, Network 1 Gb/sec, JDK 1. 4. 2_10 Throughput = 2 MB/sec, 90% latencies within 100 msec Throughput = 4 MB/sec, 90% latencies within 100 msec My. SQL Users Conf-24 04 -27 -2006 Throughput = 3 MB/sec, 90% latencies within 40 msec Latencies histograms for writing/reading threads for 4 clients MIT Lincoln Laboratory

Comparing My. SQL 4. 1 and 5. 0 Servers Performance (My. ISAM) Database server— 2 CPU Xeon, writing and reading clients—two 2 CPU Opterons Network 1 Gb/sec, JDK 1. 4. 2_10 My. SQL 5. 0. 16 My. SQL Users Conf-25 04 -27 -2006 My. SQL 4. 1. 11 MIT Lincoln Laboratory

Outline • Introduction • Benchmark for recording real-time streamed data • Analysis of Insertion Measurement Benchmark (IMB) • Benchmark for processing real-time streamed events • Summary My. SQL Users Conf-26 04 -27 -2006 MIT Lincoln Laboratory

Summary • • IMB and EMB were designed to model major data flows in ROCC IMB demonstrated: – Java/My. SQL client/server architecture satisfies ROCC requirements – My. SQL 4. 1. 11 was the fastest version for insertion transactions – My. ISAM showed superior performance relative to Inno. DB – JDK 1. 5. 0 64 -bit delivers much better performance than 1. 4. 2 32 -bit • EMB demonstrated: – Java/My. SQL-based client/server architecture with appropriate queries and indexation could be used for ESP applications – It delivered better throughput and latencies than currently employed publish/subscribe architecture – My. SQL 5. 0. 16 exhibited better linearity and performance for large number of writing/reading threads than 4. 1. 11 version • Benchmarks showed that My. SQL server is capable to handle both real-time data recording and real-time event stream processing My. SQL Users Conf-27 04 -27 -2006 MIT Lincoln Laboratory