From the lack of comments to my previous post it seems everything is clear with ERROR 1213 in different kinds and forks of MySQL. I may still write a post of two about MyRocks or TokuDB deadlocks one day, but let's get back to my main topic of MySQL bugs. Today I continue my series of posts about community bug reports I am subscribed to with a review of bugs reported in November, 2018, starting from the oldest and skipping those MySQL 8 regression ones I've already commented on. I also skip documentation bugs that should be a topic for a separate post one day (to give more illustration to …[Read more]
Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate …[Read more]
Probably all of us, MySQL users, DBAs and developers had seen
error 1213 more than once, in one context or the other:
mysql> select * from t1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionThe first thing that comes to mind in this case is: "OK, we have InnoDB deadlock, let's check the details", followed by the SHOW ENGINE INNODB STATUS check, like this:
mysql> show engine innodb status\G
*************************** 1. row ***************************
2018-12-08 17:41:11 0x7f2f8b8db700 INNODB MONITOR OUTPUT
Per second averages calculated from the last 12 seconds
… or what I should keep in mind in case of disaster
To retrieve and maintain in SQL format the definition of all tables in a database, is a best practice that we all should adopt. To have that under version control is also another best practice to keep in mind.
While doing that may seem redundant, it can become a life saver in several situations. From the need to review what has historically changed in a table, to knowing who changed what and why… to when you need to recover your data and have your beloved MySQL instance not start…
But let’s be honest, only a few do the right thing, and even fewer keep that information up to date. Given that’s the case, what can we do when we have the need to discover/recover the table structure?
From the beginning, MySQL has used some external files to describe its internal structure.
For instance, if I have a schema named windmills and a table …[Read more]
Partitioning is a way of splitting the actual data down into separate .ibd files (data compartments) in the file system based on the defined ranges using the partitioning key. It can help us with maintaining the enormous amount of data in different partitions without much hassle.
In this blog post, we are going to see how to manage table partitioning using yahoo partition manager.
Needs for partitioning:
- Enhanced data retrieval ( reduced IO ) with smaller B+Tree.
- Easy Archival or Purge by dropping or truncate of partition
- Lesser fragmentation, hence avoiding frequent table optimization.
Partitions management activity like adding/Dropping partition is made easy and automated by …[Read more]
It's time to continue my review of MySQL bug reports that I
considered interesting for some reason recently. I had not got
any notable reaction from Oracle engineers to my previous post about recent regression bugs in
MySQL 8.0.13, so probably this topic is not really that hot. In
this boring post I'll just review some bugs I've subscribed to
since August that are still not closed,
starting from the oldest.
Let me start with a couple of bug reports that remain "Open":
- Bug #91959 - "UBSAN: signed integer overflow in lock_update_trx_age". It's really unusual to see bug reported by …
Over the last year, I have been pursuing a part time hobby project exploring ways to squeeze as much data as possible in MySQL. As you will see, there are quite a few different ways. Of course things like compression ratio matters a lot but, other items like performance of inserts, selects and updates, along with the total amount of bytes written are also important. When you start combining all the possibilities, you end up with a large set of compression options and, of course, I am surely missing a ton. This project has been a great learning opportunity and I hope you’ll enjoy reading about my results. Given the volume of results, I’ll have to write a series of posts. This post is the first of the series. I also have to mention that some of my work overlaps work done by one of my colleague, Yura Sorokin, in a …[Read more]
Schema change is one of the crucial tasks in MySQL with huge tables. Schema change can cause locks.
What is gh-ost?
gh-ost is a triggerless online schema change for MySQL by Github Engineering .It produces light workload on the master during the schema changes . We need online schema change to alter a table without downtime (locking) in production.pt-online schema change is the most widely used tool for making changes in the tables.gh-ost is just an alternative to pt-online schema change.
Why we have to use gh-ost?
Very often, database performance is affected by the inability to cache all the required data in memory. Disk IO, even when using the fastest devices, takes much more time than a memory access. With MySQL/InnoDB, the main memory cache is the InnoDB buffer pool. There are many strategies we can try to fit as much data as possible in the buffer pool, and one of them is data compression.
With regular MySQL, to compress InnoDB data you can either use “Barracuda page compression” or “transparent page compression with punch holes”. The use of the ZFS filesystem is another possibility, but it is external to MySQL and doesn’t help with caching. All these solutions are transparent, but often they also have performance and management implications. If you are using Percona Server for MySQL, you have yet another option, “column …[Read more]
This is a Release Candidate quality release and
it is not intended for
production. If you want a high quality, Generally Available release, use the current stable version (the most recent …