Ten Thousand Tables Can’t Be Wrong… Presented by, MySQL AB® & O’Reilly Media, Inc. Richard Chart – Co-Founder, ScienceLogic, LLC [email protected] www.sciencelogic.com.
Download
Report
Transcript Ten Thousand Tables Can’t Be Wrong… Presented by, MySQL AB® & O’Reilly Media, Inc. Richard Chart – Co-Founder, ScienceLogic, LLC [email protected] www.sciencelogic.com.
Ten Thousand Tables
Can’t Be Wrong…
Presented by,
MySQL AB® & O’Reilly Media, Inc.
Richard Chart – Co-Founder,
ScienceLogic, LLC
[email protected]
www.sciencelogic.com
This Presentation is about…
An unusual approach to application scaling
using MySQL
The design choices we made early in product
design, and how they look a few years later
A few hard earned lessons in what to look for as
an application grows beyond its roots
…Covering
The decision path that led to using MySQL in this
way
The growth path that got us to current scale
Along the way I will talk about:
What we discovered about MySQL write performance
While real world monitoring is important… understanding
how to interpret the results is even more so
In the beginning…
…or at least the beginning for ScienceLogic:
2003
Product concept: IT Management Appliance
A series of choices - fundamental technology for
each layer:
Hardware
Operating System
Application Architecture
Database technology & architecture
Sweet Layers – Shirley Shelton
Technology Choices
Hardware/OS/architecture – for another day
Database Technology: MySQL
1.
2.
3.
4.
Performance
Stability
Experience
Support (increasingly important)
Scale
Starting point: single appliance supporting 200
devices
Goal: extensible architecture with very deep host and application monitoring
1,000+ devices, each with 100+ management points
Current reality –
several times the scale of the original goal
Future: The fundamentals are sound: next
generation of the product moves up another
order of magnitude
Database Architecture
The challenge:
Wide range of monitored nodes
(100…1,000+)
Wide range of data points (ping…hundreds
of HW, OS and application metrics)
Distributed collection (WAN: latency,
security concerns)
Commercial product: MySQL dual licensing
comes into play
Database Architecture
Data Characteristics:
90+% Writes
ACID not important
Resilient to loss
(Remaining data is not invalidated by gaps)
Data elements valuable by themselves
Data elements much more valuable when
relationships added
The MyISAM Fit
Very fast writes when no lock contention
Simple data handling
Lower license cost that InnoDB (important only
for those of us shipping commercial products)
Not transactional – and we don’t care!
Not InnoDB (all the opposite reasons)
Slower to stuff with data
More expensive (for us commercial folks)
What about memory tables?
Limited applicable areas in EM7: most of the data has to
live on disk for reporting weeks or months in future
No measurable benefit over MyISAM in transient data
areas where we could use them in EM7
Because we take advantage of MyISAM cached indexes
and the required data is in the index
…but the application continues to evolve, we will use
them in future if the right situation occurs
Scaling without lock contention
The ace in the hole
Dynamically created tables
No more than one thread writing to a
table at once
Separate thread consolidates data for
reporting across devices
This approaches the sharding
architecture used in highly scalable web
sites, but with core data stored centrally
Multiple Threads / Multiple Collectors
Dynamic Databases and Tables
Stats Databases
Dynamic_app_18
Dynamic_app_20
Stat_1
Stat_12
Stat_13
Stat_14
Stat_15
Stat_120
Stat_121
Stat_250
Dynamic_app_21
Stat_1
Stat_253
Stat_300
Stat_301
Stat_302
Dynamic_app_22
Dynamic_app_27
Dynamic_app_28
Stat_56
Stat_77
Stat_500
State_600
Stat_59
Stat_79
Stat_550
Stats_601
Stat_16
Stat_80
Stat_551
Stats_602
Stat_18
Stat_81
Stat_552
Stats_650
Stat_180
Stat_82
Stat_553
Stats_651
Stat_181
Stat_89
Stat_554
Stats_640
Stat_555
Stats_642
Stats_649
Dynamic Table Creation
NO_TABLE = 1146 # MySQL error code
try:
db.execute(“INSERT INTO dynamic_app_%s.stat_%s
VALUES (10, ’sample data’)”, (app, device))
except MySQLdb.Error, e:
if e.args[0] == NO_TABLE:
db.execute(“CREATE DATABASE IF NOT EXISTS dynamic_app_%s”, (app))
db.execute(“CREATE TABLE IF NOT EXISTS dynamic_app_%s.stat_%s
LIKE dynamic_app_0.stat_0”,(app, device))
Growth Curve
250
200
150
Collectors
Devices / 10
100
Tables / 100
50
0
Mth Mth Mth Mth Mth Mth
2
4
6
8
10
12
How far does
this go?
So Far:
20,000+ tables
2,200+ queries per
second
5 billion rows
93% writes
Next limit is how
quickly data can be
stuffed onto disk
The Database Platform
4 x Intel Xeon Dual Core 7140M,
16MB Cache, 3.4GHz, 800MHz
FSB
16GB (16 x 1GB) 400MHz Single
Ranked DIMMs
Hardware RAID Controller
10 x 146GB 15k RPM SAS Drives
(RAID10)
Linux 2.6 kernel
MySQL 5.0.x
Managing Performance With Growth
As usage rates escalate, things that once were
fine become an issue…eg:
Query Cache entries purged due to too many entries,
or too many changes to underlying tables
Lock contention
Sort data set size causing increased
created_tmp_disk_tables rather than
created_tmp_tables
Open Files & Tables
Critical when scaling this way
show global status like 'open%'
Open Files (Linux)
/etc/security/limits.conf
mysql
hard
nofile
20480
mysql
soft
nofile
20480
/etc/my.cnf
[safe_mysqld]
open_files_limit = 20480
[mysqld]
table_cache=8192
Misc MyISAM Tuning Helpers
concurrent_insert = 2
1 (Default) Enables concurrent insert for MyISAM tables
that don't have holes
2 Enables concurrent inserts for all MyISAM tables,
even those that have holes. For a table with a hole, new
rows are inserted at the end of the table if it is in use by
another thread. Otherwise, MySQL acquires a normal
write lock and inserts the row into the hole.
myisam_recover = QUICK, BACKUP
Trends
Very important to measure DB stats over time
show global status like
‘opened_tables';
Point in time counter useless in its own right
…very valuable with 5 minute poll and graphed deltas
Some stats must be combined to be useful
Percentage of requests waiting for locks (deltas)
(table_locks_waited/(table_locks_waited +
table_locks_immediate) * 100
Monitoring
MySQL Enterprise Monitor
Very worthwhile tool – take advantage of it if you
subscribe to MySQL Enterprise support
Other Tools
EM7 monitors databases (as well as servers, routers,
firewalls, etc. etc.), so of course we use that…
EM7 Example
Monitoring Caveat!
Some monitored changes are obviously bad…
eg increase in created_tmp_disk_tables
Some monitors are
misleading…
…what’s going on
here?
What really happened:
Misconfigured clients
caused the CPU
load
created_tmp_tables
unconnected with
the CPU load
EXPLAIN showed
small row sets being
sorted in memory
tmp tables
Summarizing & Pruning Data
Keeping the source statistics tables small is key
for ongoing performance –
Summarized data for reporting
Infrequent writes, regular reads - MyISAM fine here also in most
cases
In EM7 we summarize hourly, daily, monthly etc.
Retention periods configurable
DELETEs suck MySQL performance…
can use a deleted row marker
If you can do a purge in your app, you’re golden
In EM7 we schedule DELETEs for a nightly quiet time
Summarizing Data With Dynamic
Stored Procedure
USE dynamic_app_data_43;
DROP PROCEDURE IF EXISTS dynamic_app_43.dynamictest;
DELIMITER //
CREATE PROCEDURE dynamic_app_43.dynamictest(n INTEGER)
BEGIN
SET @s = "";
SET @s = CONCAT(@s," INSERT INTO app_crunched
(did,object,ind,year,month, date,average, total, poll_count)");
SET @s = CONCAT(@s," SELECT
",n,",object,ind,YEAR(date),MONTH(date),date,0,0,0");
SET @s = CONCAT(@s," FROM stat_", n);
SET @s = CONCAT(@s," WHERE crunched = 0");
#SELECT @s;
PREPARE exe FROM @s;
EXECUTE exe;
END;
//
DELIMITER ;
CALL dynamictest(5);
What’s The Downside?
Reporting tools not good with dynamic
databases and tables (eg Crystal Reports)
80 : 20 rule:
Above a certain size of implementation, some tables
just have to use row locking
(with EM7, 1,500 devices, 10,000 tables, and 0.1%
need to be InnoDB… so should be the 99.9 : 0.1 rule)
Backup and data maintenance complexity of
multiple engines
So… MyISAM all the way?
Well, no.
In our application, at larger sites, around a
dozen tables need the characteristics of
InnoDB, or around 0.1%
Be selective in the storage engine choice,
consider relative merits for each part of the
application
Questions
Richard Chart
[email protected]
A hand on us…
Contact for a pack:
Richard Chart
[email protected]