Planet MySQL

MySQL Queries No Waiting

Last time we looked at SKIP LOCKED where rows locked by another process were skipped. NOWAIT informs the server to return immediately IF the desired rows can not be locked.

How To Use NOWAIT Start a connection to your MySQL 8 server, start a transaction, and make a query to lock up part of the data. mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE;
On a second connection, start a transaction and issue a SELECT query with NOWAIT. mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE NOWAIT;
The second connection will get a message saying 'statement aborted because lock(s) could not be acquired immediately and NOWAIT is SET
So if you can come back later to lock the records or just can not wait around for your lock to be issued, NOWAIT is the keyword to add to your queries.

How to Install PrestaShop on Debian 9

In this guide, we’ll show you how to install and configure the latest version of Prestashop on Debian 9.1. Prestashop is a free Open Source e-commerce content management platform written in PHP programming language which is often deployed on Linux under Apache/Nginx web servers, PHP and MySQL/MariaDB database management system.

In-memory insert benchmark for InnoDB on a small server: MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with InnoDB from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This uses an in-memory workload with an i3 NUC and i5 NUC. The i5 NUC is newer and faster. The next post covers an IO-bound test for InnoDB.

tl;dr - from 5.0.96 to 8.0.3
  • Regressions are frequently larger on the i3 NUC than the i5 NUC. Maybe modern MySQL and the core i3 NUC aren't great together because regressions are also larger on the i3 NUC for MyISAM.
  • Insert performance improved by ~3X after MySQL 5.5 when I switched from built-in InnoDB to modern InnoDB.
  • The query rate decreased by 15% on the i5 NUC and 39% on the i3 NUC for the test with 100 inserts/second
tl;dr - from 5.6.35 to 8.0.3
  • Most of the drop in performance from 5.0 to 8.0 occurs between 5.6.35 and 8.0.3. The drop is similar for MyISAM and InnoDB. I assume the drop is from code above the storage engine.
  • The insert rate decreased by 12% on the i5 NUC and 26% on the i3 NUC
  • The query rate decreased by 13% on the i5 NUC and 22% on the i3 NUC for the test with 100 inserts/second
  • Scan performance improved

Configuration

The tests used InnoDB from upstream MySQL versions 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.3. The built-in InnoDB (remember that?) was used for 5.0, 5.1 and 5.5 and then I switched to modern InnoDB starting with 5.6. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. The i5 NUC has more RAM, faster CPUs and faster storage than the i3 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, used the same charset and collation and set innodb_purge_threads=1 to reduce mutex contention. For all tests the binlog was enabled but fsync was disabled for the binlog and database redo log. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

The database fits in RAM as the test table has ~10M rows. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark loaded the table with 10M rows, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Results

All of the data for the tests is here.

Results: load

The graph below has the insert rate for each release relative to the rate for InnoDB in 5.0.96. Performance improves dramatically in 5.6 thanks to switching from the built-in InnoDB to modern InnoDB. There are regressions post-5.6 that I hope get addressed.
Additional metrics help to explain performance. The metrics are explained here. With the switch from built-in InnoDB (5.0 to 5.5) to modern InnoDB (5.6, 5.7, 8.0) write-amplification (wKB/i) improved on the i3 and i5 NUC while CPU overhead per insert (Mcpu/i) decreased on the i5 NUC (i3 NUC results are odd). From 5.6 to 8.0 the insert rate dropped by 26% on the i3 NUC and 12% on the i5 NUC. The larger CPU overhead (Mcpu/i) probably explains this on the i3 NUC. It is harder to explain on the i5 NUC but note that the CPU overhead includes foreground (user threads) and background (write back, purge). In my previous post the regression for MyISAM was ~10% on both the i3 and i5 NUC. So the InnoDB regression is similar for the i5 NUC but not the i3 NUC. From this result and others I suspect the regression is from code that runs above InnoDB.

i3 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   4803   35.75   3069    2.5
5.1.72   4737   35.71   3129    2.5
5.5.51   4643   38.03   3427    2.5
5.6.35  18797    8.87   1737    2.4
5.7.17  15083   10.36   3497    2.4
8.0.3   13908   11.02   3783    2.4

i5 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96   8368   35.69   2643    2.5
5.1.72   8326   35.70   2713    2.5
5.5.51   7628   37.98   2972    2.5
5.6.35  29586    8.06   1461    2.4
5.7.17  25907    9.66   1491    2.4
8.0.3   26178    9.60   1474    2.5

Results: scan

Below are tables that show the number of seconds for each full index scan: 1 is the PK, 2/3/4 are the secondary indexes and 5 is the PK again. The scan doesn't take long and the result is rounded to a whole number so the numbers aren't that useful. If there is a regression from 5.0 to 8.0 it isn't apparent in this result. Scan performance got worse from 5.5.51 (builtin InnoDB) to 5.6.35 (modern InnoDB). But then it improved in 5.7.17 for the i5 NUC and for many other tests I do, but for some reason it didn't on the i3 NUC.

#seconds to scan an index, i3 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
6       3       2       3       6        8      5.0.96
3       3       2       3       3        8      5.1.72
3       3       3       3       3        9      5.5.51
4       4       4       4       4       12      5.6.35
3       4       7       3       3       14      5.7.17
4       4       3       4       3       11      8.0.3

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       2       2       2       5        6      5.0.96
3       2       3       2       2        7      5.1.72
3       2       3       3       2        8      5.5.51
3       4       3       4       3       11      5.6.35
3       3       3       2       3        8      5.7.17
2       3       3       3       3        9      8.0.3

Results: read-write, 1000 inserts/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 41% for the i3 NUC and 19% for the i5 NUC. Most of the regression occurs after 5.6.35. The large regression on the i3 NUC is another example of odd results for the i3 NUC. The regression was much smaller for MyISAM in my previous post.
The built-in InnoDB used for 5.0, 5.1 and 5.5 was unable to sustain the target insert rate. Write-amplification was also larger with built-in InnoDB. But it was able to sustain a larger QPS. QPS drops by 25% on the i3 NUC and 12% on the i5 NUC from 5.6.35 to 8.0.3. Write stalls were worse for built-in InnoDB, but even modern InnoDB had some stalls given the values in IPS.99 that are less than 999. Additional metrics help explain the performance. The  metrics are explained here. The CPU overhead per query (CPU/q) more than doubles on the i3 NUC from 5.6.35 to 5.7.17. That helps to explain the QPS reduction. The CPU overhead per query increased on the i5 NUC by 11% from 5.6.35 to 8.0.3.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
992     770     64.13   5675    5277     5332   5.0.96
992     731     64.03   4990    4863     5910   5.1.72
998     943     67.66   4364    4249     6845   5.5.51
999     994     43.90   4817    4454     5970   5.6.35
999     995     40.27   3748    3540    13834   5.7.17
999     995     41.32   3527    3349    14750   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     993     64.31   6183    5534    4776    5.0.96
998     963     64.05   5646    5594    5118    5.1.72
999     993     70.40   5284    5052    5591    5.5.51
999     998     44.12   5798    5410    4846    5.6.35
999     998     40.62   5184    4917    5186    5.7.17
999     998     41.23   4995    4763    5389    8.0.3

Results: read-write, 100 inserts/second

This section has results for the read-write tests where the writer does 100 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The regression from MySQL 5.0.96 to 8.0.3 is 39% on the i3 NUC and 15% on the i5 NUC. The regression from 5.6.35 to 8.0.3 is 22% for the i3 NUC and 13% for the i5 NUC. On the i5 NUC the CPU overhead per insert, Mcpu/i, increased by 18% from 5.0 to 8.0 and by 16% from 5.6 to 8.0 which probably explains the decrease in the insert rate.
All of the engines were able to sustain the target insert rate on average (ips.av) and write stalls were not a problem. Additional metrics help explain the performance. The  metrics are explained here

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.34   6058    5877     4238   5.0.96
100     100     67.38   5033    4891     5013   5.1.72
100     100     98.90   4465    4274     5766   5.5.51
100     100     51.85   4729    4499     5457   5.6.35
100     100     52.94   3906    3717    12636   5.7.17
100     100     61.08   3681    3524    13407   8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     67.54   6062    5543    4211    5.0.96
100     100     67.25   5655    5586    4439    5.1.72
100     100     98.98   5189    5035    4931    5.5.51
100     100     51.95   5937    5548    4309    5.6.35
100     100     55.24   5349    4976    4803    5.7.17
100     100     54.07   5148    4819    4978    8.0.3

HAProxy Connections vs MySQL Connections - What You Should Know

Having a load balancer or reverse proxy in front of your MySQL or MariaDB server does add a little bit of complexity to your database setup, which might lead to some, things behaving differently. Theoretically, a load balancer which sits in front of MySQL servers (for example an HAProxy in front of a Galera Cluster) should just act like a connection manager and distribute the connections to the backend servers according to some balancing algorithm. MySQL, on the other hand, has its own way of managing client connections. Ideally, we would need to configure these two components together so as to avoid unexpected behaviours, and narrow down the troubleshooting surface when debugging issues.

If you have such setup, it is important to understand these components as they can impact the overall performance of your database service. In this blog post, we will dive into MySQL's max_connections and HAProxy maxconn options respectively. Note that timeout is another important parameter that we should know, but we are going to cover that in a separate post.

MySQL's Max Connections Related resources  MySQL Load Balancing with HAProxy - Tutorial  Webinar Replay and Q&A: how to deploy and manage ProxySQL, HAProxy and MaxScale  Webinar Replay & Slides: How to build scalable database infrastructures with MariaDB & HAProxy

The number of connections permitted to a MySQL server is controlled by the max_connections system variable. The default value is 151 (MySQL 5.7).

To determine a good number for max_connections, the basic formulas are:

Where,

**Variable innodb_additional_mem_pool_size is removed in MySQL 5.7.4+. If you are running in the older version, take this variable into account.

And,

By using the above formulas, we can calculate a suitable max_connections value for this particular MySQL server. To start the process, stop all connections from clients and restart the MySQL server. Ensure you only have the minimum number of processes running at that particular moment. You can use 'mysqladmin' or 'SHOW PROCESSLIST' for this purpose:

$ mysqladmin -uroot -p processlist +--------+------+-----------+------+---------+------+-------+------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------+-----------+------+---------+------+-------+------------------+----------+ | 232172 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 | +--------+------+-----------+------+---------+------+-------+------------------+----------+ 1 row in set (0.00 sec)

From the above output, we can tell that only one user is connected to the MySQL server which is root. Then, retrieve the available RAM (in MB) of the host (look under 'available' column):

$ free -m total used free shared buff/cache available Mem: 3778 1427 508 148 1842 1928 Swap: 2047 4 2043

Just for the info, the 'available' column gives an estimate of how much memory is available for starting new applications, without swapping (only available in kernel 3.14+).

Then, specify the available memory, 1928 MB in the following statement:

mysql> SELECT ROUND((1928 - (ROUND((@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@query_cache_size + @@tmp_table_size + @@key_buffer_size) / 1024 / 1024))) / (ROUND(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@join_buffer_size + @@binlog_cache_size) / 1024 / 1024)) AS 'Possible Max Connections'; +--------------------------+ | Possible Max Connections | +--------------------------+ | 265 | +--------------------------+

**Variable innodb_additional_mem_pool_size is removed in MySQL 5.7.4+. If you are running in the older version, take this variable into account.

From this example, we can have up to 265 MySQL connections simultaneously according to the available RAM the host has. It doesn't make sense to configure a higher value than that. Then, append the following line inside MySQL configuration file, under the [mysqld] directive:

max_connections = 265

Restart the MySQL service to apply the change. When the total simultaneous connections reaches 265, you would get a "Too many connections" error when trying to connect to the mysqld server. This means that all available connections are in use by other clients. MySQL actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. So if you face this error, you should try to access the server as a root user (or any other SUPER user) and look at the processlist to start the troubleshooting.

HAProxy's Max Connections

HAProxy has 3 types of max connections (maxconn) - global, defaults/listen and default-server. Assume an HAProxy instance configured with two listeners, one for multi-writer listening on port 3307 (connections are distributed to all backend MySQL servers) and another one is single-writer on port 3308 (connections are forwarded to a single MySQL server):

global ... maxconn 2000 #[a] ... defaults ... maxconn 3 #[b] ... listen mysql_3307 ... maxconn 8 #[c] balance leastconn default-server port 9200 maxqueue 10 weight 10 maxconn 4 #[d] server db1 192.168.55.171 check server db2 192.168.55.172 check server db3 192.168.55.173 check listen mysql_3308 ... default-server port 9200 maxqueue 10 weight 10 maxconn 5 #[e] server db1 192.168.55.171 check server db2 192.168.55.172 check backup #[f]

Let’s look at the meaning of some of the configuration lines:

global.maxconn [a]

The total number of concurrent connections that are allowed to connect to this HAProxy instance. Usually, this value is the highest value of all. In this case, HAProxy will accept a maximum of 2000 connections at a time and distribute them to all listeners defined in the HAProxy process, or worker (you can run multiple HAProxy processes using nbproc option).

HAProxy will stop accepting connections when this limit is reached. The "ulimit-n" parameter is automatically adjusted to this value. Since sockets are considered equivalent to files from the system perspective, the default file descriptors limit is rather small. You will probably want to raise the default limit by tuning the kernel for file descriptors.

defaults.maxconn [b]

Defaults maximum connections value for all listeners. It doesn't make sense if this value is higher than global.maxconn.

If "maxconn" line is missing under the "listen" stanza (listen.maxconn), the listener will obey this value. In this case, mysql_3308 listener will get maximum of 3 connections at a time. To be safe, set this value equal to global.maxconn, divided by the number of listeners. However, if you would like to prioritize other listeners to have more connections, use listen.maxconn instead.

listen.maxconn [c]

The maximum connections allowed for the corresponding listener. The listener takes precedence over defaults.maxconn if specified. It doesn't make sense if this value is higher than global.maxconn.

For a fair distribution of connections to backend servers like in the case of a multi-writer listener (mysql_3307), set this value as listen.default-server.maxconn multiply by the number of backend servers. In this example, a better value should be 12 instead of 8 [c]. If we chose to stick with this configuration, db1 and db2 are expected to receive a maximum of 3 connections each, while db3 will receive a maximum of 2 connections (due to leastconn balancing), which amounts to 8 connections in total. It won't hit the limit as specified in [d].

For single-writer listener (mysql_3308) where connections should be allocated to one and only one backend server at a time, set this value to be the same or higher than listen.default-server.maxconn.

listen.default-server.maxconn [d][e]

This is the maximum number of connections that every backend server can receive at a time. It doesn't make sense if this value is higher than listen.maxconn or defaults.maxconn. This value should be lower or equal to MySQL's max_connections variable. Otherwise, you risk exhausting the connections to the backend MySQL server, especially when MySQL's timeout variables are configured lower than HAProxy's timeouts.

In this example, we've set each MySQL server to only get a maximum of 4 connections at a time for multi-writer Galera nodes [d]. While the single-writer Galera node will get a maximum of 3 connections at a time, due to the limit that applies from [b]. Since we specified "backup" [f] to the other node, the active node will at once get all 3 connections allocated to this listener.

The above explanation can be illustrated in the following diagram:

To sum up the connections distribution, db1 is expected to get a maximum number of 6 connections (3 from 3307 + 3 from 3308). The db2 will get 3 connections (unless if db1 goes down, where it will get additional 3) and db3 will stick to 2 connections regardless of topology changes in the cluster.

Connection Monitoring with ClusterControl

With ClusterControl, you can monitor MySQL and HAProxy connection usage from the UI. The following screenshot provides a summary of the MySQL connection advisor (ClusterControl -> Performance -> Advisors) where it monitors the current and ever used MySQL connections for every server in the cluster:

For HAProxy, ClusterControl integrates with HAProxy stats page to collect metrics. These are presented under the Nodes tab:

From the above screenshot, we can tell that each backend server on multi-writer listener gets a maximum of 8 connections. 4 concurrent sessions are running. These are highlighted in the top red square, while the single-writer listener is serving 2 connections and forwarding them to a single node respectively.

Conclusion

Configuring the maximum connections for HAProxy and MySQL server is important to ensure good load distribution to our database servers, and protect the MySQL servers from overloading or exhausting its connections.

Tags:  haproxy MySQL MariaDB troubleshooting connections

SKIP LOCKED

SKIP LOCKED is a new feature in MySQL 8 that many will find valuable.  If allows you to not wait about for locked records and work on what is available -- the unlocked records.
How To Use SKIP LOCKED The MySQL world database has 274 records in the city table where the countryCode field equals 'USA' there are 274 records. From past interactions, we somehow know there are some records with the ID field greater than 4000.
On MySQL shell number 1, start a transaction and lock some records mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE ID > 4000 and countryCode = 'USA'; There will be 66 records.
On MySQL shell number number 2, start a transaction and lets try to lock the records starting at IS 3990 and up. mysql>START TRANSACTION; mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
FOR UPDATE SKIPPED LOCKED;
There will be 10 records.  The records 4000 and up are locked by the transaction on the other shell.  
So no more hanging around for locked records.  You can process what is available at the current time.  So in situations where you are vending seats, hotel rooms, rental cares, or what have you -- you can find out what records are not locked by others and process them.

Questions I have about workloads with high write rates

I am sure I borrowed some of these from co-workers. When I read discussions about write-heavy workloads I ask myself some questions. I assume VoltDB and Tarantool has answers, but these are hard problems. I know that many of my write heavy MySQL tests ignore many of these problems because I usually run them with replication disabled.
  1. How long do you expect the storage device to last for it? Is it OK to replace the storage device once per month because you need too many DWPD?
  2. Do you have network capacity to support that QPS load on a master? Assume all servers in the rack get that QPS and the QPS source is not rack local.
  3. Do you have network capacity to replicate that write load to a replica? Assume some replicas are far away.
  4. Can the replica replay those writes fast enough to avoid lag?
  5. Will you be able to keep enough replication log archives to support PITR?
  6. How will you take backups?
  7. Will restore ever finish applying logs during the catch up phase?

MySQL Enterprise Audit – parsing audit information from log files, inserting into MySQL table via LOAD DATA INFILE and Perl script

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
(from https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html)

When you enable MySQL Enterprise Audit, log files are generated in your MySQL data directory. You can use tools like MySQL Workbench (Enterprise Edition) or Oracle Audit Vault to import the log data, to view the information and to generate reports.

I was talking with a client, and he wanted to know if the audit data could be stored in a table. Currently (as of MySQL 5.6.25), the audit information is stored as XML in the audit log files. There are several ways to do this, and I will cover two methods.

The first is to use the LOAD XML [LOCAL] INFILE command. You will need to create a table to store the audit information:

CREATE TABLE audit_log ( RECORD_ID varchar(40) NOT NULL, NAME varchar(64), TIMESTAMP timestamp, COMMAND_CLASS varchar(64), CONNECTION_ID bigint unsigned, DB varchar(64), HOST varchar(60), IPv4 int unsigned, IPv6 varbinary(16), MYSQL_VERSION varchar(64), OS_LOGIN varchar(64), PRIV_USER varchar(16), PROXY_USER varchar(16), SERVER_ID int unsigned, SQLTEXT longtext, STARTUP_OPTIONS text, STATUS int unsigned, STATUS_CODE int unsigned, USER varchar(168), VERSION int unsigned, PRIMARY KEY(RECORD_ID) ) DEFAULT CHARSET utf8mb4;

You can then load the data as:

LOAD XML LOCAL INFILE 'audit.log' INTO TABLE audit_log CHARACTER SET utf8mb4 ROWS IDENTIFIED BY '' (RECORD_ID, NAME, @TIMESTAMP, COMMAND_CLASS, CONNECTION_ID, DB, HOST, @IP, MYSQL_VERSION, OS_LOGIN, PRIV_USER, PROXY_USER, SERVER_ID, SQLTEXT, STARTUP_OPTIONS, STATUS, STATUS_CODE, USER, VERSION) SET TIMESTAMP = CONVERT_TZ(STR_TO_DATE(@TIMESTAMP, '%Y-%m-%dT%H:%i:%s UTC'), 'UTC', 'Australia/Sydney'), IPv4 = IF(IS_IPV4(@IP), INET_ATON(@IP), NULL), IPv6 = IF(IS_IPV6(@IP), INET6_ATON(@IP), NULL);

Important notes for the above example:

  • The example converts the IP address to the numeric version and stores it in IPv4 or IPv6 depending on the type of IP address. It is also possible to store the IP address in a shared varchar() column.
  • Replace the target time zone in CONVERT_TZ() with the time zone of you system.
  • The use of CONVERT_TZ() requires named time zones to be loaded or that you use a numeric offset such as +10:00.

RECORD_ID is guaranteed unique with the following limitations:

  • If you change audit_log_format, the counter will reset. However as the timestamp is also included in the RECORD_ID this will in general not be an issue.
  • It is only unique for a given MySQL instance. If you want to import the audit logs for multiple instances, you can for example add the server_uuid to the table and use as part of the primary key.
  • server_uuid is available starting from MySQL 5.6.
  • The matching between the elements in the XML file and the column names is case sensitive. All the elements in the audit log are in upper case.
  • The ROWS IDENTIFIED BY ” clause is required unless the table is named AUDIT_RECORD as the table name is the default elements to look for as rows.

Here are some links for more reading on using LOAD XML [LOCAL] INFILE:

https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-options-variables.html#sysvar_audit_log_format
https://dev.mysql.com/doc/refman/5.6/en/load-xml.html
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_convert-tz
https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
https://dev.mysql.com/doc/refman/5.6/en/replication-options.html#sysvar_server_uuid

For the second option, I wrote a quick Perl script that would parse the XML log files and insert the information into a MySQL database. You will need to set the size of your audit log files in your my.cnf or my.ini configuration file via the audit_log_rotate_on_size variable. You might need to adjust the size of your log files based upon database activity and how well the script parses the log files. If your log files are very large, the Perl script might have issues processing it, and you might want to decrease the size of your log files and run the script more frequently.

CAVEAT
Enterprise Audit does require a license from MySQL. If you are interested in an Enterprise subscription, contact me via the comment section below. If you are an Enterprise customer, you will need to configure Enterprise Audit first. See the Enterprise Audit online documentation page for more information, or contact MySQL Support.

For the data fields, I used the audit log file format information found at The Audit Log File page on MySQL.com.

My MySQL server doesn’t have a lot of activity, so I tried to configure the size of the data fields as best as possible to accommodate the possible size of the data in each field. There may be instances where you will have to increase the size of these fields or change their data types. The largest field is the SQL_TEXT field which will contain your SQL statements. Every table has a max row size of 65,535 bytes. So, the largest possible size of the SQL_TEXT field could be for this example is around 63,200 bytes (65,535 bytes minus the sum of the size of all of the other fields, and minus the 1-byte or 2-byte length prefix used for each varchar field). In this example, the SQL_TEXT field is set to 8,096 bytes, so you may need to increase or decrease this value.

I used varchar data types for each field, excluding the primary key field named ID. I did not spend a lot of time on the database schema, so you might want to modify it a little. I am sure that some of the fields are integers, but I did not have enough data in my log files to positively determine all of the possible values for each field. I did read the online manual, and it stated that CONNECTION_ID, SERVER_ID, STATUS, STATUS_CODE and VERSION were unsigned integers – but I left them as varchar.

NOTICE
This script requires the use of the new format for the audit log files, which is available in MySQL versions 5.6.20 or later.

I created a database along with two tables; one to store the log file information, and a history table to keep track of what files had already been parsed and inserted into MySQL, as well as the number of log file entries. The CREATE DATABASE and CREATE TABLE syntax is as follows:

CREATE DATABASE `audit_information` /*!40100 DEFAULT CHARACTER SET latin1 */ CREATE TABLE `audit_parsed` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `COMMAND_CLASS` varchar(64) DEFAULT NULL, `CONNECTIONID` varchar(32) DEFAULT NULL, `DB_NAME` varchar(64) DEFAULT NULL, `HOST_NAME` varchar(256) DEFAULT NULL, `IP_ADDRESS` varchar(16) DEFAULT NULL, `MYSQL_VERSION` varchar(64) DEFAULT NULL, `COMMAND_NAME` varchar(64) DEFAULT NULL, `OS_LOGIN` varchar(64) DEFAULT NULL, `OS_VERSION` varchar(256) DEFAULT NULL, `PRIV_USER` varchar(16) DEFAULT NULL, `PROXY_USER` varchar(16) DEFAULT NULL, `RECORD_ID` varchar(64) DEFAULT NULL, `SERVER_ID` varchar(32) DEFAULT NULL, `SQL_TEXT` varchar(8096) DEFAULT NULL, `STARTUP_OPTIONS` varchar(1024) DEFAULT NULL, `COMMAND_STATUS` varchar(64) DEFAULT NULL, `STATUS_CODE` varchar(11) DEFAULT NULL, `DATE_TIMESTAMP` varchar(24) DEFAULT NULL, `USER_NAME` varchar(128) DEFAULT NULL, `LOG_VERSION` varchar(11) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 CREATE TABLE `audit_history` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `AUDIT_LOG_NAME` varchar(64) DEFAULT NULL, `PARSED_DATE_TIME` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `LOG_ENTRIES` int(11) DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The Perl script finds the non-active log files (which end in .xml – example: audit.log.14357895017796690.xml), parses the data, creates an SQL file with INSERT statements, imports the data via the mysql command-line program, and then moves the log file(s) and SQL file(s) to a directory. The history table records what files have been processed, so you don’t accidentally process the same file twice.

In the beginning of the Perl script, there are several values you need to replace to match your system. The values are under the section titled “values needed”. Here is the Perl script (named audit.pl):

#!/usr/bin/perl # audit.pl use DBI; use CGI; use XML::Simple; #---------------------------------------------------------- # values needed $Database = "audit_information"; $MYSQL_DATA_DIR = "/usr/local/mysql/data"; $MySQL_Host_IP_Name = "192.168.1.2"; $mysql_user = "root"; $mysql_password = "password_needed"; # directory to store old audit files after parsing $audit_directory = "$MYSQL_DATA_DIR/audit_files"; # make an audit_files directory if one does not exist mkdir($audit_directory) unless(-d $audit_directory); #---------------------------------------------------------- #---------------------------------------------------------- #for each file do this @files = @files = ;; foreach $file_name_to_parse (@files) { #---------------------------------------------------------- # check to see if file has already been parsed $dbh1 = ConnectToMySql($Database); $query1 = "select AUDIT_LOG_NAME from audit_history where AUDIT_LOG_NAME = '$file_name_to_parse'"; $sth1 = $dbh1->prepare($query1); $sth1->execute(); while (@data = $sth1->fetchrow_array()) { $audit_log_name = $data[0]; } # if length of audit_log_name is less than 1, process file if (length($audit_log_name) $PARSED_FILE") or die print "Couldn't open log_file: $!"; $count = 0; # XML::Simple variable - SuppressEmpty => 1 ignore empty values $xml = XML::Simple->new(SuppressEmpty => 1); $data = $xml->XMLin("$file_name_to_parse"); foreach $info (@{$data->{AUDIT_RECORD}}) { # replace tick marks ' with \' in the SQL TEXT $info->{"SQLTEXT"} =~ s/'/\\'/g; print LOGOUT "INSERT INTO audit_information.AUDIT_PARSED (COMMAND_CLASS, CONNECTIONID, DB_NAME, HOST_NAME, IP_ADDRESS, MYSQL_VERSION, COMMAND_NAME, OS_LOGIN, OS_VERSION, PRIV_USER, PROXY_USER, RECORD_ID, SERVER_ID, SQL_TEXT, STARTUP_OPTIONS, COMMAND_STATUS, STATUS_CODE, DATE_TIMESTAMP, USER_NAME, LOG_VERSION) values ('" . $info->{"COMMAND_CLASS"} . "', '" . $info->{"CONNECTION_ID"} . "', '" . $info->{"DB"} . "', '" . $info->{"HOST"} . "', '" . $info->{"IP"} . "', '" . $info->{"MYSQL_VERSION"} . "', '" . $info->{"NAME"} . "', '" . $info->{"OS_LOGIN"} . "', '" . $info->{"OS_VERSION"} . "', '" . $info->{"PRIV_USER"} . "', '" . $info->{"PROXY_USER"} . "', '" . $info->{"RECORD_ID"} . "', '" . $info->{"SERVER_ID"} . "', '" . $info->{"SQLTEXT"} . "', '" . $info->{"STARTUP_OPTIONS"} . "', '" . $info->{"STATUS"} . "', '" . $info->{"STATUS_CODE"} . "', '" . $info->{"TIMESTAMP"} . "', '" . $info->{"USER"} . "', '" . $info->{"VERSION"} . "');\n"; $count++; # end foreach $info (@{$data->{AUDIT_RECORD}}) } # load parsed file into MySQL - hide warnings system("mysql -u$mysql_user -p$mysql_password /dev/null 2>&1"); $dbh2 = ConnectToMySql($Database); $query2 = "insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('$file_name_to_parse', '$count')"; # optional print output - uncomment if desired # print "$query2\n"; $sth2 = $dbh2->prepare($query2); $sth2->execute(); # close audit log file close(INFILE); # optional print output - uncomment if desired # print "Moving audit log ($file_name_to_parse) and log file ($PARSED_FILE) to $audit_directory.\n"; # strip directories off $file_name_to_parse @file_name_to_move_array = split("\/",$file_name_to_parse); $directory_count = $#file_name_to_move_array; $file_name_to_move = $file_name_to_move_array[$directory_count]; # optional print output - uncomment if desired # print "mv $file_name_to_move $file_name_to_parse\n"; # print "mv $PARSED_FILE $audit_directory\n"; # move audit log files and parsed log files to $audit_directory system("mv $file_name_to_parse $audit_directory"); system("mv $PARSED_FILE $audit_directory"); # end - if (length($audit_log_name) < 1) } else { # optional print output - uncomment if desired # print "$audit_log_name already processed\n"; system("mv $file_name_to_parse $audit_directory"); } # end - foreach $file_name_to_parse (@files) } sub ConnectToMySql { $connectionInfo="dbi:mysql:$Database;$MySQL_Host_IP_Name:3306"; # make connection to database $l_dbh = DBI->connect($connectionInfo,$mysql_user,$mysql_password); return $l_dbh; }

It should not matter where you execute audit.pl, as long as you have correctly entered the required values in the script. You might get errors if you try to run this script on a log file that has not been rotated, which is the current log file in your MySQL data directory. The current log file is named audit.log.

# pwd /usr/local/mysql/data # ls -l audit.log -rw-rw---- 1 mysql _mysql 9955118 Jul 2 15:25 audit.log

The script will only work on files ending in .xml. For testing, I used four small (and identical) audit log files:

# pwd /usr/local/mysql/data # ls -l *xml -rw-rw---- 1 mysql wheel 15508 Jul 2 12:20 audit.log.14357895017796690.xml -rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796691.xml -rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796692.xml -rw-r----- 1 mysql _mysql 15508 Jul 2 13:46 audit.log.14357895017796693.xml

I have commented-out the print statements in the Perl script, but if you uncomment them, running the script gives you this output for each log file:

# perl audit.pl Parsing - /usr/local/mysql/data/audit.log.14357895017796690.xml insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('/usr/local/mysql/data/audit.log.14357895017796690.xml', '34') Moving audit log (/usr/local/mysql/data/audit.log.14357895017796690.xml) and log file (/usr/local/mysql/data/audit.log.14357895017796690_parsed.sql) to /usr/local/mysql/data/audit_files. mv audit.log.14357895017796690.xml /usr/local/mysql/data/audit.log.14357895017796690.xml mv /usr/local/mysql/data/audit.log.14357895017796690_parsed.sql /usr/local/mysql/data/audit_files ....

After running my test script, the following data is what is in the audit_history table:

mysql> use audit_information Database changed mysql> select * from audit_history; +----+-------------------------------------------------------+---------------------+-------------+ | ID | AUDIT_LOG_NAME | PARSED_DATE_TIME | LOG_ENTRIES | +----+-------------------------------------------------------+---------------------+-------------+ | 1 | /usr/local/mysql/data/audit.log.14357895017796690.xml | 2015-07-02 15:25:07 | 34 | | 2 | /usr/local/mysql/data/audit.log.14357895017796691.xml | 2015-07-02 15:25:08 | 34 | | 3 | /usr/local/mysql/data/audit.log.14357895017796692.xml | 2015-07-02 15:25:08 | 34 | | 4 | /usr/local/mysql/data/audit.log.14357895017796693.xml | 2015-07-02 15:25:09 | 34 | +----+-------------------------------------------------------+---------------------+-------------+ 4 rows in set (0.00 sec)

And here is an example of one line from the audit_parsed table.

mysql> select * from audit_parsed limit 1 \G *************************** 1. row *************************** ID: 1 COMMAND_CLASS: select CONNECTIONID: 10093 DB_NAME: HOST_NAME: localhost IP_ADDRESS: 127.0.0.1 MYSQL_VERSION: COMMAND_NAME: Query OS_LOGIN: OS_VERSION: PRIV_USER: PROXY_USER: RECORD_ID: 1614933_2015-07-01T22:08:58 SERVER_ID: SQL_TEXT: SELECT (UNIX_TIMESTAMP(now()) - CAST(variable_value AS SIGNED))*1000 as serverStartMillis FROM information_schema.global_status WHERE variable_name='uptime' STARTUP_OPTIONS: COMMAND_STATUS: 0 STATUS_CODE: 0 DATE_TIMESTAMP: 2015-07-01T22:08:58 UTC USER_NAME: root[root] @ localhost [127.0.0.1] LOG_VERSION: 1 row in set (0.00 sec)

After parsing the log files, you can then write your own queries for searching through your audit data. You can even include this script in cron, so it runs and parses your files automatically. But as always, test this script and use it with caution before putting it in a production environment. You could also modify the Perl script to filter out values you did not want or need to store.

If you do use this script or if you have any suggestions or other questions, please leave a comment below.

Thanks to Jesper Krogh for providing the information on the LOAD XML [LOCAL] INFILE.

 

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. Tony is the author of Twenty Forty-Four: The League of Patriots

 

Visit http://2044thebook.com for more information.


when xtrabackup fails with 'read' returned OS error 0 after upgrading to 5.7

Here's something that has puzzled me for several weeks.
Right after migrating  MySQL from 5.6 to 5.7, we started experiencing random xtrabackup failures on some, but not all, of our slaves.
The failures were only happening when taking an incremental backup, and it would always fail on the same table on each slave, with errors similar to the following:

171106 13:00:33 [01] Streaming ./gls/C_GLS_IDS_AUX.ibd
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: Retry attempts for reading partial data failed.
InnoDB: Tried to read 262144 bytes at offset 0, but was only able to read 114688
InnoDB: File (unknown): 'read' returned OS error 0. Cannot continue operation
InnoDB: Cannot continue operation.

Searching the web didn't yield any useful result.
The error was happening on one slave but not on  another, OS, MySQL and Xtrabackup versions being the same:
  • CentOS release 6.8 (Final)
  • xtrabackup version 2.4.8 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 97330f7)
  • mysqld  Ver 5.7.19-17 for Linux on x86_64 (Percona Server (GPL), Release 17, Revision e19a6b7b73f)
To properly identify the table where the backup was failing I had to set parallelism to 1 (option --parallel=1 of xtrabackup). Examining the table with SHOW CREATE TABLE didn't show anything particular, the backup was failing on different tables and schemas, and tables were all different from each other and didn't have anything special,  or at least nothing that would  give me an hint.

I even tried to dump the offending schema from a failing slave and import it into a non failing slave, but on the non-failing slave the same table in that schema backed up just fine.

This has been going on for a while, where I used to run without incremental backups on the migrated slaves, until today, when it happened again on yet another slave that we had just migrated, and I had  an intuition:  since the failure of the incremental backup was only happening after the migration, of course it had to be related to the migration itself. So I started suspecting some incompatibility between 5.7 and/or xtrabackup, and tablespaces created with MySQL 5.6.

Verifying my suspect was as easy as running:

  ALTER TABLE C_GLS_IDS_AUX Engine=InnoDB

Et voilà!! The rebuilt tablespace was not crashing the incremental backup anymore.
Eventually, another table(space) would crash the backup again later, but running a NULL alter table on it and relaunching the backup would fix that. Wash, rinse, repeat until the incremental completes, and you're done.

Hope this helps someone saving some time..
And if you are looking for xtrabackup based backup scripts, feel free to check my own set here on my GitHub page.


ADDENDUM
 
Looks like I have been a little overenthusiastic about this.
In fact, the problem reappeared the day after writing this blog post - apparently, the null alter table only fixes it temporarily.

Turns out this has to be some incompatibility between Xtrabackup 2.4.x and MySQL 5.7.x as I was able to reproduce the issue. Please see this bug if you are interested in the details.

Insert benchmark for MyISAM from MySQL 5.0 to 8.0

This post explains performance for the insert benchmark with MyISAM from MySQL versions 5.0 to 8.0. The goal is to understand how performance has changed across releases. This is for an in-memory workload with an i3 NUC and i5 NUC. The i5 NUC is newer and faster.

tl;dr - from 5.0.96 to 8.0.3
  • Regressions are frequently larger on the i3 NUC than the i5 NUC. Maybe modern MySQL and the core i3 NUC aren't great together because regressions are also larger on the i3 NUC for InnoDB.
  • The insert rate decreased by 16% on the i5 NUC and 20% on the i3 NUC
  • The query rate decreased by 6% on the i5 NUC and 10% on the i5 NUC for the test with 100 inserts/second
tl;dr - from 5.6.35 to 8.0.3
  • Most of the drop in performance from 5.0 to 8.0 occurs between 5.6.35 and 8.0.3. The drop is similar for MyISAM and InnoDB. I assume the drop is from code above the storage engine. 
  • The insert rate decreased by 11% on the i5 NUC and 9% on the i3 NUC
  • The query rate decreased by 8% on the i5 NUC and 9% on the i3 NUC for the test with 100 inserts/second

Configuration

The tests used MyISAM from upstream MySQL versions 5.0.96, 5.1.72, 5.5.51, 5.6.35, 5.7.17 and 8.0.3. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. The i5 NUC has more RAM, faster CPUs and faster storage than the i3 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, used the same charset and collation. For all tests the binlog was enabled but fsync was disabled for the binlog and database redo log. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

The database fits in RAM as the test table has ~10M rows. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark loaded the table with 10M rows, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Results

All of the data for the tests is here.

Results: load

The graph below has the insert rate for each release relative to the rate for MyISAM in 5.0.96. There is a small regression over time in the insert rate. The loss from 5.7 to 8.0 is the largest. Fortunately, 8.0 is not GA yet and maybe this can be improved.

Additional metrics help to explain performance. The metrics are explained here. The CPU overhead per insert (Mcpu/i) has increased with each release (more features == more instructions to execute) and that explains the decrease in the insert rate. Otherwise the metrics look good. The increase in Mcpu/i from 5.0.96 to 8.0.3 is 19% for the i3 NUC and 16% for the i5 NUC. This matches the change in the insert rate.

i3 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96  27174   1.01    1404    2.1
5.1.72  28249   1.06    1372    2.1
5.5.51  24691   1.08    1555    2.1
5.6.35  23866   1.11    1592    1.7
5.7.17  24691   0.94    1543    1.7
8.0.3   21645   0.93    1675    1.7

i5 NUC
        IPS     wKB/i   Mcpu/i  size(GB)
5.0.96  33113   0.64    1123    1.7
5.1.72  34843   0.59    1108    1.7
5.5.51  31348   0.61    1192    1.8
5.6.35  31250   0.70    1193    2.1
5.7.17  29674   0.65    1236    1.9
8.0.3   27701   0.65    1305    2.0

Results: scan

Below are tables that show the number of seconds for each full index scan: 1 is the PK, 2/3/4 are the secondary indexes and 5 is the PK again. The scan doesn't take long and the result is rounded to a whole number so the numbers aren't that useful. If there is a regression from 5.0 to 8.0 it isn't apparent in this result.

#seconds to scan an index, i3 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
2       2       3       5       2       10      5.0.96
2       3       2       6       1       11      5.1.72
2       2       3       5       2       10      5.5.51
2       3       3       6       2       12      5.6.35
2       3       3       5       2       11      5.7.17
2       3       3       6       2       12      8.0.3

#seconds to scan an index, i5 NUC
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
1       3       2       4       2        9      5.0.96
1       2       3       4       2        9      5.1.72
1       2       3       4       2        9      5.5.51
2       2       3       5       2       10      5.6.35
1       3       2       5       1       10      5.7.17
2       2       3       4       2        9      8.0.3

Results: read-write, 1000 inserts/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 13% for the i3 NUC and 8% for the i5 NUC. That is small which is good.
All of the engines were able to sustain the target insert rate on average (ips.av). The value is 999 rather than 1000 because of implementation artifacts. The 99th percentile insert rate is 998 which means there were few write stalls. Additional metrics help explain the performance and more detail on the metrics is here. The increase in the CPU overhead per query (CPU/q) is 17% for the i3 NUC and 9% for the i5 NUC. More CPU overhead probably explains the drop in QPS.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     998     10.28   5158    4706    5160    5.0.96
999     998      9.97   5443    5028    4816    5.1.72
999     998     10.09   5192    4980    5107    5.5.51
999     998     10.01   4956    4757    5456    5.6.35
999     998     10.00   4672    4464    5792    5.7.17
999     998      9.93   4470    4306    6049    8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
999     998     10.79   5764    5383    4574    5.0.96
999     998     10.79   6144    5702    4215    5.1.72
999     998     10.84   5850    5694    4465    5.5.51
999     998     10.67   5744    5551    4613    5.6.35
999     998     10.58   5481    5285    4824    5.7.17
999     998     10.64   5284    5094    4994    8.0.3

Results: read-write, 100 inserts/second

This section has results for the read-write tests where the writer does 100 inserts/second. The graph has the query rate relative to the rate for MySQL 5.0.96. The QPS regression from MySQL 5.0.96 to 8.0.3 is 10% on the i3 NUC and 6% on the i5 NUC. That is small which is good.

All of the engines were able to sustain the target insert rate on average (ips.av) and write stalls were not a problem. Additional metrics help explain the performance and more detail on the metrics is here. The increase in the CPU overhead per query (CPU/q) is 12% for the i3 NUC and 7% for the i5 NUC. More CPU overhead probably explains the drop in QPS.

i3 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     13.89   5102    4734    4952    5.0.96
100     100     14.54   5321    5091    4687    5.1.72
100     100     15.59   5291    5077    4730    5.5.51
100     100     15.41   5076    4829    5058    5.6.35
100     100     15.61   4792    4555    5379    5.7.17
100     100     13.34   4596    4394    5569    8.0.3

i5 NUC
IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  CPU/q   engine
100     100     13.69   5719    5445    4366    5.0.96
100     100     13.49   5988    5770    4187    5.1.72
100     100     13.53   5992    5786    4214    5.5.51
100     100     13.41   5812    5621    4322    5.6.35
100     100     13.54   5476    5351    4612    5.7.17
100     100     13.56   5353    5188    4693    8.0.3

How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL

Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL. Domain Model For … Continue reading How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL →

Contention-Aware Transaction Scheduling Arriving in InnoDB to Boost Performance

Authors: Sunny Bains, Jiamin Huang (University of Michigan)

What is Transaction Scheduling?

Locking is one of the most popular mechanisms for concurrency control in most database systems, including Oracle MySQL. One major question, however, seems to have been overlooked by all database vendors:

Q: When multiple transactions are waiting for a lock on the same object, which one(s) should get the lock first?

GTID_INTERSECT

There's a GTID_SUBTRACT function, and the manual shows how to write your own cheap GTID_UNION, but not a GTID_INTERSECT. Fortunately it's easy to write your own, as it's just subtracting twice.

CREATE FUNCTION GTID_INTERSECT(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(g1, GTID_SUBTRACT(g1, g2));

What use is it?

SET @slave_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681577,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1267098:1267100-1267416:1267418-1589733';

SET @master_executed = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-730294,
421d139e-04b9-11e7-b702-0050569935dc:1-13764443,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SET @master_purged = '33738f8c-c1a5-11e7-8fc3-0a002700000f:1-681582,
421d139e-04b9-11e7-b702-0050569935dc:1-13077260,
52b9a949-d79d-11e3-80dd-0050568d193e:1-1207378:1207380-1261803:1261805-1589733';

SELECT @slave_needs := GTID_SUBTRACT(@master_executed, @slave_executed);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-730294, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

SELECT @slave_will_not_get := GTID_INTERSECT(@master_purged, @slave_needs);
/*-----------------------------------------------------+
| 33738f8c-c1a5-11e7-8fc3-0a002700000f:681578-681582, |
| 52b9a949-d79d-11e3-80dd-0050568d193e:1267099:1267417 |
+-----------------------------------------------------*/

While we're on the subject, the manual's GTID_UNION just concatenates the two sets, which means the output will list values twice. A small improvement will clean that up:

CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)
RETURNS TEXT DETERMINISTIC
RETURN GTID_SUBTRACT(CONCAT(g1,',',g2), '');

MySQL Jobs in UK for Nov 2017

Reserved Words

In the 1990s C.J.Date said: "The rule by which it is determined within the standard that one key word needs to be reserved while another need not be is not clear to this writer."

Nothing has changed since then, except there are more reserved words. No DBMS uses the standard list. So I think that it is probably best to know what words are reserved in product X that are not reserved in product Y. If you know, you can avoid syntax errors when you update or migrate.

I'll present several comparisons, ending with a grand chart of all the reserved words in the standard and six current DBMSs.

First here's a screenshot of ocelotgui where I'm hovering over the word BEGIN.

What I'm illustrating is that you can't depend on intuition and assume BEGIN is reserved, but a GUI client can tell you from context: it's a declared variable.

20 words are reserved in MariaDB but not in MySQL:

+-------------------------+ | word | +-------------------------+ | CURRENT_ROLE | | DO_DOMAIN_IDS | | GENERAL | | IGNORE_DOMAIN_IDS | | IGNORE_SERVER_IDS | | INTERSECT | | LEFT | | MASTER_HEARTBEAT_PERIOD | | MAX | | MODIFIES | | PAGE_CHECKSUM | | PARSE_VCOL_EXPR | | REF_SYSTEM_ID | | REPLACE | | RETURNING | | SCHEMA | | SLOW | | STATS_AUTO_RECALC | | STATS_PERSISTENT | | STATS_SAMPLE_PAGES | +-------------------------+

36 words are reserved in MySQL but not in MariaDB:

+-------------------+ | word | +-------------------+ | ADMIN | | ANALYSE | | CUBE | | CUME_DIST | | DENSE_RANK | | EMPTY | | FIRST_VALUE | | FUNCTION | | GENERATED | | GET | | GROUPING | | GROUPS | | IO_AFTER_GTIDS | | IO_BEFORE_GTIDS | | JSON_TABLE | | LAG | | LAST_VALUE | | LEAD | | LEAVESLEFT | | MASTER_BIND | | MODEMODIFIES | | NTH_VALUE | | NTILE | | OF | | OPTIMIZER_COSTS | | PERCENT_RANK | | PERSIST | | PERSIST_ONLY | | RANK | | REPEATABLEREPLACE | | ROW | | ROW_NUMBER | | SCHEDULESCHEMA | | STORED | | SYSTEM | | VIRTUAL | +-------------------+

15 words are reserved in MariaDB 10.3 but not in MariaDB 10.2:

+--------------------+ | word | +--------------------+ | CURRENT_ROLE | | DO_DOMAIN_IDS | | EXCEPT | | IGNORE_DOMAIN_IDS | | INTERSECT | | MAX | | OVER | | PAGE_CHECKSUM | | PARSE_VCOL_EXPR | | REF_SYSTEM_ID | | RETURNING | | STATS_AUTO_RECALC | | STATS_PERSISTENT | | STATS_SAMPLE_PAGES | | WINDOW | +--------------------+

(My MariaDB-10.3 list comes from the code source, my MariaDB-10.2 list comes from the manual, which may not be up to date.)

6 words are reserved in all of (DB2 and Oracle and Microsoft) but not in (MySQL or MariaDB):

+---------+ | word | +---------+ | ANY | | CURRENT | | FILE | | PUBLIC | | USER | | VIEW | +---------+

We said in SQL-99 Complete, Really: "[The standard] suggests that you include either a digit or an underline character in your regular identifiers and avoid names that begin with CURRENT_, SESSION_, SYSTEM_, or TIMEZONE_ and those that end with _LENGTH to avoid conflicts with reserved keywords added in future revisions." It's also good to avoid words that begin with SYS, or words that begin with the product name such as "IBM..." or "sql...". And of course it might also be good to use "delimiters", if you can avoid case-sensitivity confusions.

My original reason for making lists was to answer some questions about Tarantool. I do some paid work for this group, including tutorials about SQL like this one. In a forthcoming post I will show why I believe that this product is far ahead of the others that I discussed in an earlier post, What's in the SQL of NoSQL and even has some useful characteristics that MySQL/MariaDB lack.

Ocelot news: We have just uploaded a Windows version of the ocelotgui client, with an executable ocelotgui.exe static-linked to MariaDB Connector C and Qt. So it should be easy to download the release from github and run. See the windows.txt file on github for more explanation. Alpha.

To end this post, here is the grand finale list -- all reserved words in all dialects. Sta = Standard, Mar = MariaDB, MyS = MySQL, Db2 = DB2, Ora = Oracle, Mic = Microsoft, Odb = Odbc, Tar = Tarantool. (The Mic and Odb columns represent what Microsoft recommends but doesn't always enforce.) (The Tar column is still subject to change.)

+----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+ | word | Sta | Mar | MyS | Db2 | Ora | Mic | Odb | Tar | +----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+ | ABS | x | | | | | | | | | ABSOLUTE | | | | | | | x | | | ACCESS | | | | | x | | | | | ACCESSIBLE | | x | x | | | | | | | ACTION | | | | | | | x | | | ACTIVATE | | | | x | | | | | | ADA | | | | | | | x | | | ADD | | x | x | x | x | x | x | | | ADMIN | | | x | | | | | | | AFTER | | | | x | | | | | | ALIAS | | | | x | | | | | | ALL | x | x | x | x | x | x | x | x | | ALLOCATE | x | | | x | | | x | | | ALLOW | | | | x | | | | | | ALTER | x | x | x | x | x | x | x | x | | ANALYSE | | | x | | | | | | | ANALYZE | | x | x | | | | | x | | AND | x | x | x | x | x | x | x | x | | ANY | x | | | x | x | x | x | x | | ARE | x | | | | | | x | | | ARRAY | x | | | | | | | | | ARRAY_AGG | x | | | | | | | | | ARRAY_MAX_CARDINALITY | x | | | | | | | | | AS | x | x | x | x | x | x | x | x | | ASC | | x | x | | x | x | x | x | | ASENSITIVE | x | x | x | x | | | | x | | ASSERTION | | | | | | | x | | | ASSOCIATE | | | | x | | | | | | ASUTIME | | | | x | | | | | | ASYMMETRIC | x | | | | | | | | | AT | x | | | x | | | x | | | ATOMIC | x | | | | | | | | | ATTRIBUTES | | | | x | | | | | | AUDIT | | | | x | x | | | | | AUTHORIZATION | x | | | x | | x | x | | | AUTOINCREMENT | | | | | | | | x | | AUX | | | | x | | | | | | AUXILIARY | | | | x | | | | | | AVG | x | | | | | | x | | | BACKUP | | | | | | x | | | | BEFORE | | x | x | x | | | | | | BEGIN | x | | | x | | x | x | x | | BEGIN_FRAME | x | | | | | | | | | BEGIN_PARTITION | x | | | | | | | | | BETWEEN | x | x | x | x | x | x | x | x | | BIGINT | x | x | x | | | | | | | BINARY | x | x | x | x | | | | x | | BIT | | | | | | | x | | | BIT_LENGTH | | | | | | | x | | | BLOB | x | x | x | | | | | | | BOOLEAN | x | | | | | | | | | BOTH | x | x | x | | | | x | | | BREAK | | | | | | x | | | | BROWSE | | | | | | x | | | | BUFFERPOOL | | | | x | | | | | | BULK | | | | | | x | | | | BY | x | x | x | x | x | x | x | x | | CACHE | | | | x | | | | | | CALL | x | x | x | x | | | | x | | CALLED | x | | | x | | | | | | CAPTURE | | | | x | | | | | | CARDINALITY | x | | | x | | | | | | CASCADE | | x | x | | | x | x | | | CASCADED | x | | | x | | | x | | | CASE | x | x | x | x | | x | x | x | | CAST | x | | | x | | | x | x | | CATALOG | | | | | | | x | | | CCSID | | | | x | | | | | | CEIL | x | | | | | | | | | CEILING | x | | | | | | | | | CHANGE | | x | x | | | | | | | CHAR | x | x | x | x | x | | x | x | | CHARACTER | x | x | x | x | | | x | x | | CHARACTER_LENGTH | x | | | | | | x | | | CHAR_LENGTH | x | | | | | | x | | | CHECK | x | x | x | x | x | x | x | x | | CHECKPOINT | | | | | | x | | | | CLASSIFIER | x | | | | | | | | | CLOB | x | | | | | | | | | CLONE | | | | x | | | | | | CLOSE | x | | | x | | x | x | | | CLUSTER | | | | x | x | | | | | CLUSTERED | | | | | | x | | | | COALESCE | x | | | | | x | x | | | COLLATE | x | x | x | | | x | x | x | | COLLATION | | | | | | | x | | | COLLECT | x | | | | | | | | | COLLECTION | | | | x | | | | | | COLLID | | | | x | | | | | | COLUMN | x | x | x | x | x | x | x | x | | COLUMN_VALUE | | | | | x | | | | | COMMENT | | | | x | x | | | | | COMMIT | x | | | x | | x | x | x | | COMPRESS | | | | | x | | | | | COMPUTE | | | | | | x | | | | CONCAT | | | | x | | | | | | CONDITION | x | x | x | x | | | | x | | CONNECT | x | | | x | x | | x | x | | CONNECTION | | | | x | | | x | | | CONSTRAINT | x | x | x | x | | x | x | x | | CONSTRAINTS | | | | | | | x | | | CONTAINS | x | | | x | | x | | | | CONTAINSTABLE | | | | | | x | | | | CONTINUE | | x | x | x | | x | x | | | CONVERT | x | x | x | | | x | x | | | CORR | x | | | | | | | | | CORRESPONDING | x | | | | | | x | | | COUNT | x | | | x | | | x | | | COUNT_BIG | | | | x | | | | | | COVAR_POP | x | | | | | | | | | COVAR_SAMP | x | | | | | | | | | CREATE | x | x | x | x | x | x | x | x | | CROSS | x | x | x | x | | x | x | x | | CUBE | x | | x | | | | | | | CUME_DIST | x | | x | | | | | | | CURRENT | x | | | x | x | x | x | x | | CURRENT_CATALOG | x | | | | | | | | | CURRENT_DATE | x | x | x | x | | x | x | x | | CURRENT_DEFAULT_TRANSFORM_GROUP | x | | | | | | | | | CURRENT_LC_CTYPE | | | | x | | | | | | CURRENT_PATH | x | | | x | | | | | | CURRENT_ROLE | x | x | | | | | | | | CURRENT_ROW | x | | | | | | | | | CURRENT_SCHEMA | x | | | x | | | | | | CURRENT_SERVER | | | | x | | | | | | CURRENT_TIME | x | x | x | x | | x | x | x | | CURRENT_TIMESTAMP | x | x | x | x | | x | x | x | | CURRENT_TIMEZONE | | | | x | | | | | | CURRENT_TRANSFORM_GROUP_FOR_TYPE | x | | | | | | | | | CURRENT_USER | x | x | x | x | | x | x | x | | CURSOR | x | x | x | x | | x | x | x | | CYCLE | x | | | x | | | | | | DATA | | | | x | | | | | | DATABASE | | x | x | x | | x | | | | DATABASES | | x | x | | | | | | | DATAPARTITIONNAME | | | | x | | | | | | DATAPARTITIONNUM | | | | x | | | | | | DATE | x | | | x | x | | x | x | | DAY | x | | | x | | | x | | | DAYS | | | | x | | | | | | DAY_HOUR | | x | x | | | | | | | DAY_MICROSECOND | | x | x | | | | | | | DAY_MINUTE | | x | x | | | | | | | DAY_SECOND | | x | x | | | | | | | DB2GENERAL | | | | x | | | | | | DB2GENRL | | | | x | | | | | | DB2SQL | | | | x | | | | | | DBCC | | | | | | x | | | | DBINFO | | | | x | | | | | | DBPARTITIONNAME | | | | x | | | | | | DBPARTITIONNUM | | | | x | | | | | | DEALLOCATE | x | | | x | | x | x | | | DEC | x | x | x | | | | x | | | DECFLOAT | x | | | | | | | | | DECIMAL | x | x | x | | x | | x | x | | DECLARE | x | x | x | x | | x | x | x | | DEFAULT | x | x | x | x | x | x | x | x | | DEFAULTS | | | | x | | | | | | DEFERRABLE | | | | | | | x | x | | DEFERRED | | | | | | | x | | | DEFINE | x | | | | | | | | | DEFINITION | | | | x | | | | | | DELAYED | | x | x | | | | | | | DELETE | x | x | x | x | x | x | x | x | | DENSERANK | | | | x | | | | | | DENSE_RANK | x | | x | x | | | | x | | DENY | | | | | | x | | | | DEREF | x | | | | | | | | | DESC | | x | x | | x | x | x | x | | DESCRIBE | x | x | x | x | | | x | x | | DESCRIPTOR | | | | x | | | x | | | DETERMINISTIC | x | x | x | x | | | | x | | DIAGNOSTICS | | | | x | | | x | | | DISABLE | | | | x | | | | | | DISALLOW | | | | x | | | | | | DISCONNECT | x | | | x | | | x | | | DISK | | | | | | x | | | | DISTINCT | x | x | x | x | x | x | x | x | | DISTINCTROW | | x | x | | | | | | | DISTRIBUTED | | | | | | x | | | | DIV | | x | x | | | | | | | DO | x | | | x | | | | | | DOCUMENT | | | | x | | | | | | DOMAIN | | | | | | | x | | | DOUBLE | x | x | x | x | | x | x | x | | DO_DOMAIN_IDS | | x | | | | | | | | DROP | x | x | x | x | x | x | x | x | | DSSIZE | | | | x | | | | | | DUAL | | x | x | | | | | | | DUMP | | | | | | x | | | | DYNAMIC | x | | | x | | | | | | EACH | x | x | x | x | | | | x | | EDITPROC | | | | x | | | | | | ELEMENT | x | | | | | | | | | ELSE | x | x | x | x | x | x | x | x | | ELSEIF | x | x | x | x | | | | x | | EMPTY | x | | x | | | | | | | ENABLE | | | | x | | | | | | ENCLOSED | | x | x | | | | | | | ENCODING | | | | x | | | | | | ENCRYPTION | | | | x | | | | | | END | x | | | x | | x | x | x | | END-EXEC | x | | | x | | | x | | | ENDING | | | | x | | | | | | END_FRAME | x | | | | | | | | | END_PARTITION | x | | | | | | | | | EQUALS | x | | | | | | | | | ERASE | | | | x | | | | | | ERRLVL | | | | | | x | | | | ESCAPE | x | | | x | | x | x | x | | ESCAPED | | x | x | | | | | | | EVERY | x | | | x | | | | | | EXCEPT | x | x | x | x | | x | x | x | | EXCEPTION | | | | x | | | | | | EXCEPTION | | | | | | | x | | | EXCLUDING | | | | x | | | | | | EXCLUSIVE | | | | x | x | | | | | EXEC | x | | | | | x | x | | | EXECUTE | x | | | x | | x | x | | | EXISTS | x | x | x | x | x | x | x | x | | EXIT | | x | x | x | | x | | | | EXP | x | | | | | | | | | EXPLAIN | | x | x | x | | | | x | | EXTERNAL | x | | | x | | x | x | | | EXTRACT | x | | | x | | | x | | | FALSE | x | x | x | | | | x | | | FENCED | | | | x | | | | | | FETCH | x | x | x | x | | x | x | x | | FIELDPROC | | | | x | | | | | | FILE | | | | x | x | x | | | | FILLFACTOR | | | | | | x | | | | FILTER | x | | | | | | | | | FINAL | | | | x | | | | | | FIRST | | | | | | | x | | | FIRST_VALUE | x | | x | | | | | | | FLOAT | x | x | x | | x | | x | x | | FLOAT4 | | x | x | | | | | | | FLOAT8 | | x | x | | | | | | | FLOOR | x | | | | | | | | | FOR | x | x | x | x | x | x | x | x | | FORCE | | x | x | | | | | | | FOREIGN | x | x | x | x | | x | x | x | | FORTRAN | | | | | | | x | | | FOUND | | | | | | | x | | | FRAME_ROW | x | | | | | | | | | FREE | x | | | x | | | | | | FREETEXT | | | | | | x | | | | FREETEXTTABLE | | | | | | x | | | | FROM | x | x | x | x | x | x | x | x | | FULL | x | | | x | | x | x | | | FULLTEXT | | x | x | | | | | | | FUNCTION | x | | x | x | | x | | x | | FUSION | x | | | | | | | | | GENERAL | | x | | x | | | | | | GENERATED | | | x | x | | | | | | GET | x | | x | x | | | x | x | | GLOB | | | | | | | | x | | GLOBAL | x | | | x | | | x | | | GO | | | | x | | | x | | | GOTO | | | | x | | x | x | | | GRANT | x | x | x | x | x | x | x | x | | GRAPHIC | | | | x | | | | | | GROUP | x | x | x | x | x | x | x | x | | GROUPING | x | | x | | | | | | | GROUPS | x | | x | | | | | | | HANDLER | x | | | x | | | | | | HASH | | | | x | | | | | | HASHED_VALUE | | | | x | | | | | | HAVING | x | x | x | x | x | x | x | x | | HIGH_PRIORITY | | x | x | | | | | | | HINT | | | | x | | | | | | HOLD | x | | | x | | | | | | HOLDLOCK | | | | | | x | | | | HOUR | x | | | x | | | x | | | HOURS | | | | x | | | | | | HOUR_MICROSECOND | | x | x | | | | | | | HOUR_MINUTE | | x | x | | | | | | | HOUR_SECOND | | x | x | | | | | | | IDENTIFIED | | | | | x | | | | | IDENTITY | x | | | x | | x | x | | | IDENTITYCOL | | | | | | x | | | | IDENTITY_INSERT | | | | | | x | | | | IF | x | x | x | x | | x | | x | | IGNORE | | x | x | | | | | | | IGNORE_DOMAIN_IDS | | x | | | | | | | | IGNORE_SERVER_IDS | | x | | | | | | | | IMMEDIATE | | | | x | x | | x | x | | IN | x | x | x | x | x | x | x | x | | INCLUDE | | | | | | | x | | | INCLUDING | | | | x | | | | | | INCLUSIVE | | | | x | | | | | | INCREMENT | | | | x | x | | | | | INDEX | | x | x | x | x | x | x | x | | INDICATOR | x | | | x | | | x | | | INF | | | | x | | | | | | INFILE | | x | x | | | | | | | INFINITY | | | | x | | | | | | INHERIT | | | | x | | | | | | INITIAL | x | | | | x | | | | | INITIALLY | | | | | | | x | | | INNER | x | x | x | x | | x | x | x | | INOUT | x | x | x | x | | | | x | | INPUT | | | | | | | x | | | INSENSITIVE | x | x | x | x | | | x | x | | INSERT | x | x | x | x | x | x | x | x | | INT | x | x | x | | | | x | | | INT1 | | x | x | | | | | | | INT2 | | x | x | | | | | | | INT3 | | x | x | | | | | | | INT4 | | x | x | | | | | | | INT8 | | x | x | | | | | | | INTEGER | x | x | x | | x | | x | x | | INTEGRITY | | | | x | | | | | | INTERSECT | x | x | | x | x | x | x | x | | INTERSECTION | x | | | | | | | | | INTERVAL | x | x | x | | | | x | | | INTO | x | x | x | x | x | x | x | x | | IO_AFTER_GTIDS | | | x | | | | | | | IO_BEFORE_GTIDS | | | x | | | | | | | IS | x | x | x | x | x | x | x | x | | ISOBID | | | | x | | | | | | ISOLATION | | | | x | | | x | | | ITERATE | x | x | x | x | | | | x | | JAR | | | | x | | | | | | JAVA | | | | x | | | | | | JOIN | x | x | x | x | | x | x | x | | JSON_ARRAY | x | | | | | | | | | JSON_ARRAYAGG | x | | | | | | | | | JSON_EXISTS | x | | | | | | | | | JSON_OBJECT | x | | | | | | | | | JSON_OBJECTAGG | x | | | | | | | | | JSON_QUERY | x | | | | | | | | | JSON_TABLE | x | | x | | | | | | | JSON_TABLE_PRIMITIVE | x | | | | | | | | | JSON_VALUE | x | | | | | | | | | KEEP | | | | x | | | | | | KEY | | x | x | x | | x | x | | | KEYS | | x | x | | | | | | | KILL | | x | x | | | x | | | | LABEL | | | | x | | | | | | LAG | x | | x | | | | | | | LANGUAGE | x | | | x | | | x | | | LARGE | x | | | | | | | | | LAST | | | | | | | x | | | LAST_VALUE | x | | x | | | | | | | LATERAL | x | | | x | | | | | | LC_CTYPE | | | | x | | | | | | LEAD | x | | x | | | | | | | LEADING | x | x | x | | | | x | | | LEAVE | x | x | x | x | | | | x | | LEAVESLEFT | | | x | | | | | | | LEFT | x | x | | x | | x | x | x | | LEVEL | | | | | x | | x | | | LIKE | x | x | x | x | x | x | x | x | | LIKE_REGEX | x | | | | | | | | | LIMIT | | x | x | | | | | x | | LINEAR | | x | x | | | | | | | LINENO | | | | | | x | | | | LINES | | x | x | | | | | | | LINKTYPE | | | | x | | | | | | LN | x | | | | | | | | | LOAD | | x | x | | | x | | | | LOCAL | x | | | x | | | x | | | LOCALDATE | | | | x | | | | | | LOCALE | | | | x | | | | | | LOCALTIME | x | x | x | x | | | | x | | LOCALTIMESTAMP | x | x | x | x | | | | x | | LOCATOR | | | | x | | | | | | LOCATORS | | | | x | | | | | | LOCK | | x | x | x | x | | | | | LOCKMAX | | | | x | | | | | | LOCKSIZE | | | | x | | | | | | LONG | | x | x | x | x | | | | | LONGBLOB | | x | x | | | | | | | LONGTEXT | | x | x | | | | | | | LOOP | x | x | x | x | | | | x | | LOWER | x | | | | | | x | | | LOW_PRIORITY | | x | x | | | | | | | MAINTAINED | | | | x | | | | | | MASTER_BIND | | | x | | | | | | | MASTER_HEARTBEAT_PERIOD | | x | | | | | | | | MASTER_SSL_VERIFY_SERVER_CERT | | x | x | | | | | | | MATCH | x | x | x | | | | x | x | | MATCHES | x | | | | | | | | | MATCH_NUMBER | x | | | | | | | | | MATCH_RECOGNIZE | x | | | | | | | | | MATERIALIZED | | | | x | | | | | | MAX | x | x | | | | | x | | | MAXEXTENTS | | | | | x | | | | | MAXVALUE | | x | x | x | | | | | | MEDIUMBLOB | | x | x | | | | | | | MEDIUMINT | | x | x | | | | | | | MEDIUMTEXT | | x | x | | | | | | | MEMBER | x | | | | | | | | | MERGE | x | | | | | x | | | | METHOD | x | | | | | | | | | MICROSECOND | | | | x | | | | | | MICROSECONDS | | | | x | | | | | | MIDDLEINT | | x | x | | | | | | | MIN | x | | | | | | x | | | MINUS | | | | | x | | | | | MINUTE | x | | | x | | | x | | | MINUTES | | | | x | | | | | | MINUTE_MICROSECOND | | x | x | | | | | | | MINUTE_SECOND | | x | x | | | | | | | MINVALUE | | | | x | | | | | | MLSLABEL | | | | | x | | | | | MOD | x | x | x | | | | | | | MODE | | | | x | x | | | | | MODEMODIFIES | | | x | | | | | | | MODIFIES | x | x | | x | | | | | | MODIFY | | | | | x | | | | | MODULE | x | | | | | | x | | | MONTH | x | | | x | | | x | | | MONTHS | | | | x | | | | | | MULTISET | x | | | | | | | | | NAMES | | | | | | | x | | | NAN | | | | x | | | | | | NATIONAL | x | | | | | x | x | | | NATURAL | x | x | x | | | | x | x | | NCHAR | x | | | | | | x | | | NCLOB | x | | | | | | | | | NESTED_TABLE_ID | | | | | x | | | | | NEW | x | | | x | | | | | | NEW_TABLE | | | | x | | | | | | NEXT | | | | | | | x | | | NEXTVAL | | | | x | | | | | | NO | x | | | x | | | x | | | NOAUDIT | | | | | x | | | | | NOCACHE | | | | x | | | | | | NOCHECK | | | | | | x | | | | NOCOMPRESS | | | | | x | | | | | NOCYCLE | | | | x | | | | | | NODENAME | | | | x | | | | | | NODENUMBER | | | | x | | | | | | NOMAXVALUE | | | | x | | | | | | NOMINVALUE | | | | x | | | | | | NONCLUSTERED | | | | | | x | | | | NONE | x | | | x | | | x | | | NOORDER | | | | x | | | | | | NORMALIZE | x | | | | | | | | | NORMALIZED | | | | x | | | | | | NOT | x | x | x | x | x | x | x | x | | NOTNULL | | | | | | | | x | | NOWAIT | | | | | x | | | | | NO_WRITE_TO_BINLOG | | x | x | | | | | | | NTH_VALUE | x | | x | | | | | | | NTILE | x | | x | | | | | | | NULL | x | x | x | x | x | x | x | x | | NULLIF | x | | | | | x | x | | | NULLS | | | | x | | | | | | NUMBER | | | | | x | | | | | NUMERIC | x | x | x | | | | x | | | NUMPARTS | | | | x | | | | | | OBID | | | | x | | | | | | OCCURRENCES_REGEX | x | | | | | | | | | OCTET_LENGTH | x | | | | | | x | | | OF | x | | x | x | x | x | x | x | | OFF | | | | | | x | | | | OFFLINE | | | | | x | | | | | OFFSET | x | | | | | | | | | OFFSETS | | | | | | x | | | | OLD | x | | | x | | | | | | OLD_TABLE | | | | x | | | | | | OMIT | x | | | | | | | | | ON | x | x | x | x | x | x | x | x | | ONE | x | | | | | | | | | ONLINE | | | | | x | | | | | ONLY | x | | | | | | x | | | OPEN | x | | | x | | x | x | | | OPENDATASOURCE | | | | | | x | | | | OPENQUERY | | | | | | x | | | | OPENROWSET | | | | | | x | | | | OPENXML | | | | | | x | | | | OPTIMIZATION | | | | x | | | | | | OPTIMIZE | | x | x | x | | | | | | OPTIMIZER_COSTS | | | x | | | | | | | OPTION | | x | x | x | x | x | x | | | OPTIONALLY | | x | x | | | | | | | OR | x | x | x | x | x | x | x | x | | ORDER | x | x | x | x | x | x | x | x | | OUT | x | x | x | x | | | | x | | OUTER | x | x | x | x | | x | x | x | | OUTFILE | | x | x | | | | | | | OUTPUT | | | | | | | x | | | OVER | x | x | x | x | | x | | x | | OVERLAPS | x | | | | | | x | | | OVERLAY | x | | | | | | | | | OVERRIDING | | | | x | | | | | | PACKAGE | | | | x | | | | | | PAD | | | | | | | x | | | PADDED | | | | x | | | | | | PAGESIZE | | | | x | | | | | | PAGE_CHECKSUM | | x | | | | | | | | PARAMETER | x | | | x | | | | | | PARSE_VCOL_EXPR | | x | | | | | | | | PART | | | | x | | | | | | PARTIAL | | | | | | | x | | | PARTITION | x | x | x | x | | | | x | | PARTITIONED | | | | x | | | | | | PARTITIONING | | | | x | | | | | | PARTITIONS | | | | x | | | | | | PASCAL | | | | | | | x | | | PASSWORD | | | | x | | | | | | PATH | | | | x | | | | | | PATTERN | x | | | | | | | | | PCTFREE | | | | | x | | | | | PER | x | | | | | | | | | PERCENT | x | | | | | x | | | | PERCENTILE_CONT | x | | | | | | | | | PERCENTILE_DISC | x | | | | | | | | | PERCENT_RANK | x | | x | | | | | | | PERIOD | x | | | | | | | | | PERSIST | | | x | | | | | | | PERSIST_ONLY | | | x | | | | | | | PIECESIZE | | | | x | | | | | | PIVOT | | | | | | x | | | | PLAN | | | | x | | x | | | | PORTION | x | | | | | | | | | POSITION | x | | | x | | | x | | | POSITION_REGEX | x | | | | | | | | | POWER | x | | | | | | | | | PRAGMA | | | | | | | | x | | PRECEDES | x | | | | | | | | | PRECISION | x | x | x | x | | x | x | x | | PREPARE | x | | | x | | | x | | | PRESERVE | | | | | | | x | | | PREVVAL | | | | x | | | | | | PRIMARY | x | x | x | x | | x | x | x | | PRINT | | | | | | x | | | | PRIOR | | | | | x | | x | | | PRIQTY | | | | x | | | | | | PRIVILEGES | | | | x | | | x | | | PROC | | | | | | x | | | | PROCEDURE | x | x | x | x | | x | x | x | | PROGRAM | | | | x | | | | | | PSID | | | | x | | | | | | PUBLIC | | | | x | x | x | x | | | PURGE | | x | x | | | | | | | QUERY | | | | x | | | | | | QUERYNO | | | | x | | | | | | RAISERROR | | | | | | x | | | | RANGE | x | x | x | x | | | | x | | RANK | x | | x | x | | | | x | | RAW | | | | | x | | | | | READ | | x | x | x | | x | x | | | READS | x | x | x | x | | | | x | | READTEXT | | | | | | x | | | | READ_WRITE | | x | x | | | | | | | REAL | x | x | x | | | | x | | | RECONFIGURE | | | | | | x | | | | RECOVERY | | | | x | | | | | | RECURSIVE | x | x | x | | | | | x | | REF | x | | | | | | | | | REFERENCES | x | x | x | x | | x | x | x | | REFERENCING | x | | | x | | | | | | REFRESH | | | | x | | | | | | REF_SYSTEM_ID | | x | | | | | | | | REGEXP | | x | x | | | | | x | | REGR_AVGX | x | | | | | | | | | REGR_AVGY | x | | | | | | | | | REGR_COUNT | x | | | | | | | | | REGR_INTERCEPT | x | | | | | | | | | REGR_R2 | x | | | | | | | | | REGR_SLOPE | x | | | | | | | | | REGR_SXX | x | | | | | | | | | REGR_SXY | x | | | | | | | | | REGR_SYY | x | | | | | | | | | REINDEX | | | | | | | | x | | RELATIVE | | | | | | | x | | | RELEASE | x | x | x | x | | | | x | | RENAME | | x | x | x | x | | | x | | REPEAT | x | x | x | x | | | | x | | REPEATABLEREPLACE | | | x | | | | | | | REPLACE | | x | | | | | | x | | REPLICATION | | | | | | x | | | | REQUIRE | | x | x | | | | | | | RESET | | | | x | | | | | | RESIGNAL | x | x | x | x | | | | x | | RESOURCE | | | | | x | | | | | RESTART | | | | x | | | | | | RESTORE | | | | | | x | | | | RESTRICT | | x | x | x | | x | x | | | RESULT | x | | | x | | | | | | RESULT_SET_LOCATOR | | | | x | | | | | | RETURN | x | x | x | x | | x | | x | | RETURNING | | x | | | | | | | | RETURNS | x | | | x | | | | | | REVERT | | | | | | x | | | | REVOKE | x | x | x | x | x | x | x | x | | RIGHT | x | x | x | x | | x | x | x | | RLIKE | | x | x | | | | | | | ROLE | | | | x | | | | | | ROLLBACK | x | | | x | | x | x | x | | ROLLUP | x | | | | | | | | | ROUND_CEILING | | | | x | | | | | | ROUND_DOWN | | | | x | | | | | | ROUND_FLOOR | | | | x | | | | | | ROUND_HALF_DOWN | | | | x | | | | | | ROUND_HALF_EVEN | | | | x | | | | | | ROUND_HALF_UP | | | | x | | | | | | ROUND_UP | | | | x | | | | | | ROUTINE | | | | x | | | | | | ROW | x | | x | x | x | | | x | | ROWCOUNT | | | | | | x | | | | ROWGUIDCOL | | | | | | x | | | | ROWID | | | | | x | | | | | ROWNUM | | | | | x | | | | | ROWNUMBER | | | | x | | | | | | ROWS | x | x | x | x | x | | x | x | | ROWSET | | | | x | | | | | | ROW_NUMBER | x | | x | x | | | | x | | RRN | | | | x | | | | | | RULE | | | | | | x | | | | RUN | | | | x | | | | | | RUNNING | x | | | | | | | | | SAVE | | | | | | x | | | | SAVEPOINT | x | | | x | | | | x | | SCHEDULESCHEMA | | | x | | | | | | | SCHEMA | | x | | x | | x | x | | | SCHEMAS | | x | x | | | | | | | SCOPE | x | | | | | | | | | SCRATCHPAD | | | | x | | | | | | SCROLL | x | | | x | | | x | | | SEARCH | x | | | x | | | | | | SECOND | x | | | x | | | x | | | SECONDS | | | | x | | | | | | SECOND_MICROSECOND | | x | x | | | | | | | SECQTY | | | | x | | | | | | SECTION | | | | | | | x | | | SECURITY | | | | x | | | | | | SECURITYAUDIT | | | | | | x | | | | SEEK | x | | | | | | | | | SELECT | x | x | x | x | x | x | x | x | | SEMANTICKEYPHRASETABLE | | | | | | x | | | | SEMANTICSIMILARITYDETAILSTABLE | | | | | | x | | | | SEMANTICSIMILARITYTABLE | | | | | | x | | | | SENSITIVE | x | x | x | x | | | | x | | SEPARATOR | | x | x | | | | | | | SEQUENCE | | | | x | | | | | | SESSION | | | | x | x | | x | | | SESSION_USER | x | | | x | | x | x | | | SET | x | x | x | x | x | x | x | x | | SETUSER | | | | | | x | | | | SHARE | | | | | x | | | | | SHOW | x | x | x | | | | | | | SHUTDOWN | | | | | | x | | | | SIGNAL | x | x | x | x | | | | x | | SIMILAR | x | | | | | | | | | SIMPLE | | | | x | | | | | | SIZE | | | | | x | | x | | | SKIP | x | | | | | | | | | SLOW | | x | | | | | | | | SMALLINT | x | x | x | | x | | x | x | | SNAN | | | | x | | | | | | SOME | x | | | x | | x | x | | | SOURCE | | | | x | | | | | | SPACE | | | | | | | x | | | SPATIAL | | x | x | | | | | | | SPECIFIC | x | x | x | x | | | | x | | SPECIFICTYPE | x | | | | | | | | | SQL | x | x | x | x | | | x | x | | SQLCA | | | | | | | x | | | SQLCODE | | | | | | | x | | | SQLERROR | | | | | | | x | | | SQLEXCEPTION | x | x | x | | | | | | | SQLID | | | | x | | | | | | SQLSTATE | x | x | x | | | | x | | | SQLWARNING | x | x | x | | | | x | | | SQL_BIG_RESULT | | x | x | | | | | | | SQL_CALC_FOUND_ROWS | | x | x | | | | | | | SQL_SMALL_RESULT | | x | x | | | | | | | SQRT | x | | | | | | | | | SSL | | x | x | | | | | | | STACKED | | | | x | | | | | | STANDARD | | | | x | | | | | | START | x | | | x | x | | | x | | STARTING | | x | x | x | | | | | | STATEMENT | | | | x | | | | | | STATIC | x | | | x | | | | | | STATISTICS | | | | | | x | | | | STATMENT | | | | x | | | | | | STATS_AUTO_RECALC | | x | | | | | | | | STATS_PERSISTENT | | x | | | | | | | | STATS_SAMPLE_PAGES | | x | | | | | | | | STAY | | | | x | | | | | | STDDEV_POP | x | | | | | | | | | STDDEV_SAMP | x | | | | | | | | | STOGROUP | | | | x | | | | | | STORED | | | x | | | | | | | STORES | | | | x | | | | | | STRAIGHT_JOIN | | x | x | | | | | | | STYLE | | | | x | | | | | | SUBMULTISET | x | | | | | | | | | SUBSET | x | | | | | | | | | SUBSTRING | x | | | x | | | x | | | SUBSTRING_REGEX | x | | | | | | | | | SUCCEEDS | x | | | | | | | | | SUCCESSFUL | | | | | x | | | | | SUM | x | | | | | | x | | | SUMMARY | | | | x | | | | | | SYMMETRIC | x | | | | | | | | | SYNONYM | | | | x | x | | | | | SYSDATE | | | | | x | | | | | SYSFUN | | | | x | | | | | | SYSIBM | | | | x | | | | | | SYSPROC | | | | x | | | | | | SYSTEM | x | | x | x | | | | x | | SYSTEM_TIME | x | | | | | | | | | SYSTEM_USER | x | | | x | | x | x | | | SYS_* | | | | | x | | | | | TABLE | x | x | x | x | x | x | x | x | | TABLESAMPLE | x | | | | | x | | | | TABLESPACE | | | | x | | | | | | TEMPORARY | | | | | | | x | | | TERMINATED | | x | x | | | | | | | TEXTSIZE | | | | | | x | | | | THEN | x | x | x | x | x | x | x | x | | TIME | x | | | x | | | x | | | TIMESTAMP | x | | | x | | | x | | | TIMEZONE_HOUR | x | | | | | | x | | | TIMEZONE_MINUTE | x | | | | | | x | | | TINYBLOB | | x | x | | | | | | | TINYINT | | x | x | | | | | | | TINYTEXT | | x | x | | | | | | | TO | x | x | x | x | x | x | x | x | | TOP | | | | | | x | | | | TRAILING | x | x | x | | | | x | | | TRAN | | | | | | x | | | | TRANSACTION | | | | x | | x | x | x | | TRANSLATE | x | | | | | | x | | | TRANSLATE_REGEX | x | | | | | | | | | TRANSLATION | x | | | | | | x | | | TREAT | x | | | | | | | | | TRIGGER | x | x | x | x | x | x | | x | | TRIM | x | | | x | | | x | | | TRIM_ARRAY | x | | | | | | | | | TRUE | x | x | x | | | | x | | | TRUNCATE | x | | | x | | x | | | | TRY_CONVERT | | | | | | x | | | | TSEQUAL | | | | | | x | | | | TYPE | | | | x | | | | | | UESCAPE | x | | | | | | | | | UID | | | | | x | | | | | UNDO | | x | x | x | | | | | | UNION | x | x | x | x | x | x | x | x | | UNIQUE | x | x | x | x | x | x | x | x | | UNKNOWN | x | | | | | | x | | | UNLOCK | | x | x | | | | | | | UNNEST | x | | | | | | | | | UNPIVOT | | | | | | x | | | | UNSIGNED | | x | x | | | | | | | UNTIL | x | | | x | | | | | | UPDATE | x | x | x | x | x | x | x | x | | UPDATETEXT | | | | | | x | | | | UPPER | x | | | | | | x | | | USAGE | | x | x | x | | | x | | | USE | | x | x | | | x | | | | USER | x | | | x | x | x | x | x | | USING | x | x | x | x | | | x | x | | UTC_DATE | | x | x | | | | | | | UTC_TIME | | x | x | | | | | | | UTC_TIMESTAMP | | x | x | | | | | | | VALIDATE | | | | | x | | | | | VALIDPROC | | | | x | | | | | | VALUE | x | | | x | | | x | | | VALUES | x | x | x | x | x | x | x | x | | VALUE_OF | x | | | | | | | | | VARBINARY | x | x | x | | | | | | | VARCHAR | x | x | x | | x | | x | x | | VARCHAR2 | | | | | x | | | | | VARCHARACTER | | x | x | | | | | | | VARIABLE | | | | x | | | | | | VARIANT | | | | x | | | | | | VARYING | x | x | x | | | x | x | | | VAR_POP | x | | | | | | | | | VAR_SAMP | x | | | | | | | | | VCAT | | | | x | | | | | | VERSION | | | | x | | | | | | VERSIONING | x | | | | | | | | | VIEW | | | | x | x | x | x | x | | VIRTUAL | | | x | | | | | | | VOLATILE | | | | x | | | | | | VOLUMES | | | | x | | | | | | WAITFOR | | | | | | x | | | | WHEN | x | x | x | x | | x | x | x | | WHENEVER | x | | | x | x | | x | x | | WHERE | x | x | x | x | x | x | x | x | | WHILE | x | x | x | x | | x | | x | | WIDTH_BUCKET | x | | | | | | | | | WINDOW | x | x | x | | | | | | | WITH | x | x | x | x | x | x | x | x | | WITHIN | x | | | | | | | | | WITHIN GROUP | | | | | | x | | | | WITHOUT | x | | | x | | | | x | | WLM | | | | x | | | | | | WORK | | | | | | | x | | | WRITE | | x | x | x | | | x | | | WRITETEXT | | | | | | x | | | | XMLELEMENT | | | | x | | | | | | XMLEXISTS | | | | x | | | | | | XMLNAMESPACES | | | | x | | | | | | XOR | | x | x | | | | | | | YEAR | x | | | x | | | x | | | YEARS | | | | x | | | | | | YEAR_MONTH | | x | x | | | | | | | ZEROFILL | | x | x | | | | | | | ZONE | | | | | | | x | | +----------------------------------+-----+-----+-----+-----+-----+-----+-----+-----+

This Week in Data with Colin Charles 13: MariaDB, M18 and YugaByte

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

This week we saw the closing of the CFPs for MariaDB’s user conference, M18. Peter Zaitsev and I submitted for Percona (don’t forget that there is also a developer’s conference tacked on to this event). We don’t have high expectations of getting a talk there, but hey – you never know! I submitted a talk on running MariaDB in the cloud (either hosted on Amazon RDS or Rackspace) or in your own compute instance. Another talk on capacity planning seemed to make sense as well.

An event Percona should be at is SCALE 16x, though I’ll admit I’m a bit surprised to see that there isn’t a dedicated MySQL track this year (the PostgreSQL track is still there).

In other news, we’ve seen a new database company gain funding: YugaByte. They received $8 million, and their leadership tends to be ex-Facebook people who have worked on Apache HBase.

MariaDB Corporation raised a total of $54m in 2017, with $27 being led by Alibaba Group in their latest round (completing the Series C funding round). The Reg reports claims that MariaDB has access to more than 60 million devs (is this number even higher than the MySQL developers out there?). The Techcrunch take. There hasn’t been any reporting of this in mainstream press like the Wall Street Journal or the Financial Times.

Releases Link List Feedback

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Insert benchmark, IO-bound, Intel NUC: MyRocks vs InnoDB

This post explains the IO-bound insert benchmark on Intel NUC servers with results for MyRocks and InnoDB. The previous post explained an in-memory insert benchmark.

tl;dr
  • MyRocks gets better insert rates while using less HW per insert: ~8X faster insert rates on the i3 NUC and ~5X faster on the i5 NUC.
  • InnoDB writes ~20X more to storage per insert compared to MyRocks.
  • Uncompressed InnoDB uses ~1.6X more space than uncompressed MyRocks
  • MyRocks is up to 1.3X slower than InnoDB for secondary index scans and up to 1.7X slower for PK scans. The gap is larger for PK indexes because InnoDB secondary indexes suffer from fragmentation in this test. MyRocks suffers from using more CPU per row, so it can't drive storage as fast as InnoDB. 
  • InnoDB suffers more write stalls on the read-write test
  • MyRocks does much better for reads on the read-write tests because it is able to cache the secondary indexes. This is a benefit of being more space efficient. In the future I need to make the database larger to avoid comparing cached MyRocks vs not-cached InnoDB on this benchmark.

Configuration

The tests used a MyRocks build from FB MySQL 5.6.35 and August 15 with git hash 0d76ae and then upstream InnoDB 5.6.35 and 5.7.17. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. The i5 NUC has more RAM, faster CPUs and faster storage than the i3 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7,  use the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention on the small servers. For all tests the binlog was enabled but fsync was disabled for the binlog and I set flush_log_at_trx_commit=2 for both engines. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

The database is larger than RAM. The i3 NUC has 8gb of RAM and the i5 NUC has 16gb. The insert benchmark used 1 table and loaded 250M rows on the i3 NUC and 500M rows on the i5 NUC, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

The MyRocks tests were done without compression and with zstd compression for the max LSM level. These are labeled MyRocks.none and MyRocks.zstd below.

Results

All of the data for the graphs is here. Note that Linux iostat can overstate bytes written by RocksDB by 2X because it counts bytes trimmed as bytes written. The data in github does not adjust for that. The numbers here for wKB/i does by dividing the measured value by 2 -- which is an estimate. Note that even if my adjustment were exact this value is still missing bytes written by flash GC and when I measured that in the past it was much worse for InnoDB.

Results: load

This section has results for the load.

This graph has the insert rate for each engine relative to the rate for MyRocks. I am not surprised that MyRocks has a better insert rate than InnoDB because it does less work per insert than InnoDB. MyRocks secondary index maintenance is read free and MyRocks has much less write amplification. For the in-memory insert benchmark there is a regression in the insert rate for InnoDB from 5.6 to 5.7 but that does not occur here.

These tables have more data for the i3 NUC including the average insert rate (IPS), the CPU overhead per insert, CPU/i, KB written to and read from storage per insert, wKB/i & rKB/i, and the size of the database when the load finishes, size(GB). The first table has the absolute values and the second the values relative to MyRocks without compression (MyRocks.none). By all metrics MyRocks is more efficient with hardware:
  • InnoDB writes ~20X more to storage per insert which helps my SSD devices last longer.
  • InnoDB reads between 10X and 20X more from storage per insert because it reads leaf pages for secondary index maintenance while MyRocks reads for compaction but secondary index maintenance is read free
  • InnoDB uses more CPU per insert.
  • InnoDB uses ~1.6X more space than MyRocks when neither use compression. InnoDB suffers from B-Tree fragmentation for the secondary indexes and MyRocks benefits from key prefix compression.

                Absolute values
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    22122   2699     1.79   0.25    28
MyRocks.zstd    20169   3243     1.36   0.14    13
InnoDB-5.6       2665   4934    39.08   6.10    46
InnoDB-5.7       3242   3526    34.24   2.54    46

                Values relative to MyRocks
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    1.00    1.00     1.00    1.00   1.00
MyRocks.zstd    0.91    1.20     0.76    0.56   0.46
InnoDB-5.6      0.12    1.83    21.83   24.40   1.64
InnoDB-5.7      0.15    1.31    19.13   10.16   1.64

These tables have more data for the i5 NUC. The insert rate difference is smaller here than for the i3 NUC. InnoDB suffers more from the slower storage device on the i3 NUC. Similar to the i3 NUC, InnoDB writes ~20X more to storage per insert and ~1.6X more space compared to uncompressed MyRocks.

                Absolute values
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    26837   2278     1.75   0.12    56
MyRocks.zstd    24811   2770     1.52   0.06    25
InnoDB-5.6       5978   4265    44.50   2.10    90
InnoDB-5.7       5634   4690    44.92   1.26    90

                Values relative to MyRocks
                IPS     CPU/i   wKB/i   rKB/i   size(GB)
MyRocks.none    1.00    1.00     1.00    1.00   1.00
MyRocks.zstd    0.92    1.22     0.87    0.50   0.45
InnoDB-5.6      0.22    1.87    25.43   17.50   1.61
InnoDB-5.7      0.21    2.06    25.67   10.50   1.61

Results: scan

This section has results for the index scans. There are 5 scans: 1 is the PK, 2/3/4 are the secondary indexes and then 5 is the PK again. The 2+3+4 column has the time to scan all secondary indexes. The gap between MyRocks and InnoDB is much smaller here than it was on the in-memory full index scan. I assume the CPU overhead from MyRocks is less significant here where the scan is IO-bound.

I wrote above that I used a MyRocks build from August 15. Unfortunately for the scan test I used one for June 16 with git hash 52e058.

The result below includes results (MyRocks.none.ra, MyRocks.zstd.ra) that used a RocksDB option to make it more likely that filesystem readahead would be enabled. The option is rocksdb_advise_random_on_open and when set to 0 RocksDB uses POSIX_FADV_NORMAL rather than POSIX_FADV_RANDOM when opening SST files for user reads. I don't think this option is ready for production, but it can be used on benchmarks.

Some notes from the results:
  • Full-index scans are faster with InnoDB. The gap is larger for the PK index because InnoDB secondary indexes suffer from fragmentation on this test. MyRocks suffers from using more CPU per row, so it can't drive storage as fast as InnoDB. MyRocks is up to 1.3X slower than InnoDB for secondary index scans and up to 1.7X slower for PK scans.
  • The 2+3+4 result for MyRocks is close to the time for InnoDB in MySQL 5.6 when MyRocks uses filesystem readahead. I assume we can do more to get better readahead. Index scans on a fragmented B-Tree generate a lot of random reads. A full index scan from an LSM can do large reads from each LSM level to avoid that.
  • Something was done for InnoDB in MySQL 5.7 to make range scans faster. Thank you to the InnoDB team.
#seconds for each index scan
 1      2       3       4       5       2+3+4   index/engine
 219    202     222     224     207      648    MyRocks.none
 182    189     203     197     168      589    MyRocks.none.ra
 241    206     230     228     230      664    MyRocks.zstd
 241    195     215     212     201      622    MyRocks.zstd.ra
 292    181     194     212     127      587    InnoDB-5.6
 419    148     157     203      99      508    InnoDB-5.7

Results for the i5 NUC are similar to the i3 NUC.

#seconds for each index scan  1      2       3       4       5       2-4     index/engine  394    345     381     366     342     1092    MyRocks.none  331    318     344     331     285      993    MyRocks.none.ra  385    346     383     386     372     1115    MyRocks.zstd  359    327     360     356     341     1043    MyRocks.zstd.ra  475    290     302     314     225      906    InnoDB-5.6  417    250     267     249     164      766    InnoDB-5.7
Next are hardware efficiency metrics that help explain the performance differences. There are many interesting things in here. The columns are explained here.
  • InnoDB gets more read IO throughput than MyRocks. I need to understand this better but part of the problem is that MyRocks uses more CPU per row so it isn't able to consume data from storage as fast as InnoDB.
  • The InnoDB PK scan gets more read IO throughput than the InnoDB secondary index scan because the secondary index is fragmented and the PK is not courtesy of the workload (PK inserts are in PK order, secondary index inserts are not).
  • Filesystem readahead gets ~10% more read IO throughput for MyRocks.
  • InnoDB in MySQL 5.7 uses much less CPU than in MySQL 5.6 (thanks InnoDB team). I have yet to confirm whether the work done to make InnoDB scans faster in 5.7 are InnoDB-only or will benefit other engines.
  • The gap between InnoDB and MyRocks is larger for the PK scan than the secondary index scan. This might occur because of secondary index fragmentation for InnoDB.
  • The CPU overhead from zstd compression for MyRocks is small.

Metrics for q4: the scan of a secondary index
secs    rMB/s   rKB/o   rGB     MCPU/o  engine 366     32.3    0.024   12      16.872  MyRocks.none 331     35.7    0.024   12      17.044  MyRocks.none.ra 386     13.1    0.010    5      18.684  MyRocks.zstd 356     14.0    0.010    5      17.764  MyRocks.zstd.ra 314     44.2    0.028   13      10.748  InnoDB-5.6 249     56.0    0.028   14       7.592  InnoDB-5.7
Metrics for q5 - the second scan of the PK secs    rMB/s   rKB/o   rGB     MCPU/o 342      72.7   0.051   24      16.064  MyRocks.none 285      87.3   0.051   24      14.612  MyRocks.none.ra 372      30.6   0.023   11      17.820  MyRocks.zstd 341      33.3   0.023   11      17.396  MyRocks.zstd.ra 225     147.0   0.067   32      14.732  InnoDB-5.6 164     198.1   0.067   32       8.900  InnoDB-5.6
And a graph of the results from the 2+3+4 column because some readers like graphs.
Results: read-write, 1000 writes/second

Disclaimer - MyRocks is able to cache the secondary indexes during this test while InnoDB is not. I didn't plan this and in the future I need to prevent that from happening. While being more efficient with space is a benefit of MyRocks, even without compression, there is a step function in performance when the database is cached that distorts performance differences. MyRocks needs less space for secondary indexes because it doesn't suffer fragmentation like a B-Tree and it uses key prefix compression.

This section has results for the read-write tests where the writer does 1000 inserts/second. The tables below have the most interesting data. The  metrics are explained here. All of the engines were able to sustain the target insert rate on average (ips.av). The value is 999 rather than 1000 because of implementation artifacts. There were more write stalls on InnoDB than MyRocks based on the 99th percentile insert rate as ips.99 is lower for InnoDB. QPS is much better for MyRocks but see the disclaimer above. InnoDB writes ~20X more to storage per insert compared to MyRocks.

                Absolute values for the i3 NUC
                IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    999     998      1.30   3349    2377      0.37   7912
MyRocks.zstd    999     998      1.70   3450    2506      0.25   7986
InnoDB-5.6      999     977     26.20    258     156    101.61  37834
InnoDB-5.7      999     977     30.70    311     238     98.68  26829

                Absolute values for the i5 NUC
                IPS.av  IPS.99  wKB/i   QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    999     998      2.23   3818    2395     0.47    6945
MyRocks.zstd    999     998      1.96   3986    2858     0.28    6788
InnoDB-5.6      999     987     73.09    992     626    59.45   16798
InnoDB-5.7      999     988     71.22   1042     738    61.07   16115

Results: read-write, 100 writes/second

See the disclaimer in the previous section.

This section has results for the read-write tests where the writer does 100 inserts/second. The metrics are explained here. The analysis from the previous section is relevant here.

                Absolute values for the i3 NUC
                IPS.av  IPS.99  QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    100     100     3419    3184     0.01    7173
MyRocks.zstd    100     100     3549    3270     0.01    6969
InnoDB-5.6      100     100      827     534    66.49   13675
InnoDB-5.7      100     100      900     566    69.56   10269

                Absolute values for the i5 NUC
                IPS.av  IPS.99  QPS.av  QPS.99  rKB/q   CPU/q
MyRocks.none    100     100     3911    3740     0       6202
MyRocks.zstd    100     100     4090    3841     0.01    5938
InnoDB-5.6      100     100     1334     951    50.42   11546
InnoDB-5.7      100     100     1383     996    52.50   10807

I did not include a graph. See the disclaimer above.


Percona Monitoring and Management 1.4.1 Is Now Available

Percona announces the release of Percona Monitoring and Management 1.4.1 on Thursday, November 2nd, 2017. This release contains fixes to bugs found after Percona Monitoring and Management 1.4.0 was released. It also introduces two important improvements. We replaced the btrfs file system with XFS in AMI and OVF images, and the Prometheus dashboard has been enhanced to offer more information.

For install and upgrade instructions, see Deploying Percona Monitoring and Management.

Improvements
  • PMM-1567: The btrfs file system has been replaced with XFS in AMI and OVF images to meet the requirements of AWS Marketplace.

  • PMM-1594: In Metrics Monitor, the Prometheus dashboard has been updated to show more information about the running Prometheus jobs and help estimate their efficiency.

Bug fixes
  • PMM-1620: In some cases, PMM could not be upgraded to version 1.4.0 by using the Update button on the landing page.

  • PMM-1633QAN would show error List of Tables is Empty for instances having been upgraded from earlier releases of PMM, due to incorrect values being stored in the database. This has been addressed to identify the incorrect values and replace with accurate schema and table information.

  • PMM-1634: The Advanced Data Exploration dashboard did not always display data points from external:metrics monitoring services due to a too restrictive Grafana Template filter.

  • PMM-1636: Special characters prevented the removal of external:metrics services using the pmm-admin remove command.

Insert benchmark, in-memory, Intel NUC: MyRocks vs InnoDB

I repeated my performance tests using more storage engines, newer versions of MyRocks and possibly better my.cnf settings. This post explains the in-memory insert benchmark on Intel NUC servers with results for MyRocks and InnoDB.

tl;dr
  • MyRocks sustains higher insert rates
  • InnoDB writes about 10X more per insert during the load and more than 30X per insert during one of the read-write tests compared to MyRocks. Better write efficiency with MyRocks means that SSD devices last longer and I replaced many SSDs this year.
  • Index scans are about 2X faster on InnoDB compared to MyRocks.
  • InnoDB suffers more write stalls during the read-write test on the i3 NUC
  • InnoDB gets more read QPS during the read-write test compared to MyRocks but there is a regression from 5.6 to 5.7

Configuration

The tests used a MyRocks build from FB MySQL 5.6.35 and August 15 with git hash 0d76ae and then upstream InnoDB 5.6.35 and 5.7.17. All tests used jemalloc with mysqld. The i3 and i5 NUC servers are described here. The insert benchmark is described here. The my.cnf files are here for the i3 NUC and i5 NUC. I tried to tune my.cnf for all engines including: disabled SSL for MySQL 5.7, use the same charset and collation, set innodb_purge_threads=1 to reduce mutex contention on the small servers. For all tests the binlog was enabled but fsync was disabled for the binlog and I set flush_log_at_trx_commit=2 for both engines. I compiled all of the MySQL versions on the test servers and did not use binaries from upstream.

In this test the database is cached by MyRocks and InnoDB. The insert benchmark loaded 10M rows into one table, then did a full scan of each index on the table (PK, 3 secondary, PK again), then two read-write tests. The first read-write test tries to insert 1000 rows/second with one client while the other client does short range scans as fast as possible. The second read-write test is similar except the insert rate limit is 100/second.

Results

All of the data for the graphs is here. Note that Linux iostat can overstate bytes written by RocksDB by 2X because it counts bytes trimmed as bytes written. The data in github does not adjust for that. The numbers here for wKB/i does by dividing the measured value by 2 -- which is an estimate. Note that even if my adjustment were exact this value is still missing bytes written by flash GC and when I measured that in the past it was much worse for InnoDB.

Results: load

This section has results for the load.

This graph has the insert rate for each engine relative to the rate for MyRocks. I am not surprised that MyRocks has a better insert rate than InnoDB because it does less work per insert than InnoDB. MyRocks secondary index maintenance is read free and MyRocks has much less write amplification. There is a regression in the insert rate for InnoDB from 5.6 to 5.7 and I will cover that in detail in another post.
These tables have more data for the i3 NUC including the average insert rate (IPS), the CPU overhead per insert (CPU/i) and KB written to storage per insert (wKB/i). The first table has the absolute values and the second the values relative to MyRocks. InnoDB writes ~10X more to storage per insert compared to MyRocks. I appreciate that MyRocks helps my SSD devices last longer. The variance in CPU/i values are harder to explain but the CPU overhead includes foreground and background processing as well as anything else running on the test server.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         24570   2004     0.78
InnoDB-5.6      18797   1737     8.87
InnoDB-5.7      15083   3497    10.36

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.77    0.87    11.37
InnoDB-5.7      0.61    1.75    13.28

These tables have more data for the i5 NUC. There is less difference between the engines here than on the i3 NUC. But InnoDB continues to write more than 10X to storage per insert compared to MyRocks.

                Absolute values
                IPS     CPU/i   wKB/i
MyRocks         31847   1526     0.77
InnoDB-5.6      29586   1461     8.06
InnoDB-5.7      25907   1491     9.66

                Values relative to MyRocks
                IPS     CPU/i   wKB/i
MyRocks         1.00    1.00     1.00
InnoDB-5.6      0.93    0.96    10.47
InnoDB-5.7      0.81    0.98    12.55

Results: scan

This section has results for the index scans. There are 5 scans: 1 is the PK, 2/3/4 are the secondary indexes and then 5 is the PK again. The 2+3+4 column has the time to scan all secondary indexes. This time is about 2X worse for MyRocks and we expect to make that better.  The PK scan is also slower I will write more about that in another post.

Index scans are slower on MyRocks than InnoDB. I assume there is more CPU overhead from merging iterators from different levels of the LSM tree. In this case all data is cached by the database so there are no reads from storage.

#seconds for each index scan
1       2       3       4       5       2+3+4   index/engine
-       -       -       -       -       -----   ------------
7       8       8       8       5       24      MyRocks
4       4       4       4       4       12      InnoDB-5.6
3       4       7       3       3       14      InnoDB-5.7

Results for the i5 NUC are similar to the i3 NUC.

#seconds for each index scan
1       2       3       4       5       2+3+4   engine
-       -       -       -       -       -----   ------
5       7       8       7       5       22      MyRocks
3       4       3       4       3       11      InnoDB-5.6
3       3       3       2       3        8      InnoDB-5.7

And a graph of the results from the 2+3+4 column because some readers like graphs. Results: read-write, 1000 writes/second

This section has results for the read-write tests where the writer does 1000 inserts/second. The tables below have the most interesting data. The  metrics are explained here. All of the engines were able to sustain the target insert rate on average (ips.av). The value is 999 rather than 1000 because of implementation artifacts. There were more write stalls on InnoDB than MyRocks based on the 99th percentile insert rate as ips.99 is lower for InnoDB on the i3 NUC. InnoDB sustained a better query rate than MyRocks, although there is a regression from 5.6 to 5.7 but that is a topic for another post. InnoDB writes more than 30X to storage per insert compared to MyRocks.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     3684    3329     1.24    7307   1.8    2.5   26.9  MyRocks
999    994     4817    4454    43.90    5970   3.4   43.8   28.8  InnoDB-5.6
999    995     3748    3540    40.27   13834   3.4   40.2   51.8  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
999    998     4390    4006     1.26    6004   1.8    2.5   26.4  MyRocks
999    998     5798    5410    44.12    4846   3.4   44.1   28.1  InnoDB-5.6
999    998     5184    4917    40.62    5186   3.4   40.6   26.9  InnoDB-5.7

Results: read-write, 100 writes/second

This section has results for the read-write tests where the writer does 100 inserts/second. The metrics are explained here. Because the write rate is lower than in the previous section, the reads account for more of the overhead. The Mcpu/q result is larger for MyRocks than for InnoDB-5.6 which might explain why InnoDB does more read QPS. Again, InnoDB writes more to storage per insert and there is a regression from 5.6 to 5.7.

i3 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     3683    3416     1.35    6706   1.8   0.3    24.7  MyRocks
100    100     4729    4499    51.85    5457   3.4   5.2    25.8  InnoDB-5.6
100    100     3906    3717    52.94   12636   3.4   5.3    49.4  InnoDB-5.7

i5 NUC
ips.av ips.99  qps.av  qps.99  wkb/i   Mcpu/q  size  wmb/s  cpu   engine
100    100     4444    4159     1.38    5538   1.8    0.3   24.6  MyRocks
100    100     5937    5548    51.95    4309   3.4    5.2   25.6  InnoDB-5.6
100    100     5349    4976    55.24    4803   3.4    5.5   25.7  InnoDB-5.7

These graphs show the read QPS relative to MyRocks for the read-write tests from above.



MySQL vs. MariaDB: Reality Check

In this blog, we’ll provide a comparison between MySQL vs. MariaDB (including Percona Server for MySQL).

Introduction

The goal of this blog post is to evaluate, at a higher level, MySQL, MariaDB and Percona Server for MySQL side-by-side to better inform the decision making process. It is largely an unofficial response to published comments from the MariaDB Corporation.

It is worth noting that Percona Server for MySQL is a drop-in compatible branch of MySQL, where Percona contributes as much as possible upstream. MariaDB Server, on the other hand, is a fork of MySQL 5.5. They cherry-picked MySQL features, and don’t guarantee drop-in compatibility any longer.

MySQL Percona Server for MySQL* MariaDB Server Protocols MySQL protocol over port 3306, X Protocol over port 33060 MySQL protocol over port 3306, X Protocol over port 33060 MySQL protocol, MariaDB Server extensions Community –
Source Code Open Source Open Source Open Source Community – Development Open Source, contributions via signing the Oracle Contributor Agreement (OCA) Open Source Open Source, contributions via the new BSD license or signing the MariaDB Contributor Agreement (MCA) Community – Collaboration Mailing list, forums, bugs system Mailing list, forums, bugs system (Jira, Launchpad) Mailing list, bugs system (Jira), IRC channel Core –
Replication MySQL replication with GTID MySQL replication with GTID MariaDB Server replication, with own GTID, compatible only if MariaDB Server is a slave to MySQL, not vice versa Core –
Routing MySQL Router (GPLv2) ProxySQL (GPLv3) MariaDB MaxScale (Business Source License) Core –
Partitioning Standard Standard Standard, with extra engines like SPIDER/CONNECT that offer varying levels of support Tool –
Editing MySQL Workbench for Microsoft Windows, macOS, and Linux MySQL Workbench for Microsoft Windows, macOS, and Linux Webyog’s SQLYog for Microsoft Windows (MySQL Workbench notes an incompatible server) Tool –
Monitoring MySQL Enterprise Monitor Percona Monitoring & Management (PMM) (100% open source) Webyog’s Monyog Scalability –
Client Connections MySQL Enterprise Threadpool Open Source Threadpool with support for priority tickets Open Source Threadpool Scalability –
Clustering MySQL Group Replication MySQL Group Replication, Percona XtraDB Cluster (based on a further engineered Galera Cluster) MariaDB Enterprise Cluster (based on Galera Cluster) Security –
Encryption Tablespace data-at-rest encryption. Amazon KMS, Oracle Vault Enterprise Edition Tablespace data-at-rest encryption with Keyring Vault plugin Tablespace and table data-at-rest encryption. Amazon KMS, binlog/redo/tmp file with Aria tablespace encryption Security –
Data Masking ProxySQL data masking ProxySQL data masking MariaDB MaxScale data masking Security –
Firewall MySQL Enterprise Firewall ProxySQL Firewall MariaDB MaxScale Firewall Security –
Auditing MySQL Enterprise Audit Plugin Percona Audit Plugin (OSS) MariaDB Audit Plugin (OSS) Analytics No ClickHouse MariaDB ColumnStore SQL –
Common Table Expressions In-development for MySQL 8.0 (now a release candidate) In-development for MySQL 8.0 (now a release candidate) Present in MariaDB Server 10.2 SQL –
Window Functions In-development for MySQL 8.0 (now a release candidate) In-development for MySQL 8.0 (now a release candidate) Present in MariaDB Server 10.2 Temporal –
Log-based rollback No No In development for MariaDB Server 10.3 Temporal – system versioned tables No No In development for MariaDB Server 10.3 JSON JSON Data type, 21 functions JSON Data type, 21 functions No JSON Data Type, 26 functions Official
client connectors C (libmysqlclient), Java, ODBC, .NET, Node.js, Python, C++, mysqlnd for PHP C (libmysqlclient), Java, ODBC, .NET, Node.js, Python, C++, mysqlnd for PHP C (libmariadbclient), Java, ODBC Usability – CJK Language support Gb18030, ngram & MeCab for InnoDB full-text search Gb18030, ngram & MeCab for InnoDB full-text search No Monitoring – PERFORMANCE
_SCHEMA Thorough instrumentation in 5.7, sys schema included Thorough instrumentation in 5.7, sys schema included Instrumentation from MySQL 5.6, sys schema not included Security – Password authentication sha256_password (with caching_sha2_password in 8.0) sha256_password (with caching_sha2_password in 8.0) ed25519 (incompatible with sha256_password) Security –
Secure out of the box validate_password on by default, to choose a strong password at the start validate_password on by default, to choose a strong password at the start No Usability – Syntax differences EXPLAIN FOR CONNECTION <thread_id> EXPLAIN FOR CONNECTION <thread_id> SHOW EXPLAIN FOR <thread_id> Optimiser –
Optimiser Tracing Yes Yes No Optimiser –
Optimiser Hints Yes Yes No DBA –
Super readonly mode Yes Yes No Security – Password expiry Yes Yes No Security – Password last changed? Password lifetime? Yes Yes No Security – VALIDATE_PASSWORD
_STRENGTH() Yes Yes No Security – ACCOUNT LOCK/UNLOCK Yes Yes No Usability – Query Rewriting Yes Yes No GIS – GeoJSON &
GeoHash functionality Yes Yes Incomplete Security – mysql_ssl_rsa_setup Yes Yes No (setup SSL connections manually) MySQL Utilities Yes Yes No Backup locks No (in development for 8.0) Yes No Usability – InnoDB memcached interface Yes Yes No

*Note. Third-party software (such as ProxySQL and ClickHouse) used in conjunction with Percona Server for MySQL is not necessarily covered by Percona Support services.

To get a higher level view of what Percona Server for MySQL offers compared to MySQL, please visit: Percona Server Feature Comparison. Read this for a higher level view of compatibility between MariaDB Server and MySQL written by MariaDB Corporation.

Open Community

MariaDB Server undoubtedly has an open community, with governance mixed between MariaDB Foundation and MariaDB Corporation. There are open developer meetings on average about twice per year, two mailing lists (one for developers and users), an IRC channel and an open JIRA ticket system that logs bugs and feature requests.

Percona Server for MySQL also has an open community. Developer meetings are not open to general contributors, but there is a mailing list, an IRC channel and two systems – Launchpad and JIRA – for logging bugs and feature requests.

MySQL also has an open community where developer meetings are also not open to general contributors. There are many mailing lists, there are a few IRC channels and there is the MySQL bugs system. The worklogs are where the design for future releases happens, and these are opened up when their features are fully developed and  source-code-pushed.

From a source code standpoint, MySQL makes pushes to Github when a release is made; whereas open source development happens for Percona Server for MySQL and MariaDB Server on Github.

Feature development on MySQL continues in leaps and bounds, and Oracle has been an excellent steward of MySQL. Please refer to The Complete List of Features in 5.7, as well as The Unofficial MySQL 8 Optimiser Guide.

Linux distributions have chosen MariaDB Server 5.5, and some have chosen MariaDB Server 10.0/10.1 when there was more backward compatibility to MySQL 5.5/5.6. It is the “default” MySQL in many Linux distributions (such as Red Hat Enterprise Linux, SUSE and Debian). However, Ubuntu still believes that when you ask for MySQL you should get it (and that is what Ubuntu ships).

One of the main reasons Debian switched was due to the way Oracle publishes updates for security issues. They are released as a whole quarterly as Critical Patch Updates, without much detail about individual fixes. This is a policy that is unlikely to change, but has had no adverse effects on distribution.

All projects actively embrace contributions from the open community. MariaDB Server does include contributions like the MyRocks engine developed at Facebook, but so does Percona Server for MySQL. Oracle accepts contributions from a long list of contributors, including Percona. Please see Licensing information for MySQL 5.7 as an example.

A Shared Core Engine

MariaDB Server has differed from MySQL since MySQL 5.5. This is one reason why you don’t get version numbers that follow the MySQL scheme. It is also worth noting that features are cherry-picked at merge time, because the source code has diverged so much since then.

As the table below shows, it took Percona Server for MySQL over four months to get a stable 5.5 release based on MySQL 5.5, while it took MariaDB Server one year and four months to get a stable 5.5 release based on MySQL 5.5. Percona Server for MySQL 5.6 and 5.7 are based on their respective MySQL versions.

MySQL Percona Server for MySQL MariaDB Server 3 December 2010 5.5.8 GA 28 April 2011 5.5.11-20.2 GA 11 April 2012 5.5.23 GA 5 February 2013 5.6.10 GA 7 October 2013 5.6.13-61.0 GA 31 March 2014 10.0.10 GA 17 October 2015 10.1.8 GA 21 October 2015 5.7.9 GA 23 February 2016 5.7.10-3 GA 23 May 2017 10.2.6 GA

 

MySQL is currently at 8.0.3 Release Candidate, while MariaDB Server is at 10.3.2 Alpha as of this writing.

MariaDB Server is by no means a drop-in replacement for MySQL. The risk of moving to MariaDB Server if you aren’t using newer MySQL features may be minimal, but the risk of moving out of MariaDB Server to MySQL is very prevalent. Linux distributions like Debian already warn you of this.

The differences are beyond just default configuration options. Some features, like time-delayed replication that were present in MySQL since 2013, only make an appearance in MariaDB Server in 2017! (Refer to the MariaDB Server 10.2 Overview for more.) However, it is also worth noting some features such as multi-source replication appeared in MariaDB Server 10.0 first, and only then came to MySQL 5.7.

Extensibility

MySQL and MariaDB Server have a storage engine interface, and this is how you access all engines, including the favored InnoDB/Percona XtraDB. It is worth noting that Percona XtraDB was the default InnoDB replacement in MariaDB Server 5.1, 5.2, 5.3, 5.5, 10.0 and 10.1. But in MariaDB Server 10.2, the InnoDB of choice is upstream MySQL.

Stock MySQL has provided several storage engines beyond just InnoDB (the default) and MyISAM. You can find out more information about 5.7 Supported Engines.

Percona Server for MySQL includes a modified MEMORY storage engine, ships Percona XtraDB as the default InnoDB and also ships TokuDB and MyRocks (currently experimental). MyRocks is based on the RocksDB engine, and both are developed extensively at Facebook.

MariaDB Server includes many storage engines, beyond the default InnoDB. MyISAM is modified with segmented key caches, the default temporary table storage engine is Aria (which is a crash-safe MyISAM), the FederatedX engine is a modified FEDERATED engine, and there are more: CONNECT, Mroonga, OQGRAPH, Sequence, SphinxSE, SPIDER, TokuDB and of course MyRocks.

Storage engines have specific use cases, and have different levels of feature completeness. You should thoroughly evaluate a storage engine before choosing it. We believe that over 90% of installations are fine with just InnoDB or Percona XtraDB. Percona TokuDB is another engine that users who need compression could use. We naturally expect more usage in the MyRocks sphere going forward.

Analytics

MariaDB ColumnStore is the MariaDB solution to analytics and using a column-based store. It is a separate download and product, and not a traditional storage engine (yet). It is based on the now defunct InfiniDB product.

At Percona, we are quite excited by ClickHouse. We also have plenty of content around it. There is no MySQL story around this.

High Availability

High Availability is an exciting topic in the MySQL world, considering the server itself has been around for over 22 years. There are so many solutions out there, and some have had evolution as well.

MySQL provides MySQL Cluster (NDBCLUSTER) (there is no equivalent in the MariaDB world). MySQL also provides group replication (similar to Galera Cluster). Combined with the proxy MySQL Router, and the mysqlsh for administration (part of the X Protocol/X Dev API), you can also get MySQL InnoDB Cluster.

We benefit from the above at Percona, but also put lots of engineering work to make Percona XtraDB Cluster.

MariaDB Server only provides Galera Cluster.

Security

While we don’t want to compare the proprietary MySQL Enterprise Firewall, MariaDB’s recommendation is the proprietary, non-open source MariaDB MaxScale (it uses a Business Source License). We highly recommend the alternative, ProxySQL.

When it comes to encryption, MariaDB Server implements Google patches to provide complete data at rest encryption. This supports InnoDB, XtraDB and Aria temporary tables. The log files can also be encrypted (not present in MySQL, which only allows tablespace encryption and not log file encryption).

When it comes to attack prevention, ProxySQL should offer everything you need.

MySQL Enterprise provides auditing, while MariaDB Server provides an audit plugin as well as an extension to the audit interface for user filtering. Percona Server for MySQL has an audit plugin that sticks to the MySQL API, yet provides user filtering and controls the ability to audit (since auditing is expensive). Streaming to syslog is supported by the audit plugins from Percona and MariaDB.

Supporting Ecosystem and Tools

Upgrading from MySQL to MariaDB Server should be a relatively simple process (as stated above). If you want to upgrade away from MariaDB Server to MySQL, you may face hassles. For tools, see the following table:

Purpose MySQL Percona Server for MySQL MariaDB Server Monitoring MySQL Enterprise Monitor Percona Monitoring & Management (PMM) (100% open source) Webyog Monyog Backup MySQL Enterprise Backup Percona XtraBackup MariaDB Backup (fork of Percona XtraBackup) SQL Management MySQL Workbench MySQL Workbench Webyog SQLyog Load Balancing & Routing MySQL Router ProxySQL MariaDB MaxScale Database Firewall MySQL Enterprise Firewall ProxySQL MariaDB MaxScale

 

Enterprise Database Compatibility

MariaDB Server today has window functions and common table expressions (CTEs). These appeared in MariaDB Server 10.2. MySQL 8 is presently in release candidate status and also has similar functionality.

Looking ahead, MariaDB Server 10.3 also includes an Oracle SQL_MODE and a partial PL/SQL parser. This is to aid migration from Oracle to MariaDB Server.

MariaDB Server 10.2 also has “flashback”, developed at Alibaba, to help with log-based rollback using the binary log.

Conclusion

Percona sees healthy competition in the MySQL ecosystem. We support all databases in the ecosystem: MySQL, MariaDB Server and Percona Server for MySQL. Our focus is to provide alternatives to proprietary parts of open source software. Percona has a strong operations focus on compatibility, application scalability, high availability security and observability. We also support many additional tools within the ecosystem, and love integrating and contributing to open source code.

For example, Percona Monitoring and Management (PMM) includes many open source tools like Prometheus, Consul, Grafana, Orchestrator and more. We have made the de facto open source hot backup solution for MySQL, MariaDB Server and Percona Server for MySQL (called Percona XtraBackup). We continue to maintain and extend useful tools for database engineers and administrators in Percona Toolkit. We make Percona XtraDB Cluster safe for deployment out of the box. We have invested in a write-optimized storage engine, TokuDB, and now continue to work with making MyRocks better.

We look forward to supporting your deployments of MySQL or MariaDB Server, whichever option is right for you! If you need assistance on migrations between servers, or further information, don’t hesitate to contact your friendly Percona sales associate.

Using Facebook/Twitter Authentication in Adonis 4.0

In this tutorial, we are building an authentication system using Adonis-ally. I know the question on your mind, "what's adonis-ally?". Adonis-ally package makes it easier to authenticate your user via Facebook, Github, Google, LinkedIn, Twitter, Instagram, Foursquare and Bitbucket(Coming Soon). For this particular blog, we will be talking about authentication via Facebook and Twitter. I will talk about authentication via Google, Github, Instagram e.t.c in another tutorial.

Setting Up Adonis

Let's generate a new AdonisJS Application. For this tutorial, I'm using AdonisJS 4.0. Open your terminal and type this command:

# if you don't have AdonisJS CLI installed on your machine. $ npm i -g @adonisjs/cli $ adonis new adonis-social

Then change directory into the application directory, start your server and test if it's working fine.

$ cd adonis-social $ adonis serve --dev 2017-09-29T17:05:29.154Z - info: serving app on http://127.0.0.1:3333

Open your browser and make a request to http://127.0.0.1:3333. You should see Below UI.

Let's install adonis-ally package using adonis CLI

adonis install @adonisjs/ally

Then register the provider inside start/app.js file.

const providers = [ //... '@adonisjs/ally/providers/AllyProvider' //... ] Database Setup

For this tutorial, I will be using mysql. Create a database called adonis-social. Get your database's username and password and add it to the .env file in the project's root directory.

DB_CONNECTION=mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_DATABASE=adonis-social DB_USERNAME=root DB_PASSWORD=adonisjs Migrations and Models

We are not going to create any migration or model but we will modify the existing user model and migration file before we migrate it.
Go to "database/migrations" directory, delete <TIMESTAMP>_token.js file then edit <TIMESTAMP>_user.js

TIMESTAMP_user.js 'use strict' const Schema = use('Schema') class UserSchema extends Schema { up () { this.create('users', table => { table.increments() table.string('name').nullable() table.string('avatar').nullable() table.string('username', 80).nullable() table.string('email', 254).nullable() table.string('provider_id').nullable() table.string('provider').nullable() table.string('password', 60).nullable() table.timestamps() }) } down () { this.drop('users') } } module.exports = UserSchema

Install mysql module before we migrate our table.

$ npm install --save mysql

Let's go ahead with migrating this table.

adonis migration:run

Check your database and see that users table is created.

Let's move to app/Models directory, delete the Token.js file and edit the User.js file

'use strict' const Model = use('Model') class User extends Model { static boot () { super.boot() /** * A hook to bash the user password before saving * it to the database. * * Look at `app/Models/Hooks/User.js` file to * check the hashPassword method */ this.addHook('beforeCreate', 'User.hashPassword') } static get table () { return 'users' } static get primaryKey () { return 'id' } } module.exports = User Application Registration

In this section, we are going to obtain Facebook/Twitter client id and secret.

Let's start with Facebook -- click this link and add a new app. When done registering the application, click on settings on the sidenav. From the basic settings you can get your APP ID (client id) and APP Secret (client secret). Futher more, click the Add Platform button below the settings configuration. Select Website in the platform dialog box then enter the website URL, in our case it is http://localhost:8000. Set the App Domains to localhost then save the settings.

Update config\services.js file, under ally object, add the following key, if it does not exist.

[...] facebook: { clientId: Env.get('FB_CLIENT_ID'), clientSecret: Env.get('FB_CLIENT_SECRET'), redirectUri: `${Env.get('APP_URL')}/authenticated/facebook` } [...]

Set the keys obtained for the app in your .env file

FB_CLIENT_ID=<APP ID> FB_CLIENT_SECRET=<APP Secret>

One more thing to do for Facebook -- on the sidenav, select Products and Add Product, then select Facbook Login. Fill Valid OAuth redirect URIs which is http://localhost:3333/authenticated/facebook in our case

Next, head to twitter, log into your twitter account and register an application here. Set callback URL to http://localhost:3333/authenticated/twitter.

When the application is created, click on the keys and access token tab to obtain your Consumer API Key and Consumer API secret

Once the app has been created click on the keys and access tokens tab to get your Consumer API Key(client id) and Consumer API secret(client secret).

Update config\services.js file, under ally object, add the following key, if it does not exist.

[...] twitter: { clientId: Env.get('TWITTER_CLIENT_ID'), clientSecret: Env.get('TWITTER_CLIENT_SECRET'), redirectUri: `${Env.get('APP_URL')}/authenticated/twitter` } [...]

Set the keys obtained for the app in your .env file

TWITTER_CLIENT_ID=<Consumer API Key> TWITTER_CLIENT_SECRET=<Consumer API secret> Application Views

It's time to create some views for our application. Go to "resources/views" directory then we replace the content of welcome.edge file

welcome.edge @layout('master') @section('content') <div class="container" style="margin-top: 160px"> <div class="row"> <div class="col-md-1"></div> <div class="col-md-10"> <div class="card"> @loggedIn <div class="card-header">User Information</div> <div class="card-body"> <div class="container"> <div class="row justify-content-md-center"> <div class="col col-md-12"> <img src="{{ auth.user.avatar }}"> <h2>{{ auth.user.name }}</h2> <h5>{{ auth.user.provider }}</h5> </div> </div> </div> <br> </div> @else <div class="card-header">Authentication</div> <div class="card-body"> <div class="container"> <div class="row justify-content-md-center"> <div class="col col-md-6"> <a href="{{ route('social.login', {provider: 'facebook'}) }}" class="btn btn-block btn-facebook btn-social" role="button"> <i class="fa fa-facebook"></i> Login With Facebook </a> <a href="{{ route('social.login', {provider: 'twitter'}) }}" class="btn btn-block btn-twitter btn-social"> <i class="fa fa-twitter"></i> Sign in with Twitter </a> </div> </div> </div> <br> </div> @endloggedIn </div> </div> </div> @endsection

Also, create a file called master.edge file

master.edge <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="description" content="AdonisJs Social"> <meta name="author" content=""> <title>AdonisJs Social</title> <!-- Fonts --> {{ css('https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css') }} {{ css('https://fonts.googleapis.com/css?family=Lato:100,300,400,700') }} <!-- Styles --> {{ css('https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-beta/css/bootstrap.min.css') }} {{ css('https://cdnjs.cloudflare.com/ajax/libs/bootstrap-social/5.1.1/bootstrap-social.min.css') }} {{ css('style.css') }} </head> <body id="app-layout"> <nav class="navbar navbar-expand-md navbar-dark fixed-top"> <a class="navbar-brand" href="{{ route('welcomePage') }}"><i class="fa fa-cube"></i> AdonisJS</a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarCollapse"> <ul class="navbar-nav mr-auto"> <li class="nav-item"> <a class="nav-link {{ url == route('welcomePage') ? 'active' : '' }}" href="{{ route('welcomePage') }}">HOME</a> </li> </ul> <!-- Right Side Of Navbar --> <ul class="navbar-nav navbar-right"> <!-- Authentication Links --> @loggedIn <li class="nav-item dropdown"> <a class="nav-link dropdown-toggle" href="#" id="navbarDropdownMenuLink" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false"> <img src="{{ auth.user.toJSON().avatar }}" style="width: 1.9rem; height: 1.9rem; margin-right: 0.5rem" class="rounded-circle"> {{ auth.user.name }} <span class="caret"></span> </a> <div class="dropdown-menu" aria-labelledby="navbarDropdownMenuLink"> <a class="dropdown-item" href="{{ route('logout') }}"><i class="fa fa-btn fa-sign-out"></i> Logout</a> </div> </li> @endloggedIn </ul> </div> </nav> @!section('content') <!-- JavaScripts --> {{ script('https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.slim.min.js') }} {{ script('https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.11.0/umd/popper.min.js') }} {{ script('https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-beta/js/bootstrap.min.js') }} </body> </html>

Refresh your browser, your home page should look like this.

Creating Adonis Routes

Let's take care of our application's route in this section. Go to "start/routes.js" file and replace the content with:

start/routes.js 'use strict' const Route = use('Route') Route.on('/').render('welcome') Route.get('/auth/:provider', 'AuthController.redirectToProvider').as('social.login') Route.get('/authenticated/:provider', 'AuthController.handleProviderCallback').as('social.login.callback') Route.get('/logout', 'AuthController.logout').as('logout')

If you noticed, I added 3 more routes to the existing ones. One for redirecting the user to the OAuth provider(facebook and twitter in our case), another one for receiving the callback from the provider after authentication and last one for logout.

Controllers

We are going to create a controller for our application. In our case,we will call it AuthController. Run the below command

adonis make:controller AuthController

It will ask you "Generating a controller for?". Select Http Request,

Check your app/Controllers/Http/ directory, you should see a controller called AuthController.js has been created.

AuthController.js 'use strict' const User = use('App/Models/User') class AuthController { async redirectToProvider ({ally, params}) { await ally.driver(params.provider).redirect() } async handleProviderCallback ({params, ally, auth, response}) { const provider = params.provider try { const userData = await ally.driver(params.provider).getUser() const authUser = await User.query().where({ 'provider': provider, 'provider_id': userData.getId() }).first() if (!(authUser === null)) { await auth.loginViaId(authUser.id) return response.redirect('/') } const user = new User() user.name = userData.getName() user.username = userData.getNickname() user.email = userData.getEmail() user.provider_id = userData.getId() user.avatar = userData.getAvatar() user.provider = provider await user.save() await auth.loginViaId(user.id) return response.redirect('/') } catch (e) { console.log(e) response.redirect('/auth/' + provider) } } async logout ({auth, response}) { await auth.logout() response.redirect('/') } } module.exports = AuthController

Let's talk about the functions in the controller;

  • redirectToProvider handles redirecting the user to the OAuth provider(Facebook and Twitter).
  • handleProviderCallback handles retrieve the user's information from the provider(Facebook and Twitter). In this method, we checked if the user already exist in the database. If so, we return the user's information. Otherwise, create a new user. This concept prevents a user account from being created twice.

Below is view when you login via facebook and twitter

Conclusion

Now, you can authenticate user via facebook and twitter in your new AdonisJS Application. In my next post, we will work on authentication via google and github.

Let me mention this, I created an Hackathon Starter in AdonisJS those of you that want to explore AdonisJS. The source code is Here

If you have any questions or observations, feel free to drop it in the comments section below. I would be happy to respond to you.

Pages