Using the InnoDB Buffer Pool Pre-Load Feature in MySQL 5.7
In this blog post, I’ll discuss how to use the InnoDB buffer pool pre-load feature in MySQL 5.7 Starting MySQL 5.6, you can configure MySQL to save the contents of your InnoDB buffer pool and load it on startup. Starting in MySQL 5.7, this is the default behavior. Without any special effort, MySQL saves and restores a portion of buffer pool in the default configuration. We made a similar feature available in Percona Server 5.5 – so the concept has been around for quite a while.
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Copying the MySQL data files from one MySQL instance to another MySQL instance is too difficult job. Because MyISAM table engine table will imported easily without any issues. But InnoDB engine tables create in-sync issues, because the InnoDB tables keeps their table structure in .frm file and metadata in .ibd file. So you can’t just move .ibd files to a new MySQL instance. This will create serious pain when moving InnoDB MYSQL data from one MySQL instance to another MySQL instance.
The databases were benchmarked for speed and also for the space consumed by the tpcc-mysql dataset generated with 20 warehouses. So lets first have a look at how much space was needed by TokuDB vs. InnoDB (using both compressed and uncompressed tables)...
In MySQL, when you are using InnoDB, all the tables and indexes are stored under the MySQL system tablespace. In this tutorial, we’ll explain how to rebuild your entire MySQL database, and break the big MySQL system tablespace file into small individual MySQL table files.
MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files. MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key to decrypt the tablespace key and then uses it to encrypt (on write) and decrypt (on read).
This blog post will discuss what the Adaptive Hash Index in InnoDB is used for, and whether it is a good fit for your workload. Adaptive Hash Index (AHI) is one of the least understood features in InnoDB. In theory, it magically determines when it is worth supplementing InnoDB B-Tree-based indexes with fast hash lookup tables and then builds them automatically without a prompt from the user...
MySQL 5.7 offers new scalability features that should have you eager to make the change. To highlight one of the changes, scalability has been greatly improved. The new peak performance for a MySQL server is over 640K queries per second, and the memcached API, which speaks directly to the InnoDB storage engine, is capable of sustaining over 1.1 million requests per second.
This blog discusses the gradual end of MyISAM in MySQL. The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.
One of the things we have been working on for MySQL 8, is speeding up scans that read multiple records from a table or an index. Such scans benefit from reading the records in batches, so that they don’t get the overhead of latching data pages and navigating in the B-tree for every single record that is read. When the MySQL server asks the storage engine for a record, the storage engine may therefore choose to prefetch a batch of records into a buffer, so that it can quickly return a record from the buffer when the server asks for the next record.
In my last post, “A closer look at the MySQL ibdata1 disk space issue and big tables,” I looked at the growing ibdata1 problem under the perspective of having big tables residing inside the so-called shared tablespace. In the particular case that motivated that post, we had a customer running out of disk space in his server who was looking for a way to make the ibdata1 file shrink. As you may know, that file (or, as explained there, the set of ibdata files composing the shared tablespace) stores all InnoDB tables created when innodb_file_per_table is disabled, but also other InnoDB structures, such as undo logs and data dictionary.
In this blog post, we’ll discuss fixing MySQL scalability problems using either ProxySQL or thread pool. In the previous post I showed that even MySQL 5.7 in read-write workloads is not able to maintain throughput. Oracle’s recommendation to play black magic with innodb_thread_concurrency and innodb_spin_wait_delay doesn’t always help. We need a different solution to deal with this scaling problem.