mysql 5.1 and beyond
DESCRIPTION
MySQL 5.1 adds new features and more performance. And MySQL 5.4 is even faster. Some theory and practical testsTRANSCRIPT
![Page 1: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/1.jpg)
MySQL 5.1 (and more)What's new
Giuseppe Maxia
MySQL Community Team Lead
![Page 2: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/2.jpg)
about me -Giuseppe Maxia a.k.a. The Data Charmer MySQL Community Team Lead Long time hacking with MySQL features Formerly, database consultant, designer, coder. A passion for QA An even greater passion for open source ... and community Passionate blogger http://datacharmer.blogspot.com
![Page 3: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/3.jpg)
MySQL 5.1 GA
![Page 4: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/4.jpg)
MySQL 5.1 GA
![Page 5: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/5.jpg)
MySQL 5.1 GA
![Page 6: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/6.jpg)
MySQL 5.1 featuresPartitions performance
row-based replication stability
event scheduler ease of use
logs on demand ease of use
plugin interface extensibility
GENERAL PERFORMANCE performance
![Page 7: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/7.jpg)
What exactly is this "partitions" thing? Logical splitting of tables Transparent to user
![Page 8: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/8.jpg)
Remember the MERGE tables? separate tables risk of duplicates insert in each table no constraints
MERGE TABLE
![Page 9: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/9.jpg)
It isn't a merge table! One table No risk of duplicates insert in one table constraints enforced
PARTITIONED TABLE
![Page 10: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/10.jpg)
Partition pruning 1a - unpartitioned table - SINGLE RECORD
select * from table_name where colx = 120
![Page 11: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/11.jpg)
Partition pruning 1b - unpartitioned table - SINGLE RECORD
select * from table_name where colx = 350
![Page 12: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/12.jpg)
Partition pruning 1c - unpartitioned table - RANGE
select * from table_name where colx between 120 and 230
![Page 13: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/13.jpg)
Partition pruning 2a - table partitioned by colx - SINGLE REC
select * from table_name where colx = 120
100-199
1-99
200-299
300-399
400-499
500-599
![Page 14: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/14.jpg)
Partition pruning 2b - table partitioned by colx - SINGLE REC
select * from table_name where colx = 350
100-199
1-99
200-299
300-399
400-499
500-599
![Page 15: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/15.jpg)
Partition pruning 2c - table partitioned by colx - RANGE
100-199
1-99
200-299
300-399
400-499
500-599
select * from table_name where colx between 120 and 230
![Page 16: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/16.jpg)
Partition pruning
EXPLAINselect * from table_name where colx = 120
EXPLAIN PARTITIONSselect * from table_name where colx = 120
in 5.1
before
![Page 17: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/17.jpg)
Partition pruning - unpartitioned tableexplain partitions select count(*) from table_name where colx=120\G***** 1. row **** id: 1 select_type: SIMPLE table: table_name partitions: p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 type: index...
![Page 18: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/18.jpg)
Partition pruning - unpartitioned tableexplain partitions select count(*) from table_name where colx between 120 and 230\G***** 1. row **** id: 1 select_type: SIMPLE table: table_name partitions: p01,p02,p03,p04,p05,p06,p07,p08,p09,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19 type: index...
![Page 19: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/19.jpg)
Partition pruning - table partitioned by colxexplain partitions select count(*) from table_name where colx between 120 and 230\G***** 1. row **** id: 1 select_type: SIMPLE table: table_name partitions: p02,p03 type: index...
![Page 20: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/20.jpg)
HOW TO MAKE PARTITIONS
![Page 21: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/21.jpg)
HOW TO MAKE PARTITIONS
RTFM ...
![Page 22: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/22.jpg)
HOW TO MAKE PARTITIONS
RTFM ... No, seriously, the manual has everything
![Page 23: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/23.jpg)
HOW TO MAKE PARTITIONS
RTFM ... No, seriously, the manual has everything But if you absolutely insist ...
![Page 24: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/24.jpg)
HOW TO MAKE PARTITIONSCREATE TABLE t1 ( id int ) ENGINE=InnoDB # or MyISAM, ARCHIVEPARTITION BY RANGE (id)( PARTITION P1 VALUES LESS THAN (10), PARTITION P2 VALUES LESS THAN (20))
20
![Page 25: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/25.jpg)
HOW TO MAKE PARTITIONSCREATE TABLE t1 ( id int ) ENGINE=InnoDB PARTITION BY LIST (id)( PARTITION P1 VALUES IN (1,2,4), PARTITION P2 VALUES IN (3,5,9))
21
![Page 26: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/26.jpg)
HOW TO MAKE PARTITIONSCREATE TABLE t1 ( id int not null primary key) ENGINE=InnoDB PARTITION BY HASH (id)PARTITIONS 10;
22
![Page 27: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/27.jpg)
HOW TO MAKE PARTITIONSCREATE TABLE t1 ( id int not null primary key) ENGINE=InnoDB PARTITION BY KEY ()PARTITIONS 10;
23
![Page 28: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/28.jpg)
Limitations• Can partition only by INTEGER columns• OR you can partition by an expression, which must
return an integer• Maximum 1024 partitions
• If you have a Unique Key or PK, the partition column must be part of that key
• No Foreign Key support• No Fulltext or GIS support
24
![Page 29: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/29.jpg)
Benchmarking partitions Compare results Unpartitioned vs partitioned ISOLATION Repeatability Check your resources!
![Page 30: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/30.jpg)
Partitions with InnoDB (laptop) Key points: Takes much more storage than other engines
engine storage (MB)
innodb 221myisam 181archive 74innodb partitioned (whole) 289innodb partitioned (file per table) 676myisam partitioned 182archive partitioned 72
![Page 31: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/31.jpg)
Benchmarking results (laptop)engine query year
2000query year 2002
InnoDB 1.25 1.25
MyISAM 1.72 1.73
Archive 2.47 2.45
InnoDB partitioned whole
0.24 0.10
InnoDB Partitioned (file per table)
0.45 0.10
MyISAM partitioned 0.18 0.12
Archive partitioned 0.22 0.12
![Page 32: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/32.jpg)
Partitions with InnoDB (huge server) Key points: Takes much more storage than other engines
engine storage (GB)
innodb (with PK) 330myisam (with PK) 141archive 13innodb partitioned (no PK) 237myisam partitioned (no PK) 107archive partitioned 13
![Page 33: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/33.jpg)
Benchmarking results (huge server)engine 6 month rangeInnoDB 4 min 30sMyISAM 25.03sArchive 22 min 25sInnoDB partitioned by month 13.19MyISAM partitioned by year 6.31MyISAM partitioned by month 4.45Archive partitioned by year 16.67Archive partitioned by month 8.97
![Page 34: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/34.jpg)
What is the event scheduler Temporal triggers NOT related to a specific table Execute SQL code
at a given time or at given intervals
Created by Andrey Hristov First released with MySQL 5.1
![Page 35: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/35.jpg)
How does it work?
MySQL Server
event scheduler thread
regular threadregular threadregular threadregular threadregular thread
event time?
event thread
start
![Page 36: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/36.jpg)
Why using the event scheduler? Cross platform scheduler No external applications needed No overhead
![Page 37: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/37.jpg)
How to use the event scheduler1. Enable the event scheduler
A. in the option file• event-scheduler=1B. online • SET GLOBAL event_scheduler=ON;
2. Create an event3. Check the effects
![Page 38: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/38.jpg)
Event creation syntaxCREATE EVENT event_nameON SCHEDULE AT {DATE AND TIME}DO {SQL COMMAND};
CREATE EVENT event_nameON SCHEDULE EVERY {X} {SECOND|MINUTE|HOUR|DAY|MONTH|YEAR|WEEK}DO {SQL COMMAND};
![Page 39: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/39.jpg)
Event creation syntaxCREATE EVENT event_nameON SCHEDULE {schedule clause}
[ON COMPLETION [NOT] PRESERVE][STARTS {DATE TIME}][ENDS {DATE TIME} ][ENABLE|DISABLE]
DO {SQL COMMAND};
![Page 40: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/40.jpg)
Creating an event at a given timeCREATE EVENT event_nameON SCHEDULE AT '2009-04-21 15:55:00'DO INSERT INTO some_table VALUES ('gotcha', now());
CREATE EVENT event_nameON SCHEDULE AT now() + interval 20 minuteDOCALL smart_procedure()
![Page 41: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/41.jpg)
Creating a recurring eventCREATE EVENT event_nameON SCHEDULE EVERY 20 MINUTEDO INSERT INTO some_table VALUES ('gotcha', now());
CREATE EVENT event_nameON SCHEDULE every 7 DAYDOCALL smart_procedure()
![Page 42: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/42.jpg)
Creating a recurring eventCREATE EVENT event_nameON SCHEDULE EVERY 10 MINUTESTARTS NOW() + INTERVAL 2 HOURENDS NOW() + INTERVAL 4 HOURDO CALL some_procedure();
# creates an event that runs every# 10 minutes, but does not start now.# It will start in 2 hours# and end two hours later
![Page 43: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/43.jpg)
MySQL 5.1 and 5.4in practice
Giuseppe Maxia
MySQL Community Team Lead
![Page 44: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/44.jpg)
5.4 ?This release is very special for
two reasons
![Page 45: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/45.jpg)
Tight team work
1MySQL architects, top MySQL coders, Sun
performance engineers, all worked together to
create this release
![Page 46: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/46.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 47: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/47.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 48: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/48.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 49: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/49.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 50: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/50.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 51: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/51.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 52: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/52.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 53: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/53.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 54: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/54.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 55: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/55.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 56: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/56.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 57: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/57.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 58: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/58.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 59: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/59.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 60: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/60.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 61: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/61.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 62: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/62.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 63: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/63.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 64: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/64.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 65: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/65.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 66: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/66.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 67: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/67.jpg)
How to improve performancemethod efficacy difficulty
Schema optimization ***** #####Server tuning ** #### Query tuning *** ####Hardware upgrade *** # Replication ** #####Partitioning ***** #####Server Upgrade (5.4) ***** #
2
![Page 68: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/68.jpg)
5.4 = 5.1 +
performance patches
![Page 69: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/69.jpg)
Practical experience
![Page 70: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/70.jpg)
sysbench preparetime sysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --num-threads=8 prepare
![Page 71: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/71.jpg)
sysbench r/osysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --max-time=60 \ --oltp-read-only=on \ --max-requests=0 \ --num-threads=8 run
![Page 72: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/72.jpg)
sysbench r/wsysbench \ --test=oltp \ --oltp-table-size=1000000 \ --mysql-db=test \ --mysql-user=msandbox \ --mysql-password=msandbox \ --mysql-host=127.0.0.1 \ --mysql-port=$PORT \ --max-time=60 \ --oltp-read-only=off \ --max-requests=0 \ --num-threads=8 run
![Page 73: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/73.jpg)
sysbench resultsMySQL 5.0 read-only
![Page 74: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/74.jpg)
sysbench resultsMySQL 5.1 read-only
![Page 75: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/75.jpg)
sysbench resultsMySQL 5.0 R/W
![Page 76: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/76.jpg)
sysbench resultsMySQL 5.1 R/W
![Page 77: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/77.jpg)
sysbench resultsMySQL 5.4 R/O
![Page 78: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/78.jpg)
sysbench resultsMySQL 5.4 R/W
![Page 79: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/79.jpg)
sysbench results graphMySQL 5.0 read-only
![Page 80: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/80.jpg)
sysbench results graphMySQL 5.1 R/O
![Page 81: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/81.jpg)
sysbench results graph - read only
5.0
5.1
![Page 82: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/82.jpg)
sysbench results graphMySQL 5.0 R/W
![Page 83: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/83.jpg)
sysbench results graphMySQL 5.1 R/W
![Page 84: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/84.jpg)
sysbench results graph - R/W
5.0
5.1
![Page 85: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/85.jpg)
sysbench results graphMySQL 5.4 R/O
![Page 86: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/86.jpg)
sysbench results graph - read only
5.0
5.1/5.4
![Page 87: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/87.jpg)
sysbench results graph - read only
![Page 88: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/88.jpg)
sysbench results graphMySQL 5.4 R/W
![Page 89: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/89.jpg)
sysbench results graph - R/W
5.0
5.1/5.4
![Page 90: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/90.jpg)
sysbench results graph - R/W
![Page 91: MySQL 5.1 and beyond](https://reader033.vdocuments.us/reader033/viewer/2022051610/5495b17bb479597e6a8b6092/html5/thumbnails/91.jpg)
QUESTIONS?
Slides in my bloghttp://datacharmer.blogspot.com
THANKS