The details on this issue are here:
This test is very simple. I loaded the SSB (star schema benchmark) data for scale factor 20 (12GB raw data), added indexes, and tried to count the rows in the table.
After loading data and creating indexes, the .rocksdb data directory is 17GB in size.
A full table scan "count(*)" query takes less than four minutes, sometimes reading over 1M rows per second, but when scanning the index to accomplish the same count, the database can only scan around 2000 rows per second. The four minute query would take an estimated 1000 minutes, a 250x difference.
I have eliminated the type of CRC32 function (SSE vs non-SSE) by forcing the hardware SSE function by patching the code.
There seem to be problems with any queries …
10 Older Entries »
The details on this issue are here:
One of the main tasks that any DBA has to deal with is certainly
data retention. Especially when your developers like to store
logging and tracking information inside the database. This is not
something that I would recommend (don't try it at home!) but when
you're late to the party (read: someone had taken this path
before you joined) , then you'll have to live with it.
Data retention in MySQL is usually applied by partitioning the table by RANGE on a selected column, usually containing the row creation timestamp. New partitions are created in advance for the coming days or weeks or months, and a script is used that will run nightly and drop partitions that are older than a set retention.
There are many tools that can be used to automate data retention; I have chosen the excellent pdb-parted, a nice and cozy perl script that you can find in the PalominoDB repository on GitHub (since PalominoDB is no longer in existence, …
If there is something that I love about information technology is
the incredible amount of unpredictable ways that bugs can bite
you where and when you less expect it.
This one I'm about to describe is a serious one, yet there has been a very quiet response from Percona bug team at Launchpad, where I first submitted it (because we use Percona server here). If you have read my other posts you know by now that I am a laz... err, busy guy so bear with me if only today I have verified that this is, in fact, an upstream bug and have raised a bug to Oracle too.
It affects 5.6.33, latest version at the time of this article.
Anyways, the bug started to manifest itself in the form of complete stalls of our main cluster, which was happening at different times and apparently in a way that was unrelated to the amount of traffic hitting the master. When stalling, system CPU time was topping 100% of total available …
One of the most interesting features introduced in MariaDB
10 was without doubt multi source replication, or the ability for
a slave to have multiple masters.
Tired of hearing complaints from developers who couldn't join tables because they were on different servers, I decided to give MariaDB a try to see if I could leverage this neat feature.
At the time, we had 5 main clusters, classic multi-master active/standby configuration, with some slaves under each of them. I wanted to create a "super slave" that would contain the dataset from all the five clusters, so that developers could connect to it and join at will.
The initial creation of the MariaDB superslave was easy. After installing the binaries, I just bootstrapped it with an xtrabackup copy of our main cluster and set up replication. All went just fine as expected.
Suddendly I realized that I couldn't use xtrabackup to bring the datasets from other …
A question which would come sometimes to mind when starting with
MySQL is whether I should use DATETIME or TIMESTAMP data type
since both appear to store same date and time component.
Similarities between datetime and timestamp:
1. Values contain both date and time parts.
2. Format of retrieval and display is "YYYY-MM-DD HH:MM:SS".
3. Can include a trailing fractional seconds part in up to microseconds (6 digits) precision.
4. With the fractional part included, the format for these values is "YYYY-MM-DD HH:MM:SS[.fraction]".
5. Both the TIMESTAMP and (as of MySQL 5.6.5) DATETIME offer automatic initialization and updating to the current date and time.
But both differ in some ways as mentioned below:
I've got several useful habits over the years of work in MySQL
Support. One of them is to start working on every problem with
search for known MySQL bugs related to the problem at hand.
I'd like to share one recent case where this habit helped me to
get a solution for customer almost instantly.
It was one of rare cases when customer opened a support request with a very clear question and even a test case. The problem was described very precisely, more or less as follows (with table and column names, and data changed for this blog post, surely).
Let's assume we have two tables created like these:
mysql> create table t1(id int auto_increment primary key, c1 varchar(2), c2 varchar(100));Query OK, 0 rows affected (0.27 sec)
mysql> create table t2(id int auto_increment primary key, t1_id int, ctime datetime, cvalue decimal(10,2), key(t1_id, ctime));
Query OK, 0 …
I had not reviewed bug fixes in MySQL 5.6 for quite a some time, so I decided to check what
bugs reported by MySQL Community were fixed in recently released MySQL 5.6.24. I'll mention
both a bug reporter and engineer who verified the bug in the list
below, because I still think that in MySQL world names should
So, MySQL 5.6.24 includes fixes for the following bugs from http://bugs.mysql.com. I'd start with InnoDB and memcached-related fixes:
- Bug #72080 - truncate temporary table crash: !DICT_TF2_FLAG_IS_SET(table, DICT_TF2_TEMPORARY). Reported by …
Restoring MariaDB (MySQL) slave using Xtrabackup & GTID.
Let me start with questions related to responsible MySQL bugs
reporting that I'd like to be discussed and then present a
history behind them.
Assuming that you, my dear reader from MySQL Community, noted or found some simple sequence of SQL statements that, when executed by authenticated MySQL user explicitly having all the privileges needed to execute these statements, crashes some version of your favorite MySQL fork, please, answer the following questions:
- Do you consider this kind of a bug a "security vulnerability"?
- Should you share complete test case at any public site (MySQL bugs database, Facebook, your personal blog, any)?
- Should you share just a description of possible "attack vector", as Oracle does when they publish security bug fixes?
- Should you share just a stack trace or failed assertion information, without any details on how to get it?
- Should …
One of the first posts in this blog was about reporting MySQL
bugs "properly", in a way that maximizes chances for it to be
processed really soon. I had written the following there:
"Ideally, you should provide a complete test case and/or instructions that any reader can use to reproduce your problem"Indeed, if one can just copy/paste something to mysql command line client or run some file attached to see the problem, chances are high for the bug to be processed really soon. We all like to get low hanging fruits from time to time, and Oracle engineers who work on bugs are not exceptions. But does this mean that bug without clear test case has no value and is going to be ignored?
It should NOT be the case. Let's review Bug #70617 reported by my …
10 Older Entries »