Month: June 2018

This Week in Data with Colin Charles 44: MongoDB 4.0 and Facebook MyRocks

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.
There have been two big pieces of news this week: the release of MongoDB 4.0 and the fact that Facebook has migrated the Messenger backend over to MyRocks.
MongoDB 4.0 is stable, with support for multi-document ACID transactions. I quite like the engineering chalk and talks videos on the transactions page. There are also improvements to help manage your MongoDB workloads in a Kubernetes cluster. MongoDB Atlas supports global clusters (geographically distributed databases, low latency writes, and data placement controls for regulatory compliance), HIPAA compliance, and more. ZDNet calls it the “operational database that is developer friendly”. The TechCrunch take was more focused on MongoDB Atlas, MongoDB launches Global Clusters to put geographic data control within reach of anyone.
In addition to that, I found this little snippet on CNBC featuring Michael Gordon, MongoDB CFO, very interesting: last quarter MongoDB Inc reported 53% year-over-year growth in their subscription revenue business. The fastest-growing piece of the business? Cloud-hosted database as a service offering. They partner with Amazon, Google and Microsoft. They are looking to grow in the Chinese market.
Did you attend MongoDB World 2018? I personally can’t wait to see the presentations. Do not forget to read the MongoDB 4.0 release notes in the manual. Take heed of this important note: “In most cases, multi-document transaction incurs a greater performance cost over single document writes, and the availability of multi-document transaction should not be a replacement for effective schema design.”
As for Facebook Messenger migrating to MyRocks, this blog post is highly detailed: Migrating Messenger storage to optimize performance. This is a migration from the previous HBase backend to MyRocks. End users should notice a more responsive product and better search. For Facebook, storage consumption went down by 90%! The migration methodology to ensure Messenger usage was not disrupted for end users is also worth paying attention to. A more personal note from Yoshinori Matsunobu, as MyRocks is something he’s been spearheading. Don’t forget that you can try out MyRocks in Percona Server for MySQL as well as in MariaDB Server 10.2 and 10.3. To use Zstandard (or zstd for short), Percona Server for MySQL supports this (MariaDB does not, but has varying other compression algorithms).
Have you seen the Percona Open Source Database Community Blog? Jean-François Gagné recently wrote about how he posted on the Community Blog (so a very nice behind the scenes kind of post), and I hope you also read A Nice Feature in MariaDB 10.3: No InnoDB Buffer Pool in Core Dumps. Don’t forget to add this new blog to your RSS feed readers.
Lastly, as a quick note, there will unlikely be a column next week. I’m taking a short vacation, so see you in the following week!
Releases

Percona XtraDB Cluster 5.7.22-29.26 – Percona XtraDB Cluster now supports keyring_vault plugin so that you can store the master key in a Vault server. It also depends on Percona XtraBackup 2.4.12 to fully support Vault plugin functionality. Many bugs fixed as well.

Percona XtraBackup 2.4.12

Percona Monitoring and Management 1.12.0 – check out the visual explain in the query analytics! There are also a handful of new features, including if you happen to be using InnoDB compression.

FromDual Backup & Recovery Manager 2.0.0 – includes support for MySQL 8, MariaDB Server 10.3.

pgAdmin 4.3.1

phpMyAdmin 4.8.2 includes security fixes, so is a recommended upgrade.

MariaDB 10.2.16 – MyRocks is now stable GA in the MariaDB Server 10.2 series, with numerous bug fixes. Were you using MariaDB Galera Cluster in previous releases? A stability issue reported since 10.2.5 (18 May 2017) has been fixed since MariaDB 10.2.12 (released 4 Jan 2018). If you have encountered stalls and seen WSREP: BF lock wait long, do read MDEV-12837. I think it might also be relevant to follow MDEV-15154 WSREP: BF lock waits long after a TRUNCATE TABLE and MDEV-15747.

Oracle Linux 7 for ARM is now GA. 64-bit only, UEK5 only kernel, it also comes with MySQL 8.0 for the ARM platform.

Link List

MySQL 8.0 InnoDB Cluster – the quick hands-on manual – this is truly the quick start guide. There has to be more of this beginner styled cookbook guides on the MySQL site too! (The user manual is now really, really, large).

MariaDB 10.3 use case: Hidden PRIMARY KEY column for closed/legacy applications – use case for the invisible columns feature introduced in MariaDB 10.3.

usql – described as “A universal command-line interface for PostgreSQL, MySQL” and many other databases, including the likes of ClickHouse, Vitess and more.

xo – I find the idea of xo rather interesting, as it generates idiomatic Go code for SQL databases like MySQL and PostgreSQL. They initially wrote this for a migration from PHP to Go, and the idea is your self-documenting schema will generate static, type-safe, fast and idiomatic Go code.

Ever wondered why MySQL 8.0 GROUP BY performance could be a lot better than on MySQL 5.7? Øystein Grøvlen has answered that on StackOverflow, and it has a lot to do with the new TempTable storage engine. This is probably a worthy test to repeat with MariaDB Server 10.3.

How Oracle’s Acquisition Was Actually the Best Thing to Happen to MySQL – David Stokes (MySQL Community Manager @ Oracle) had a very good interview at SouthEastLinuxFest.

Industry Updates

Louis Fahrberger (formerly of Clustrix, MariaDB Corporation, InfoBright and MySQL) is now an Account Executive in Sales for MemSQL.

The Wall Street Journal reports on Oracle Cloud and how the business continues to grow. “Revenues from its cloud services businesses jumped 25% year over year to $1.7 billion for its fiscal fourth quarter that ended May 31”.

The Financial Times reports on Red Hat sinks as currency swings cloud full-year sales outlook. The CFO, Eric Shander said, “we continue to expect strong demand for our hybrid cloud enabling technologies”.

Upcoming appearances

OSCON – Portland, Oregon, USA – July 16-19 2018

Feedback
I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.
The post This Week in Data with Colin Charles 44: MongoDB 4.0 and Facebook MyRocks appeared first on Percona Database Performance Blog.

Percona XtraDB Cluster 5.7.22-29.26 Is Now Available

Percona XtraDB Cluster 5.7

Percona announces the release of Percona XtraDB Cluster 5.7.22-29.26 (PXC) on June 29, 2018. Binaries are available from the downloads section or our software repositories. Percona XtraDB Cluster 5.7.22-29.26 is now the current release, based on the following: Percona Server for MySQL 5.7.22 Galera/Codership WSREP API Release 5.7.21 Galera Replication library 3.23 Deprecated The following variables […]

The post Percona XtraDB Cluster 5.7.22-29.26 Is Now Available appeared first on Percona Database Performance Blog.

MySQL 8.0 Hot Rows with NOWAIT and SKIP LOCKED

In MySQL 8.0 there are two new features designed to support lock handling: NOWAIT and SKIP LOCKED. In this post, we’ll look at how MySQL 8.0 handles hot rows. Up until now, how have you handled locks that are part of an active transaction or are hot rows? It’s likely that you have the application attempt to access the data, and if there is a lock on the requested rows, you incur a timeout and have to retry the transaction. These two new features help you to implement sophisticated lock handling scenarios allowing you to handle timeouts better and improve the application’s performance.
To demonstrate I’ll use this product table.

mysql> select @@version;
+———–+
| @@version |
+———–+
| 8.0.11 |
+———–+
1 row in set (0.00 sec)

CREATE TABLE `product` (
`p_id` int(11) NOT NULL AUTO_INCREMENT,
`p_name` varchar(255) DEFAULT NULL,
`p_cost` decimal(19,4) NOT NULL,
`p_availability` enum(‘YES’,’NO’) DEFAULT ‘NO’,
PRIMARY KEY (`p_id`),
KEY `p_cost` (`p_cost`),
KEY `p_name` (`p_name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Let’s run through an example. The transaction below will lock the rows 2 and 3 if not already locked. The rows will get released when our transaction does a COMMIT or a ROLLBACK. Autocommit is enabled by default for any transaction and can be disabled either by using the START TRANSACTION clause or by setting the Autocommit to 0.
Session 1:
mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+——+——–+———+—————-+
| p_id | p_name | p_cost | p_availability |
+——+——–+———+—————-+
| 2 | Item2 | 20.0000 | YES |
| 3 | Item3 | 30.0000 | YES |
+——+——–+———+—————-+
2 rows in set (0.00 sec)
InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks.
We can get the details of a transaction such as the transaction id, row lock count etc using the command innodb engine status or by querying the performance_schema.data_locks table. The result from the innodb engine status command can however be confusing as we can see below. Our query only locked rows 3 and 4 but the output of the query reports 5 rows as locked (Count of Locked PRIMARY+ locked selected column secondary index + supremum pseudo-record). We can see that the row right next to the rows that we selected is also reported as locked. This is an expected and documented behavior. Since the table is small with only 5 rows, a full scan of the table is much faster than an index search. This causes all rows or most rows of the table to end up as locked as a result of our query.
Innodb Engine Status :-
—TRANSACTION 205338, ACTIVE 22 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 8, OS thread handle 140220824467200, query id 28 localhost root
performance_schema.data_locks (another new feature in 8.0.1):
mysql> SELECT ENGINE_TRANSACTION_ID,
CONCAT(OBJECT_SCHEMA, ‘.’,
OBJECT_NAME)TBL,
INDEX_NAME,count(*) LOCK_DATA
FROM performance_schema.data_locks
where LOCK_DATA!=’supremum pseudo-record’
GROUP BY ENGINE_TRANSACTION_ID,INDEX_NAME,OBJECT_NAME,OBJECT_SCHEMA;
+———————–+————–+————+———–+
| ENGINE_TRANSACTION_ID | TBL | INDEX_NAME | LOCK_DATA |
+———————–+————–+————+———–+
| 205338 | mydb.product | p_cost | 3 |
| 205338 | mydb.product | PRIMARY | 2 |
+———————–+————–+————+———–+
2 rows in set (0.04 sec)

mysql> SELECT ENGINE_TRANSACTION_ID as ENG_TRX_ID,
object_name,
index_name,
lock_type,
lock_mode,
lock_data
FROM performance_schema.data_locks WHERE object_name = ‘product’;
+————+————-+————+———–+———–+————————-+
| ENG_TRX_ID | object_name | index_name | lock_type | lock_mode | lock_data |
+————+————-+————+———–+———–+————————-+
| 205338 | product | NULL | TABLE | IX | NULL |
| 205338 | product | p_cost | RECORD | X | 0x800000000000140000, 2 |
| 205338 | product | p_cost | RECORD | X | 0x8000000000001E0000, 3 |
| 205338 | product | p_cost | RECORD | X | 0x800000000000320000, 5 |
| 205338 | product | PRIMARY | RECORD | X | 2 |
| 205338 | product | PRIMARY | RECORD | X | 3 |
+————+————-+————+———–+———–+————————-+
6 rows in set (0.00 sec)
Session 1:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

SELECT FOR UPDATE with innodb_lock_wait_timeout:
The innodb_lock_wait_timeout feature is one mechanism that is used to handle lock conflicts. The variable has default value set to 50 sec and causes any transaction that is waiting for a lock for more than 50 seconds to terminate and post a timeout message to the user. The parameter is configurable based on the requirements of the application.
Let’s look at how this feature works using an example with a select for update query.
mysql> select @@innodb_lock_wait_timeout;
+—————————-+
| @@innodb_lock_wait_timeout |
+—————————-+
| 50 |
+—————————-+
1 row in set (0.00 sec)
Session 1:
mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+——+——–+———+—————-+
| p_id | p_name | p_cost | p_availability |
+——+——–+———+—————-+
| 2 | Item2 | 20.0000 | YES |
| 3 | Item3 | 30.0000 | YES |
+——+——–+———+—————-+
2 rows in set (0.00 sec)
Session 2:
mysql> select now();SELECT * FROM mydb.product WHERE p_id=3 FOR UPDATE;select now();
+———————+
| now() |
+———————+
| 2018-06-19 05:29:48 |
+———————+
1 row in set (0.00 sec)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
+———————+
| now() |
+———————+
| 2018-06-19 05:30:39 |
+———————+
1 row in set (0.00 sec)
mysql>
Autocommit is enabled (by default) and as expected the transaction waited for lock wait timeout and exited.
Session 1:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

NOWAIT:
The NOWAIT clause causes a query to terminate immediately in the case that candidate rows are already locked. Considering the previous example, if the application’s requirement is to not wait for the locks to be released or for a timeout, using the NOWAIT clause is the perfect solution. (Setting the innodb_lock_wait_timeout=1 in session also has the similar effect). 
Session 1:
mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+——+——–+———+—————-+
| p_id | p_name | p_cost | p_availability |
+——+——–+———+—————-+
| 2 | Item2 | 20.0000 | YES |
| 3 | Item3 | 30.0000 | YES |
+——+——–+———+—————-+
2 rows in set (0.00 sec)
Session 2:
mysql> SELECT * FROM mydb.product WHERE p_id = 3 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.
mysql>
Session 1:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

SKIP LOCKED:
The SKIP LOCKED clause asks MySQL to non-deterministically skip over the locked rows and process the remaining rows based on the where clause. Let’s look at how this works using some examples:
Session 1:
mysql> START TRANSACTION;SELECT * FROM mydb.product WHERE p_cost >=20 and p_cost <=30 FOR UPDATE;
Query OK, 0 rows affected (0.00 sec)
+——+——–+———+—————-+
| p_id | p_name | p_cost | p_availability |
+——+——–+———+—————-+
| 2 | Item2 | 20.0000 | YES |
| 3 | Item3 | 30.0000 | YES |
+——+——–+———+—————-+
2 rows in set (0.00 sec)
Session 2:
mysql> SELECT * FROM mydb.product WHERE p_cost = 30 FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)
mysql>

mysql> SELECT * from mydb.product where p_id IN (1,2,3,4,5) FOR UPDATE SKIP LOCKED;
+——+——–+———+—————-+
| p_id | p_name | p_cost | p_availability |
+——+——–+———+—————-+
| 1 | Item1 | 10.0000 | YES |
| 5 | Item5 | 50.0000 | YES |
+——+——–+———+—————-+
2 rows in set (0.00 sec)
Session 1:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
The first transaction is selecting rows 2 and 3 for update(ie locked). The second transaction skips these rows and returns the remaining rows when the SKIP LOCKED clause is used.
Important Notes: As the SELECT … FOR UPDATE clause affects concurrency, it should only be used when absolutely necessary. Make sure to index the column part of the where clause as the SELECT … FOR UPDATE is likely to lock the whole table if proper indexes are not setup for the table. When an index is used, only the candidate rows are locked.
The post MySQL 8.0 Hot Rows with NOWAIT and SKIP LOCKED appeared first on Percona Database Performance Blog.

What is the FCB_REPLICA_SYNC spinlock?

A question came up on the Data Platform MVP email list last night asking what the FCB_REPLICA_SYNC spinlock is. I answered the question and then promised to do a quick blog post, as there’s no information online about it that I could find. Explanation In a nutshell, this spinlock is used to synchronize access to […]

The post What is the FCB_REPLICA_SYNC spinlock? appeared first on Paul S. Randal.

What’s Keeping You From Oracle Database 12c OCP?

Now that you have achieved your first step on your Oracle Database Certification path, why not continue to the next level and upgrade at the same time to version 12c? 

Keeping your skills sharp and relevant are key to maintaining your career relevancy.  Hear from Oracle Database Expert Joel Goodman An Insider’s Perspective on why staying current is so important.

Show employers you have the Oracle Database skills that today’s market demands.

“Having passed the certification is the best you can do to show maximum experience even when nobody has the version in production yet. Once again, it is the way to have one step ahead. “ – Franck Pachot Oracle Certified Master & ACE Director  Read his full story 

How to Prepare for Oracle Database 12c Certification

There are some great training courses you can take to learn the latest in Database technology.  Or if you already feel up-to-speed there, an Exam Prep Seminar package may be exactly what you need. 

Here is what’s included:

  • 4-6 hours of recorded content where you are expertly guided through what is needed to pass the exam. 
  • Access to a practice test.
  • An exam voucher and a free retake if you need to make a 2nd attempt. 

All for a great price of $618 USD!

And last but not least, if you feel prepared Get Your Voucher now and schedule your exam!

Helpful Links:

 

How NOT to Monitor Your Database

Do you have experience putting out backend database fires? What were some things you wished you had done differently? Proactive database monitoring is more cost efficient, manageable, and sanity-saving than reactive monitoring. We reviewed some of the most common mistakes – too many log messages, metric “melting pots,” retroactive changes, incomplete visibility, undefined KPIs – and put together an action plan on how to prevent them. From our experience, we’ve listed out the top 5 biggest (and preventable!) database monitoring pitfalls.

Log Levels
There never seem to be enough logging levels to capture the desired granularity and relevance of a log message accurately. Is it INFO, TRACE, or DEBUG? What if it’s DEBUG but it’s for a condition we should WARN about? Is there really a linear hierarchy here? If you’re like most people, you’ve seen at least once an extension of those types of standard logging levels on top of a widely available logging system in an attempt to add even more custom levels. There exists a good argument that there should really only be two types of log messages: those useful for writing and debugging the code, and those useful for operating it. Dave Cheney has a good blog post about this differentiation.
Mixed Status and Configuration Variables
Many systems don’t distinguish between status variables, which signal the system’s state, and configuration variables, which are inputs to the system’s operation. For example, in both MySQL and Redis, the commands to get system status will return mixtures of configuration variables and status metrics. Such a metrics “melting pot” is a very common problem that usually requires custom code or exception lists (blacklist/whitelist) to identify which variables are what. 
Breaking Backwards Compatibility

If you change the meaning or dimensions of a metric, ideally you should leave the old behavior unchanged and introduce a replacement alongside it. Failure to do this causes a lot of work for other systems. For example, in MySQL, the SHOW STATUS command was changed to include connection-specific counters by default, with the old system-wide global counters accessible via a different query syntax. This change was just a bad decision, and it caused an enormous amount of grief. Likewise, the meaning of MySQL’s “Questions” status variable was changed at one point, and the old behavior was available in a new variable called “Queries.” Essentially, they renamed a variable and then introduced a new, different variable with the same name as the old one. This change also caused a lot of confusion. Don’t do this.

Incomplete Visibility
Again, the easiest example of this is in MySQL, which has had a SHOW VARIABLES command for many years. Most, but not all, of the server’s command line options had identically named variables visible in the output of this command. But some were missing entirely, and others were present but under names that didn’t match.
Missing KPIs
The list of crucial metrics for finding and diagnosing performance issues isn’t that large. Metrics such as utilization, latency, queue length, and the like can be incredibly valuable, and can be computed from fundamental metrics, if those are available. For an example, see the Linux /proc/diskstats metrics, which include values that you can analyze with queueing theory, as illustrated on Baron’s personal blog. But you’d be surprised how many systems don’t have any way to inspect these key metrics, because people without much knowledge of good monitoring built the systems. For example, PostgreSQL has a standard performance counter for transactions, but not for statements, so if you want to know how many queries (statements) per second your server is handling, you have to resort to much more complex alternatives. This lack of a basic performance metric (throughput) is quite a serious oversight.
These are just some don’ts for developing and monitoring database applications. Interested in learning some of the do’s? Download the full eBook, Best Practices for Architecting Highly Monitorable Applications.

Detecting Incompatible Use of Spatial Functions before Upgrading to MySQL 8.0

There are many changes to spatial functions in MySQL 8.0:

Old aliases for functions have been removed (after being deprecated in 5.7)
Functions that don’t support geographic computations raise errors if called with geographic data in their arguments
Many functions support geographic computations

The first two are failing cases.…

Faster Point In Time Recovery (PITR) in PostgreSQL Using a Delayed Standby

PostgreSQL Point in Time Recovery

The need to recover a database back to a certain point in time can be a nerve-racking task for DBAs and for businesses. Can this be simplified? Could it be made to work faster? Can we recover to a given point in time with zero loss of transactions/records? Fortunately, the answer to these questions is […]

The post Faster Point In Time Recovery (PITR) in PostgreSQL Using a Delayed Standby appeared first on Percona Database Performance Blog.

MySQL Swapping With Fsync

One problem that’s a lot less common these days is swapping. Most of the issues that cause swapping with MySQL have been nailed down to several different key configuration points, either in the OS or MySQL, or issues like the swap insanity issue documented by Jeremy Cole back in 2010. As such, it’s usually pretty easy to resolve these issues and keep MySQL out of swap space. Recently, however, we had tried all of the usual tricks but had an issue where MySQL was still swapping.
The server with the issue was a VM running with a single CPU socket (multiple cores), so we knew it wasn’t NUMA. Swappiness and MySQL were both configured correctly and when you checked the output of free -m it showed 4735M of memory available.
[sylvester@host~]$ free -m
total used free shared buff/cache available
Mem: 16046 10861 242 16 4941 4735
Swap: 15255 821 14434
The point that needs a bit more attention here is the amount of memory being used by the OS cache. As you can see, there is a total of 16046M of physical memory available to the host, with only 10861M in use and the majority of what’s left over being used by the OS cache. This typically isn’t a problem. When requests for more memory come in from threads running in the OS, it should evict pages from the OS cache in order to make memory available to the requesting process. In this case, this did not occur. Instead, we observed that it held onto that cache memory and forced MySQL to turn to swap. But why?
As it turns out, the system in question had recently been converted from MYISAM to InnoDB and hadn’t had any server configuration set to accommodate for this. As such it was still configured for innodb_flush_method at the default value, which in 5.7 is still fsync. Both Ivan Groenwold and I have both written blog posts in regards to flush methods, and it’s been generally accepted that O_DIRECT is a much better way to go in most use cases on Linux, including this one, so we wanted to get the system in question more aligned with best practices before investigating further. As it turns out, we didn’t have to look any further than this, as switching the system over to innodb_flush_method = O_DIRECT resolved the issue. It appears that fsync causes the kernel to want to hang onto its data pages, so when innodb attempted to expand its required amount of memory, it was unable to do so without accessing swap, even with swappiness set to 0 to test.
Ever since we did the change to O_DIRECT, the OS cache usage has dropped and there have been no problems with OS cache page eviction.
CONCLUSION
MySQL swapping can really ruin your day and it’s something you want to avoid if at all possible. We still run into issues with swapping every now and then and want to continue to provide the community with our findings as they become available. So if you have a server that is swapping, and the OS cache isn’t making room for MySQL, and if you’re still using fsync for InnoDB flush, consider switching to O_DIRECT.

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569