MySQL Scaling Presentation

1.
Strategic MySQL Planningfor Complexity & Growth Or MySQL Scaling for Dummies Thomas Falgout Sr. Developer Yahoo!

2.
The Basics Waysto squeeze extra performance: Query Optimization

3.
Schema Definition

4.
Hardware

5.
Software

6.
MySQL Config

7.
Cache

8.
Monitoring

9.
Query Optimizations KNOWEVERY QUERY!

10.
Developers usually don'twrite proper SQL

11.
SQL Statements willdirectly affect your performance

12.
Example: Repeating SQLStatements for no benefit

13.
1000 very quickunnecessary queries vs 1 slow query

14.
Query Optimizations Don'tDevelop in an island Talk it over.

15.
Your coworkers mightsurprise you.

16.
In a goodway.

17.
Query Optimizations -EXPLAIN Only works for SELECT

18.
Quickly show youhow much data your queries are processing

19.
Could spend anentire session on just this.

20.
But won't.

21.
Read for yourself.

22.
http://dev.mysql.com/doc/refman/5.1/en/explain.html

23.
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

24.
http://www.percona.com/files//presentations/EXPLAIN_demystified.pdf

25.
LUUUUUCCCYYYYYYY!!!!!

26.
Query Optimizations SELECTSSELECT * is bad Hurts performance (cache, maintainability, bandwidth) Joining two 100,000 row tables gets big

27.
“ SELECT COUNT(*)FROM users” Performs full table scan on Innodb

28.
Faster on MyISAM(stored in table info)

29.
Query Optimizations INSERTSCan you batch INSERT?

30.
INSERT INTO aVALUES (1,23),(2,34),(4,33);

31.
To speed upINSERT operations that require multiple statements, LOCK your tables

32.
Can you useLOAD DATA? (Import via CSV)

33.
Query Optimizations UPDATEUpdates are usually pretty good.

34.
Not much tosay.

35.
Query Optimizations DELETEDELETES can be costly. Sometimes causes DB to rebuild index IDEAS Add a “to_delete” column and then delete batch

36.
If having todelete A LOT of rows SELECT INTO rows to keep into temp table

37.
DROP table

38.
RENAME tmp tableto table

39.
Query Optimizations Findqueries that do COUNT(*) or create tables with results Create tables with results

40.
Aggregate data hourly/daily/perupdate/etc.

41.
Query Optimization –Handling Logs Don't talk directly to DB

42.
Send updates throughdaemon to load data

43.
Don't update foreach request Aggregate per 1000 req/2 min/app start

44.
UPDATE counts SETcount = count + 42 WHERE id=1

45.
Schema Definition -MyISAM Original Engine for MySQL

46.
Does not handletransactions

47.
Faster to Write,Slightly faster to Read

48.
Uses separate Data(.MYD), Index (.MYI) and Frame (.frm)

49.
Schema Definition -INNODB 2 nd Engine for MySQL – From Oracle/Innobase

50.
Handles transactions STARTTRANS; INSERT xxx; COMMIT Uses one file for both data and index for entire DB (Can change in my.cnf to separate to table)

51.
Slower to writeand read since it stores data per transaction. Can be tuned to be almost as fast as MyISAM

52.
Schema Definition -BLACKHOLE Fastest Engine available

53.
Writes Binary logsto disk

54.
Doesn't store data

55.
Useful for offloadingreplication from the master

56.
Blackhole Sun. Won't you come. Wash away my data.

57.
Schema Definitions Ifyou can, stick to one storage engine

58.
Falcon New TransactionalEngine

59.
http://forge.mysql.com/wiki/Falcon

60.
Schema Definition -Size Make your data Small

61.
http://dev.mysql.com/doc/refman/5.1/en/data-size.html

62.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

63.
Schema Definition –Size TINYINT – 1 Byte

64.
SMALLINT – 2Bytes

65.
MEDIUMINT – 3Bytes

66.
INT – 4Bytes

67.
BIGINT – 8Bytes

68.
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

69.
Schema Definition -Size CHAR(N) = N + 1 byte. 2 bytes if N > 255 bytes

70.
VARCHAR(N) = X+ 1 byte. X = Number of actual characters in string.

71.
N & X= 1 Byte. Unless using utf8 or a multi-byte format

72.
Same rules applyfor BINARY & VARBINARY.

73.
Extra data isTRUNCATED!!!!!!

74.
That's bad.

75.
http://dev.mysql.com/doc/refman/5.0/en/string-types.html

76.
Schema Definition -VARCHAR VARCHAR(255) is usually the sign of a careless design

77.
VARCHAR(255) is 4.xlimitation

78.
When creating atemp table, it allocates the greatest size of data (255)

79.
Schema Definition -INT BIGINT is not needed for AUTO_INCREMENT

80.
INT UNSIGNED =4.3 Billion values. If you hit this, you should be partitioning Unless you have a reason, all of your INT columns should be UNSIGNED Adds data integrity for negative values

81.
Schema Definition -Date TIMESTAMP = 4 bytes

82.
DATETIME = 8bytes DATE = 3 bytes

83.
TIME = 3bytes

84.
??? INT asUNIX Timestamp = 4 bytes

85.
Schema Definition –Storing IP's IP = 4 Bytes

86.
192.168.101.201 = 11000000.10101000.01100101.11001001

87.
INT UNSIGNED =4 Bytes!

88.
INET_NTOA => NumberTOAddress

89.
INET_ATON => AddressTONumber

90.
Don't use VARCHAR(15)Avg of 12 bytes vs 4 bytes

91.
Schema Definition –IP Addr mysql> SELECT INET_ATON('192.168.101.201'); +------------------------------+ | INET_ATON('192.168.101.201') | +------------------------------+ | 3232261577 | +------------------------------+ mysql> SELECT ... WHERE ip_col BETWEEN INET_ATON('12.13.14.15') AND INET_ATON('13.14.15.16')

92.
Schema Definition -ENUM Use Codes for enum m / f instead of male / female Must alter table to add an enum

93.
Schema Definition -BLOB BE WARY! SELECT <field> will return THE WHOLE THING!

94.
Stores first as768 Bytes, and then increments into 16k data pages per row per TEXT/BLOB field

95.
Hardware DB typicallyrequire hot on RAM before CPU

96.
Separate your Appand DB Keep them close.

97.
Network latency affectsexperience

98.
Hardware Split upREADS vs WRITES Create a Slave to replicate your DB to

99.
Send all READSto Slave

100.
Send all WRITESto Master

101.
Hardware – MySQLCluster Realtime Transactional DB Engine

102.
Provides HA w/no Single Point of Failure

103.
Requires 3 servers(2 Data, 1 Mgmt Nodes)

104.
Inserts on oneDB are replicated to others

105.
Selects can beperformed on any DB

106.
http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html

107.
http://www.lod.com/whitepapers/mysql-cluster-howto.html

108.
Software – MySQLProxy Program between your client and your DB

109.
Can be usedfor Load Balancing

110.
Failover

111.
Query Analysis

112.
Query Filtering /Modification http://forge.mysql.com/wiki/MySQL_Proxy

113.
Software – MaatkitSeries of Tools for monitoring & administration Table Checksum (Compare Master/Slave)

114.
Table Sync (ResolveDifferences)

115.
Visual Explain (Explain! Visually!)

116.
Heartbeat (Shows SlaveReplication Delay)

117.
And Much, MuchMore! http://www.maatkit.org/

118.
Scaling Reads GetMore Servers

119.
Point Slaves toMaster

120.
Put Slaves BehindLoad Balancer

121.
Scaling Writes NoEasy button

122.
Batch Insert /Update

123.
Don't Delete

124.
Partition your Data

125.
If you're atthis point, this presentation probably won't help you. Read Cal Henderson's Scaling MySQL

126.
MySQL Config Doyou know what those config variables do? SHOW VARIABLES key_buffer_size

127.
table_cache

128.
innodb_buffer_pool_size

129.
query_cache_limit http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

130.
MySQL Config key_buffer_sizeMyISAM Only

131.
Size of cachefor MyISAM Indexes

132.
Can check performancewith SHOW STATUS key_read_requests

133.
key_reads

134.
key_write_requests

135.
key_writes

136.
key_reads/key_read_requests < 0.01== GOOD

137.
MySQL Config table_cache/ table_open_cache (5.1.3) Number of open tables for all threads

138.
This dictates thenumber of open file descriptors

139.
MySQL Config innodb_buffer_pool_sizeINNODB only

140.
Size in bytesof the memory buffer InnoDB uses to cache data and indexes.

141.
Default 8MB

142.
MySQL Config QueryCache The query cache stores the text of a SELECT statement with the corresponding result that was sent to the client

143.
Great for tablesthat don't change often

144.
Doesn't return staledata

145.
Cache is invalidatedif returned entry is modified

146.
Another reason whySELECT(*) is bad

147.
MySQL Config MemoryINNODB – Let MySQL use all memory

148.
MyISAM – Leavemore memory for OS page caches

149.
MySQL Config MySQLSlow Log Used to log any queries that take longer than x seconds

150.
Use long_query_time tospecify x

151.
~1 sec isgood starting point

152.
http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

153.
Caching - ExternalMemcache Used by all the big players (Y!, Facebook, etc.)

154.
Originally created byLiveJournal to handle scale

155.
EASY Button

156.
$memcache_obj = memcache_connect("localhost",11211);

157.
$memcache_obj->add('var_key', 'test variable');

158.
$var = $memcache_obj->get('var_key');

159.
Caching - InternalQuery Cache http://dev.mysql.com/doc/refman/5.1/en/query-cache.html key_buffer_size http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html innodb_buffer_pool_size http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.htm

160.
Benchmarking - mysqlslapMysqlslap

161.
mysqlslap -u root-concurrency=10000 -iterations=5 -number-of-queries=10000 -query=”SELECT name FROM city WHERE city_id=12”

162.
Benchmarking – ApacheBench Built for testing webservices

163.
Like mysqlslap

164.
GOOD NEWS Benchmarkwhole web app BAD NEWS Benchmark whole web app http://httpd.apache.org/docs/2.2/programs/ab.html

165.
Monitoring Monitoring isas important as benchmarking

166.
Basics: SHOW FULLPROCESSLIST

167.
SHOW GLOBAL STATUS

168.
SHOW GLOBAL VARIABLES

169.
Monitoring Tools Cacti– RRDtool based networking graphing tool

170.
MUNIN – Similarto Cacti, but easier to maintain

171.
Nagios – Moreof a notification tool

172.
MySQL Enterprise Dashboard– Notification & Monitoring

173.
Other Write ProperSQL – Naming Convention customer_id, order_id NOT id

174.
Be descriptive

175.
Avoid reserve words(date, time, timestamp)

176.
Other NEVER teston a production system

177.
Goal of testenvironment: NOT to test your software

178.
Break your software.

179.
Other SET sql_mode= 'STRICT_TRANS_TABLES' Forces you to use good data

180.
The Most ImportantLesson This part is absolutely important that you get. It's time to wake up.

181.
The Most ImportantLesson When you need to, Ignore everything I've suggested.

182.
Thanks Thanks forlistening.

183.
Links Flickr -Scaling MySQL http://www.scribd.com/doc/21593/Scaling-PHPMySQL Very Basic PHP & MySQL: http://www.cs.gsu.edu/~stefan/teaching/csc-6370/notes/phpmysql.pdf JPIPES! http://jpipes.com/presentations/joinfu/joinfu_part_one.pdf http://jpipes.com/presentations/joinfu/joinfu_part_two.pdf http://jpipes.com/presentations/dqm/legend_drunken_query_master.pdf EXPLAIN: http://www.percona.com/files//presentations/EXPLAIN_demystified.pdf Query Optimization: http://www.percona.com/files//presentations/Zurich2007-QueryOptimization.pdf Server Settings Tuning: http://www.percona.com/files//presentations/UC2007-MySQL-Server-Settings-Tuning.pdf http://www.flupps.org/mysql/MySQL_Microscope.ppt http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html http://www.devshed.com/c/a/MySQL/MySQL-Server-Tuning-Tips-and-Tricks/ http://www.planetmysql.org/

MySQL Scaling Presentation

More Related Content

What's hot

Similar to MySQL Scaling Presentation

Recently uploaded

MySQL Scaling Presentation