So I tried to do my first set of benchmarks and testing on
RocksDB today, but I ran into a problem and had to file a
bug:
https://github.com/facebook/mysql-5.6/issues/365
MySQL @ Facebook RocksDB appears to store at least 2x the size of
the volume of changes in a transaction. I don't know how much
space for the row + overhead there is in each transcation, so I'm
just going to say 2x the raw size of the data changed in the
transaction, as approximation. I am not sure how this works for
updates either, that is whether old/new row information is
maintained. If old/row data is maintained, then a pure update
workload you would need 4x the ram for the given transactional
changes. My bulk load was 12GB of raw data, so it failed as I
have only 12GB of RAM in my test system.
The workaround (as suggested in the bug) is to set two
configuration …
So I tried to do my first set of benchmarks and testing on
RocksDB today, but I ran into a problem and had to file a
bug:
https://github.com/facebook/mysql-5.6/issues/365
MySQL @ Facebook RocksDB appears to store at least 2x the size of
the volume of changes in a transaction. I don't know how much
space for the row + overhead there is in each transcation, so I'm
just going to say 2x the raw size of the data changed in the
transaction, as approximation. I am not sure how this works for
updates either, that is whether old/new row information is
maintained. If old/row data is maintained, then a pure update
workload you would need 4x the ram for the given transactional
changes. My bulk load was 12GB of raw data, so it failed as I
have only 12GB of RAM in my test system.
The workaround …
I'm not a huge fan of the InnoDB FULLTEXT feature and I admit I
wasn't too keen to play with it in the past. Apparently, the
feeling is mutual and so FULLTEXT issues haven't popped up in too
many projects I worked on... until last week.
This post describes the troubleshooting process of a FULLTEXT
cache performance issue. Quite inconspicuous at the beginning, it
proved to be a lot of fun in the end.
Ready, Set, GDB!
Background It all started with a generic performance issue report
unrelated to FULLTEXT indexing. The issue, as described in the
report, was:
- The server performs acceptably for a few days.
- After a certain amount of time, DML performance suddenly drops and remains low until the server is rebooted.
- Reboot restores original performance, which again only lasts for a few days.
The situation was resulting in DML latency degradation during normal OLTP …
[Read more]
On the MySQL Bugs website there are some tide stats
available. These show rate of bug creation.
I've put them in a graph:
I made these with this IPython Notebook. There are more detailed
graphs per version in the notebook.
Update: The version in the notebook now uses the same range for
the Y axis and has a marker for the GA dates of each release.
MySQL has just released MySQL 8.0 DR (and yes, DR stands for Don’t Run-it-in-production-yet) so let’s jump right in and take a look at the hottest new features coming in this new release:
1. Persistent runtime configuration changes.
Love it. From now on we’ll be able to use SET PERSIST
innodb_buffer_pool_size = X;
instead of SET GLOBAL
innodb_buffer_pool_size = X;
for the runtime changes to
persist during a restart. It may not make much sense if you’re
using a modern database that doesn’t even have a configuration
file, but for us who lived with MySQL for over 20 years, this is
huge!
How does it work? In a nutshell, these changes are saved in
mysqld-auto.cnf
file in MySQL data directory.
2. MySQL privilege tables are now InnoDB. I think this was …
[Read more]MySQL 8.0.0 was released today. It has been some time in the making, shrouded in a veil of secrecy for over one year. We knew, from listening to the gossip and looking at the few available previews, some of what was going to bring. So, for the observant users, its main features may not come as a surprise. For the rest of you, here's a quick roundup:
Notable features
- No MyISAM tables anymore! The grant tables are now InnoDB, meaning that grant operations are now atomic.
- A real data dictionary. This change is less visible than the previous one. The data dictionary tables are hidden and only a subset of the data is available through information_schema views. The reason for the hidden tables is to allow a stable interface through several versions. I am …
Several months ago I reported on Default Users in MySQL 5.7. With the addition of the sys schema the server needs an extra user to secure operations. The problem with extra users is that, if you are not aware of their existence and why they are needed, you may easily mismanage them. For example, you may have a cleanup routine that you run when you use a new server, and the routine may have a command like
DELETE FROM mysql.user WHERE user != 'root';
This was good up to MySQL 5.6. Then the sys schema was added, and
with it the user mysql.sys
, which may cause errors
if you try to re-create views in the sys schema.
The latest user sneaking below the radar is
mysqlxsys
. Like its predecessor, it …
This is not a comprehensive review, nor an user guide. It's a step-by-step account of my initial impressions while trying the new MySQL XProtocol and the document store capabilities. In fact, I am barely scratching the surface here: more articles will come as time allows.
MySQL 5.7 has been GA for several months, as it was released in October 2015. Among the many features and improvements, I was surprised to see the MySQL team emphasizing the JSON data type. While it is an interesting feature per se, I failed to see the reason why so many articles and conference talks were focused around this single feature. Everything became clear when, with the release of MySQL 5.7.12, the MySQL team announced a new release model.
Overview
In …
[Read more]Restating MySQL can be really annoying. You just want to disable the goddamn query cache and it takes forever (read 5-10 minutes) to shutdown, not to mention the warm-up time. Yes, with MySQL 5.7 you can do many changes online, so you won’t necessarily be restarting that often, but you still need to do upgrades, occasionally increase redo log size and, admit it, enable skip-grant-tables. Here’s how you can make this process way less painful.
Why is MySQL so slow to restart?
Before we go any further, let me tell you right away that when I’m speaking about MySQL here, I’m actually speaking about InnoDB, or rather, a MySQL server that’s running InnoDB as the main storage engine. And if that’s not your case, do not read any further. You’ve been warned!
Now.. ah yes. Restart. So, restarting MySQL involves two slow stages. I have already mentioned them, but repetition is the mother of skill, so let me say …
[Read more]Playing around with the Employees sample database and full text search on MySQL, I found a weird bug. After creating a full text targeting a column on a table, a "select distinct" query to retrieve the range of values returns an empty set. So if you initially perform the following query, the outcome comes with 7 rows: mysql> select distinct title from titles; +--------------------+ | title