O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Instant InnoDB

Book Description

A quick reference guide to walk you through the setup of InnoDB, and help you start unlocking the engine's potential

  • Detailed and in-depth explanation of configuration and tuning parameters, in addition to troubleshooting and performance analysis methods
  • Learn how to optimize InnoDB with static and dynamic configuration parameters
  • Learn how to measure, analyse, and load test InnoDB workloads

In Detail

InnoDB, the default storage engine for MySQL, is one of the most robust and commonly used in the world today. It can, however, be initially daunting to learn how to start implementing its features, and it can pose unique problems to the beginner user. This guide is written to address these problems.

"Instant InnoDB" addresses everything you need to know for the installation, configuration, load testing, analysis, and long term support of an InnoDB based database environment. If you've ever wanted to learn more about InnoDB but didn't want to sit down with a formidably large or dry read, then the Instant InnoDB is your ideal companion.

"Instant InnoDB" guides you expertly through the initial setup and system requirements for getting up and running with a simple environment that can be used for the development and testing of the InnoDB engine.

After an in-depth look at basic and advanced configuration settings we delve into the internals of InnoDB that allow it to handle Multi-Version Concurrency Control and ACID transaction compliance. We will then dig deeper into advanced topics, such as memory buffer tuning methods and transaction log file handling. We'll then cover typical approaches and solutions to monitoring the database and reporting on operational states.

By the end of the Instant InnoDB, we will have covered the common approaches to troubleshooting and error handling, as well as providing you with solutions to the most common architectural, administrative, and engineering related situations that arise when working with the InnoDB database engine.

Table of Contents

  1. Instant InnoDB
    1. Table of Contents
    2. Instant InnoDB
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
        1. Why Subscribe?
        2. Free Access for Packt account holders
        3. Instant Updates on New Packt Books
    7. Preface
      1. What this book covers
      2. What you need for this book
      3. Who this book is for
      4. Conventions
      5. Reader feedback
        1. Customer support
        2. Errata
        3. Piracy
        4. Questions
    8. 1. Getting Started with InnoDB
      1. Basic features of InnoDB
        1. Referential integrity
        2. Multiversion concurrency control
        3. Transaction isolation
        4. ACID compliance
        5. Locking characteristics
      2. System requirements and supported platforms
      3. Downloading MySQL with InnoDB
        1. Installing MySQL with InnoDB support
        2. Verifying InnoDB support and transaction settings
          1. Atomicity
          2. Consistency
          3. Isolation
          4. Durability
        3. Setting the transaction isolation level for InnoDB
      4. Creating your first InnoDB table
      5. Summary
    9. 2. Basic Configuration Parameters
      1. MySQL memory allocation equations
      2. InnoDB memory and data file configuration
        1. Static variables
          1. innodb_data_home_dir
          2. innodb_data_file_path
          3. innodb_log_group_home_dir
          4. innodb_log_file_size
          5. innodb_log_buffer_size
          6. innodb_log_files_in_group
          7. innodb_buffer_pool_size
          8. innodb_status_file
          9. transaction-isolation
          10. skip-innodb
      3. Summary
    10. 3. Advanced Configuration Parameters
      1. InnoDB and input/output resources
        1. SysBench
        2. Fio
        3. Bonnie++
      2. Disk speed: standard IOPS
      3. InnoDB variables
        1. Dynamic InnoDB variables
          1. innodb_table_locks
          2. innodb_flush_log_at_trx_commit
          3. innodb_io_capacity
          4. innodb_thread_concurrency
          5. innodb_commit_concurrency
          6. innodb_support_xa
          7. innodb_fast_shutdown
        2. Static InnoDB variables
          1. innodb_file_per_table
          2. innodb_additional_mem_pool_size
          3. innodb_buffer_pool_instances
          4. innodb_write_io_threads
          5. innodb_read_io_threads
          6. skip-innodb-doublewrite
      4. Summary
    11. 4. Load Testing InnoDB for Performance
      1. Open source system monitoring tools
      2. Open source MySQL load testing applications
        1. Log replay
        2. SysBench
        3. Quadrant framework
        4. OLTPbenchmark
      3. MySQL Benchmark Suite
        1. MySQLslap
      4. Filesystem benchmarking
        1. hdparm
        2. Bonnie++
        3. Fio
      5. InnoDB Flush methods
        1. InnoDB Flush method comparison chart
      6. Thread concurrency testing
      7. Advisory on transaction-isolation level
      8. Summary
    12. 5. Maintenance and Monitoring
      1. Maintenance procedures
        1. Adding and resizing InnoDB log files
        2. Adding tablespaces to InnoDB
        3. Utilizing RAW tablespaces
      2. Monitoring InnoDB
        1. Monitoring via show table status
        2. Querying INFORMATION_SCHEMA
        3. Global status and global variables
        4. Third-party resources for trending and visualization
        5. Equations for analyzing InnoDB statistics
      3. Summary
    13. 6. Troubleshooting InnoDB
      1. Troubleshooting system issues
        1. InnoDB system crashes
        2. Using InnoDB crash recovery modes
          1. Enabling InnoDB recovery modes
        3. Utilizing InnoDB status statistics
      2. Troubleshooting InnoDB performance
        1. I/O resources
        2. Table-space and logging
        3. Memory resources
        4. Concurrency settings
      3. Global status variables – trending statistics
        1. InnoDB performance status variables
        2. InnoDB pending operation status variables
      4. Get to grips with InnoDB error codes
        1. 1005 (ER_CANT_CREATE_TABLE)
        2. 1016 (ER_CANT_OPEN_FILE)
        3. 1114 (ER_RECORD_FILE_FULL)
        4. 1205 (ER_LOCK_WAIT_TIMEOUT)
        5. 1206 (ER_LOCK_TABLE_FULL)
        6. 1213 (ER_LOCK_DEADLOCK)
        7. 1216 (ER_NO_REFERENCED_ROW)
        8. 1217 (ER_ROW_IS_REFERENCED)
      5. InnoDB troubleshooting and backup applications
      6. Summary
    14. 7. References and Links
      1. General documentation
      2. Reference books
        1. Terminology
          1. InnoDB tuning references
    15. Index