Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

InnoDB Scalability Limits

VIEWS: 9 PAGES: 31

									InnoDB Scalability Limits
              Peter Zaitsev, Vadim
              Tkachenko
              Percona Inc
              MySQL Users Conference
              2008
              April 14-17, 2008
                                                       -2-



       Who are the Speakers ?
• Founders of Percona Inc
  – MySQL Performance and Scaling consulting company
• Active MySQL Community Members
• Writers http://www.mysqlperformanceblog.com
• Co-Authors of High Performance MySQL Second
  Edition
                          What we'll talk about
• MySQL's main transactional storage engine InnoDB
• Looking into InnoDB Scalability Limits
      – And how to solve them (or work around them)
• Check into other aspects affecting InnoDB
  Performance
• Focus on existing codebase in 5.0 and 5.1
• Mark Callaghan may already have fixed some of the
  issues
• Provide benchmark results to support most of our
  claims.
InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                             Kinds of Scalability
• Scalability means different things to different people
• Upwards and Downwards in terms of hardware
      – We're not really interested in the downwards part
• We look at Scaling application first
      – And what InnoDB needs to have in order to support it
• This comes down to a number of InnoDB scaling
  primitives.




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                    Scalability for your Boss
• For Product Manager scalability means Application
  can Perform as it Grows.
      – What does Perform mean ?
      – What does Grow mean ?
• And typically these demands come with budget
  constraints




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
               What does Performs mean
• Response time is “Decent”
      – No pages take 30 seconds to load
      – Easy to measure in production
• There is enough system capacity
      – System can take the load without degrading in response
        time and failing
      – Often estimated (benchmarked)
• Operations can manage the system
      – You can perform backups, table maintenance etc



InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
      Application Growth Parameters
• Load (think spikes)
      – Growing from 100 q/sec to 1000
• Database Size
      – Going from 10GB to 100GB data size
• Data Distribution
      – Going from 10 friends in average to 250
• Launching new features or changes to the old one
      – Full text search feature can be heavy add on




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
         Application Scaling Challenge
• As application grows all parameters tend to grow at
  once
      – And you have to deal with larger number of more complex
        queries on large database size.
• To maintain performance in these conditions you
      – Optimize “application”
             • Schema, Queries, Caching, Sharding, Replication
      – Get more hardware
• We focus on Scaling by Platform Upgrades in this
  presentation

InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
        What do we mean by Platform
• Hardware
• Operating System
• MySQL Server




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
          Testing by Micro Benchmarks
• Micro Benchmarks correspond to some simple
  operations.
• Tend to stress some particular aspect of behavior
• If problem is seen in micro benchmarks it is seen in
  some applications
      – Though applications tend to expose wider range of scaling
        problems
• Micro benchmarks may take things to extreme
      – Showing issue at larger scale than application would have



InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                              Operational Issues
• Often forgotten about
• Descriptive nature and does not need to be
  benchmarked




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                                         Large Tables
• Large Tables (and instances are hard to deal)
• Backup – physical backup is must.
• Can't move separate tables between servers
  physical way
• No REPAIR functionality
      – Corruption often means dump and restore
      – Restore from physical backup is often faster
• ALTER TABLE is very slow
      – Master-Master replication can help
• Table maintenance OPTIMIZE TABLE
InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
     Online Index creation in InnoDB
• New plugin just announced today by Ken and Heikki
      – We had early access to the code
• InnoDB can now build indexes without rebuilding
  whole table
      – Index build done by sort which is much faster
• 10 times faster load and better index sizes
      – But only if you load data and add indexes separately
                      Load Method             Load Time Data Size        Index Size
                      SQL Dump                88m             1333788672       1867513856
                      LOAD INFILE             90m             1333788672       1867513856
                      ALTER from MYISAM       90m             1333788672       1867513856
                      LOAD + ADD INDEX        3m+5m           1333788672       1124073472
                      SQL Dump MyISAM         3m              1050000000        312579072


InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
         How good are Sorted Indexes
• Indexes built by sort can be better physically sorted
  and have better fill factor.
• Full Index Scan speed (cold)
      – 31 sec standard vs 22 sec built by sorting
             • Becomes CPU bound at this stage, could be even better
• Update:
      – update sample set c=md5(i) where i%1000=1;
             • 3 min 20 sec standard vs 8 min 16 sec sorted
      – Index size growth:
             • 0% (standard) vs 30% (sorted)


InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                                          Many tables
• You may be escaping from large tables by creating
  many small tables instead
      – InnoDB keeps all table it accessed open
             • Can consume a lot of memory (reduced in 5.1)
             • Lesser issue with modern 64bit platforms
      – innodb_file_per_table=1
             • Can get small tables to use more space
             • Crash recovery is a problem with many tables.
      – “Warmup” is a problem
             • Only one table can be opened at a time (5.0)
             • Stats update on open makes it quite slow


InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                       Large Buffer Pool Size
• In general the more memory you can get for buffer
  pool the better it is.
• Watch out for Warmup
      – Larger buffer pool means longer warmup time
      – 32GB Buffer pool will take an hour to fill
             • reading 600 pages/sec
• SHOW STATUS and SHOW INNODB STATUS gets
  expensive
      – It takes a global lock to count dirty pages



InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                                Large Buffer Pool
• Clean shutdown time may be long
      – Buffer Pool needs to be flushed
      – Set innodb_max_dirty_pages_pct=0 in advance
• Checkpointing activity may cause uneven
  performance
      – The “dip” is often longer and deeper with large buffer pool




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                              Replication Speed
• Replication can get behind much earlier when
  Master or Slave are saturated
      – Becoming more problem with Multi-Core CPUs
      – Or if you do not have RAID with BBU on the slave
• Limited by Transaction Commit speed
      – Set innodb_flush_log_at_trx_commit=2
             • Replication is Asynchronous anyway
• Limited by Update execution Speed
      – Disk – Prefetching may help
      – CPU – Check row level replication in 5.1+

InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
               Lets do some Benchmarks




                                 Take Results with grain of salt




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                    Workload Scalability 5.0
                                                  •    Scaling factor for different number of threads
• MySQL 5.0.51a                                            7



• Dell PE 2950                                             6
                                                                                           5.95




• 2* Quad Core CPUs                                        5                                                 4.73



      – Intel Xeon L5335
                                                                                           4.29              4.25     4.21

                                                           4                               3.81              3.8       3.8
                                                                                                                      3.63
                                                                           3.53


• CPU Bound                                                3               2.85
                                                                           2.66
                                                                           2.51


• Scaling depends on
                                                                                           2.42
                                                                            2.2

                                                                                           1.87
                                                           2


  workload a lot
                                                                                                             1.56

                                                                                                                      1.19
                                                                                                             1.1      1.09
                                                               1
                                                           1



                                                           0
                                                               1            4              16                64       256

                                                                          KEY_RANGE   KEY_POINT_IND   PK_POINT_INDX
                                                                          FTS         KEY_POINT




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                    Workload Scalability 5.1
                                                  •    Scaling factor for different number of threads
• MySQL 5.1.23-rc                                          6



• Everything same but                                      5
                                                                                           5.26




  MySQL Version                                                                                              4.19
                                                                                           3.92              3.92     3.91


• We can see serious
                                                           4


                                                                                                                      3.24


  regressions for some                                     3
                                                                           3.02

                                                                           2.73




  workloads
                                                                           2.38
                                                                           2.19

                                                           2               1.87


                                                                                           1.43              1.44     1.38
                                                                                           1.28

                                                               1                           1.04
                                                           1                                                 0.88
                                                                                                             0.73


                                                                                                                       0.3
                                                                                                                      0.24


                                                           0
                                                               1            4              16                64       256

                                                                          KEY_RANGE   KEY_POINT_IND   PK_POINT_INDX
                                                                          FTS         KEY_POINT




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
               Single thread performance
                                                  •    Relative Performance of MySQL versions
• Performance for one                                      1.2


  thread                                                          1                    1    1             1   0.99
                                                                                                                     1.04
                                                                                                                            1   0.99          1   0.99
                                                            1         0.97


• Scaling factor alone is
                                                                                                                                       0.96
                                                                                                                                                         0.94
                                                                             0.9

                                                                                                 0.82


  not conclusive                                           0.8




• Actual results too                                       0.6



  different – use ratios to                                0.4

  5.0.22 instead
                                                           0.2

• 5.0.22 and 5.0.51 are
  very close                                                0
                                                                 KEY_RANGE          KEY_POINT_IND PK_POINT_INDX                 FTS           KEY_POINT



• 5.1 shows some                                                                   5.0.22       5.0.51a   5.1.23




  regressions
InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
             MySQL Versions Scalability
                                                  •    Scaling factor for different MySQL versions
• Scaling factor for 64                                     5


  threads
                                                                                                                    4.72

                                                                                                             4.48
                                                           4.5
                                                                                               4.23                        4.19



• MySQL 5.0.51 overall
                                                                                                                                                3.93
                                                            4                                                                            3.78




  best results
                                                           3.5


                                                            3


• Good improvements                                        2.5                          2.42
                                                                                                                                  2.49




  from 5.0.22                                               2
                                                                       1.61
                                                                                                      1.44
                                                           1.5

• 5.1 shows serious                                         1                 0.88
                                                                                                                                                              1.14


                                                                                                                                                                     0.73


  regressions                                              0.5
                                                                 0.2                                                                                   0.18

                                                            0
                                                                 KEY_RANGE            KEY_POINT_IND PK_POINT_INDX                        FTS           KEY_POINT

                                                                                     5.0.22       5.0.51a    5.1.23




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
             innodb_thread_concurrency
                                                  •    innodb_thread_concurrency affects performance
• Performance for 64                                       2.5


  threads                                                             2.07 2.06
                                                                                                                                                    2.21


                                                                                                                                                           2.03


• MySQL 5.1.23                                              2




• Using                                                    1.5


  innodb_thread_concurre                                                                                           1.05


  ncy=0 as baseline
                                                                  1                   1                 1      1          1            0.98     1
                                                            1

                                                                                                                                0.7



• No perfect value –                                       0.5
                                                                                          0.53
                                                                                                 0.49




  different workloads
  behave differently                                        0
                                                                 KEY_RANGE         KEY_POINT_IND PK_POINT_INDX                  FTS            KEY_POINT

                                                                                  innodb_thread_con         innodb_thread_con         innodb_thread_con
                                                                                  currency=0                currency=4                currency=8




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
          Fixing scaling by CPU Affinity
                                                  •    How binding to CPUs affects performance
• Performance for 16                                        4


  threads                                                  3.5
                                                                                             3.63




• MySQL 5.1.23                                              3
                                                                                                                                                                                                            3.01


                                                                                                                                                                                         2.65

      – 5.0.51a for comparison                             2.5
                                                                                                                                                                       2.27
                                                                                                                                                                                  2.39




• Workloads which scaled                                    2
                                                                                                        1.72 1.72
                                                                                                                    1.89          1.93

                                                                                                                                                                                                1.68 1.68


  worse on 5.1.23                                          1.5
                                                                          1.16
                                                                                                                           1.28

                                                                                                                                                    1.04


• Trying to bind MySQL to
                                                                 1 0.97          0.97 0.99          1                                    1                                    1
                                                            1                                                                                0.91


                                                                                                                                                           0.61 0.61



  specific CPU Cores                                       0.5




• Restricting can help
                                                            0
                                                                 KEY_POINT_INDX                         KEY_POINT                        KEY_RANGE_INDX                           KEY_RANGE



  scaling                                                                                4x4        2x2         4x0                1x1       2x0            5.0.51




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                     Quadcore vs Dual Core
                                                  •    Scaling factor for different number of threads
• MySQL 5.1.23rc                                           3.5



• Worst scaling patterns                                    3                                  2.89
                                                                                               2.78
                                                                                                                    2.94
                                                                                                                           2.84
                                                                            2.72                                    2.73


• For 2 out of 3 query                                                      2.61                                           2.63

                                                                            2.44
                                                           2.5


  patterns Dual core                                        2
                                                                            2.19
                                                                            2.07

                                                                            1.87
                                                                                               2.16


                                                                                               1.89
                                                                                                                     2




  system scales and                                                                                                 1.75
                                                                                                                           1.68

                                                                                                                           1.45


  performs much better
                                                           1.5
                                                                                               1.28


                                                                 1                             1.04
                                                            1                                                       0.88
                                                                                                                    0.73



                                                           0.5
                                                                                                                            0.3
                                                                                                                           0.24



                                                            0
                                                                 1           4                 16                   64     256

                                                                          POINT 8    POINT 4          RANGE 8
                                                                          RANGE 8    RANGE_IDX 8      RANGE_IDX 4




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                                         Large Pages
                                                  •    Performance effect of using Large Pages
• MySQL can use Large                                      1.4


  Pages for InnoDB Buffer                                  1.2
                                                                  1.2



  Pool                                                                  1.08

                                                                                        1
                                                                                            1.02
                                                                                                   1
                                                                                                            1.05
                                                                                                                   1    1
                                                                                                                            1.05
                                                                                                                                   1    1
                                                            1                  0.96


• Huge Pages reduce TLB
  cache misses
                                                           0.8




• Non Swappable
                                                           0.6



                                                           0.4

• Mediocre results for this
  workload, may be better                                  0.2




  in case of skewed                                         0
                                                                         1                   4                     16              64


  working set                                                                    FTS   KEY_RANGE       POINT_INDEX




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                         InnoDB IO Scalability
                     •                                     Performance effect of using Large Pages
• Dell PowerEdge                                           160


  2950, Perc6, CentOS                                      140
                                                                                                          139




  5.0                                                      120


• RAID1 vs RAID10 (6                                       100


  disk)                                                     80



• SysBench MySQL                                                                            62
                                                            60



  Test workload                                             40
                                                                                       42




                                                                        21
                                                            20



                                                             0
                                                                              RAID1              RAID10

                                                                             RW   RO




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                                     EXT3 vs EXT2
                       •                                   Performance effect of using Large Pages
• Same Hardware                                            160



• RAID10                                                   140
                                                                                       129
                                                                                                         139




• Ext3 does worse on                                       120



  writes (journaling                                       100



  overhead) but better                                      80
                                                                        69



  with reads                                                60
                                                                                             62




                                                            40



                                                            20



                                                             0
                                                                              EXT2                EXT3

                                                                             RW   RO




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                           Linux IO Schedulers
                                                      •    Performance effect of using Large Pages
• Elevators improved                                       160


      – Difference is not as                               140
                                                                              139
                                                                                                                              137



        huge as years ago                                  120                                     116
                                                                                                                   112


• CFQ (default) is best                                    100


  for this workload on                                      80


  this box                                                  60
                                                                   62                         63
                                                                                                         56
                                                                                                                         60




                                                            40



                                                            20



                                                             0
                                                                        CFQ                   DEADLINE        AS          NOOP

                                                                                    RW   RO




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008
                             Thanks for Coming
• Time for Questions
• Write us
      – pz@percona.com vadim@percona.com
• Come and visit us
      – For Information: http://www.mysqlperformanceblog.com
      – For Business: http://www.percona.com




InnoDB Scalability Limits, MySQL Users Conference, Santa Clara,CA April 14-17 2008

								
To top