#《High Performance MySQL》

#Highlights

  • Page 1 (MySQL’s Logical Architecture):

    Any functionality provided across storage engines lives at this level: stored procedures, triggers, and views, for example.

  • Page 2 (Connection Management and Security):

    This API hides differences between storage engines and makes them largely transparent at the query layer.

  • Page 5 (Lock Granularity):

    Its storage engines can implement their own locking policies and lock granularities.

  • Page 6 (Transactions):

    Row locks are implemented in the storage engine, not the server.

  • Page 8 (Isolation Levels): “dirty read”

  • Page 8 (Isolation Levels): “nonrepeatable read”

  • Page 8 (Isolation Levels): “phantom reads”

  • Page 9 (Deadlocks):

    Deadlocks occur when transactions try to lock resources in a different order.

  • Page 10 (Transaction Logging):

    The way InnoDB currently handles deadlocks is to roll back the transaction that has the fewest exclusive row locks

  • Page 10 (Transaction Logging): “Lock behavior and order are storage engine specific”

  • Page 11 (Transactions in MySQL):

    By default, a single INSERT, UPDATE, or DELETE statement is implicitly wrapped in a transaction and committed immediately

  • Page 11 (Transactions in MySQL):

    Certain commands, when issued during an open transaction, cause MySQL to com‐ mit the transaction before they execute.

  • Page 12 (Transactions in MySQL):

    if a rollback is required, the changes to the nontransactional table can’t be undone.

  • Page 13 (Transactions in MySQL):

    MVCC works by using snapshots of the data as it existed at some point in time.

  • Page 13 (Transactions in MySQL):

    InnoDB implements MVCC by assigning a transaction ID for each transaction that starts. That ID is assigned the first time the transaction reads any data.

  • Page 14 (Multiversion Concurrency Control):

    the undo log record is followed and applied until the session reaches a transaction ID that is eligible to be visible.

  • Page 14 (Multiversion Concurrency Control):

    Records in a transaction are deleted by setting a “deleted” bit in the “info flags” of the record.

  • Page 16 (The InnoDB Engine):

    InnoDB locks gaps in the index structure as well, preventing phantoms from being inserted.

  • Page 17 (Data Dictionary Changes):

    You should strive for a small primary key if you’ll have many indexes on a table.

  • Page 17 (Data Dictionary Changes):

    removing file-based table metadata storage and moving to a data dictionary using InnoDB table storage.

  • Page 20 (Defining Service Level Goals):

    Our measure should focus on outcomes, not outputs."

  • Page 22 (What Does It Take to Make Customers Happy?): “engineering time is a finite resource”

  • Page 23 (Defining SLIs and SLOs):

    detecting when a data set becomes a bottleneck for very different query profiles by different stakeholders, jeopardizing performance. This also means finding a way to separate those different stakeholder needs so that you can provide them reasonable SLIs and SLOs.

  • Page 23 (Defining SLIs and SLOs): “availability, latency, and lack of critical errors.”

  • Page 26 (Monitoring Availability): “mean time to recovery (MTTR)”

  • Page 31 (Proactive Monitoring):

    A useful thing to consider when setting up monitoring for connection count is rely‐ ing on percentages and not absolute numbers.

  • Page 31 (Proactive Monitoring):

    The delay between data being written on the source and being available on the replicas is referred to as replication lag.

  • Page 32 (Proactive Monitoring):

    Replication lag is one of those metrics that can affect both immediate and tactical decisions, but also keeping an eye on its trends long term can help save you the hassle of larger business impact and keep you ahead of the growth curve.

  • Page 33 (Proactive Monitoring):

    IOutil is reported as a percentage of the overall system’s disk access capacity.

  • Page 33 (Proactive Monitoring):

    monitoring remaining integer space for any tables that use auto increments as the primary key

  • Page 35 (Proactive Monitoring):

    Monitor how long it takes to restore a backup from a file to a running database

  • Page 35 (Proactive Monitoring):

    if you consider sharding when you have incidents where capacity issues are a major contributing cause, then you likely have considered it too late

  • Page 36 (Learning Your Business Cadence):

    It is important to be aware of the traffic cadence of your business

  • Page 37 (Using Monitoring Tools to Inspect the Performance): “high-signal metrics for long-term trends”

  • Page 39 (Summary):

    spend some time on moni‐ toring things that can help you prevent incidents.

  • Page 42 (Instrument Elements): “performance_schema encapsulates each examined call into two macros,”

  • Page 45 (Resource Consumption):

    this memory is never freed once allocated, even if you disabled specific instrumentation and truncated the table.

  • Page 50 (Enabling and Disabling Consumers):

    If multiple options are specified, the longer instrument string has precedence over the shorter regardless of the order.

  • Page 55 (Examining SQL Statements): “CREATED_TMP_DISK_TABLES”

  • Page 75 (What Limits MySQL’s Performance?):

    CPU saturation can happen when MySQL tries to execute too many queries in parallel or when a smaller number of queries runs for too long on the CPU.

  • Page 77 (What’s Your Working Set?): “a write-ahead logging strategy.”

  • Page 77 (What’s Your Working Set?):

    When dealing with HDDs, it was good practice to try to find an effective memory-todisk ratio

  • Page 78 (An Overview of Flash Memory):

    The most important things are improvements in random I/O and concurrency. Flash memory gives you very good random I/O performance at high concurrency.

  • Page 79 (RAID Performance Optimization):

    many devices get slower as they fill up. How much slower is different for every vendor and model and depends on the device’s architecture.

  • Page 79 (RAID Performance Optimization):

    A write to a free block takes a couple of hundred microseconds, but an erase is much slower—typically a few milliseconds.

  • Page 89 (Choosing a Disk Queue Scheduler): “We usually recommend using the XFS filesystem.”

  • Page 89 (Choosing a Disk Queue Scheduler):

    On GNU/Linux, the queue scheduler determines the order in which requests to a block device are actually sent to the underlying device.

  • Page 90 (Memory and Swapping):

    We like the si and so column values to be 0, and they should definitely be less than 10 blocks per second.

  • Page 91 (Memory and Swapping):

    We recommend you run your databases without using swap space at all.

  • Page 99 (Chapter 5. Optimizing Server Settings):

    It’s best to start with an understanding of MySQL’s internals and behavior.

  • Page 99 (Chapter 5. Optimizing Server Settings):

    After you’ve set MySQL’s basic configuration options correctly, the potential gains from further changes are usually small.

#Detailed comments

  • Page 14 (Multiversion Concurrency Control): 查询时依赖索引，如果索引在事务内改变，如何不影响查询？

  • Page 61 (Examining SQL Statements): 看到这暂时跳过这一章。。。