Author: MySQL Performance Blog

InnoDB Cluster in a nutshell – Part 1

Since MySQL 5.7 we have a new player in the field, MySQL InnoDB Cluster. This is an Oracle High Availability solution that can be easily installed over MySQL to get High Availability with multi-master capabilities and automatic failover.
This solution consists in 3 components: InnoDB Group Replication, MySQL Router and MySQL Shell, you can see how these components interact in this graphic:

In this three blog post series, we will cover each of this components to get a sense of what this tool provides and how it can help with architecture decisions.
Group Replication
This is the actual High Availability solution, and a while ago I wrote a short review of it when it still was in its labs stage. It has improved a lot since then.
This solution is based on a plugin that has to be installed (not installed by default) and works on the top of built-in replication. So it relies on binary logs and relay logs to apply writes to members of the cluster.
The main concept about this new type of replication is that all members of a cluster (i.e. each node) are considered equals. This means there is no master-slave (where slaves follow master) but members that apply transactions based on a consensus algorithm. This algorithm forces all members of a cluster to commit or reject a given transaction following decisions made by each individual member.
In practical terms, this means each member of the cluster has to decide if a transaction can be committed (i.e. no conflicts) or should be rolled back but all other members follow this decision. In other words, the transaction is either committed or rolled back according to the majority of members in a consistent state.
To achieve this, there is a service that exposes a view of cluster status indicating what members form the cluster and the current status of each of them. Additionally Group Replication requires GTID and Row Based Replication (or
binlog_format=ROW ) to distribute each writeset with row changes between members. This is done via binary logs and relay logs but before each transaction is pushed to binary/relay logs it has to be acknowledged by a majority of members of the clusters, in other words through consensus. This process is synchronous, unlike legacy replication. After a transaction is replicated we have a certification process to commit the transaction, and thus making it durable.
Here appears a new concept, the certification process, which is the process that confirms if a writeset can be applied/committed (i.e. a row change can be done without conflicts) after replication of the transaction is complete.
Basically this process consists of inspecting writesets to check if there are conflicts (i.e. same row updated by concurrent transactions). Based on an order set in the writeset, the conflict is resolved by ‘first-commiter wins’ while the second is rolled back in the originator. Finally, the transaction is pushed to binary/relay logs and committed.
Solution features
Some other features provided by this solution are:

Single-primary or multi-primary modes meaning that the cluster can operate with a single writer and multiple readers (recommended and default setup); or with multiple writers where all nodes are capable to accept write transactions. The latter is at the cost of a performance penalty due to conflict resolution.

Automatic failure detection, where an internal mechanism is able to detect a failed node (i.e. a crash, network problems, etc) and decide to exclude it from the cluster automatically. Also if a member can’t communicate with the cluster and gets isolated, it can’t accept transactions. This ensures that cluster data is not impacted by this situation.

Fault tolerance. This is the strategy that the cluster uses to support failing members. As described above, this is based on a majority. A cluster needs at least three members to support one node failure because the other two members will keep the majority. The bigger the number of nodes, the bigger the number of failing nodes the cluster supports. The maximum number of members (nodes) in a cluster is currently limited to 7. If it has seven members, then the majority is kept by four or more active members. In other words, a cluster of seven would support up to three failing nodes.

We will not cover installation and configuration aspects now. This will probably come with a new series of blogs where we can cover not only deployment but also use cases and so on.
In the next post we will talk about the next cluster component: MySQL Router, so stay tuned.
The post InnoDB Cluster in a nutshell – Part 1 appeared first on Percona Database Performance Blog.

Linux OS Tuning for MySQL Database Performance

In this post we will review the most important Linux settings to adjust for performance tuning and optimization of a MySQL database server. We’ll note how some of the Linux parameter settings used OS tuning may vary according to different system types: physical, virtual or cloud. Other posts have addressed MySQL parameters, like Alexander’s blog MySQL 5.7 Performance Tuning Immediately After Installation. That post remains highly relevant for the latest versions of MySQL, 5.7 and 8.0. Here we will focus more on the Linux operating system parameters that can affect database performance.
Server and Operating System
Here are some Linux parameters that you should check and consider modifying if you need to improve database performance.
Kernel – vm.swappiness
The value represents the tendency of the kernel  to swap out memory pages. On a database server with ample amounts of RAM, we should keep this value as low as possible. The extra I/O can slow down or even render the service unresponsive. A value of 0 disables swapping completely while 1 causes the kernel to perform the minimum amount of swapping. In most cases the latter setting should be OK:
# Set the swappiness value as root
echo 1 > /proc/sys/vm/swappiness
# Alternatively, using sysctl
sysctl -w vm.swappiness=1
# Verify the change
cat /proc/sys/vm/swappiness
1
# Alternatively, using sysctl
sysctl vm.swappiness
vm.swappiness = 1
The change should be also persisted in /etc/sysctl.conf:
vm.swappiness = 1

Filesystems – XFS/ext4/ZFS
XFS
XFS is a high-performance, journaling file system designed for high scalability. It provides near native I/O performance even when the file system spans multiple storage devices.  XFS has features that make it suitable for very large file systems, supporting files up to 8EiB in size. Fast recovery, fast transactions, delayed allocation for reduced fragmentation and near raw I/O performance with DIRECT I/O.
The default options for mkfs.xfs are good for optimal speed, so the simple command:
# Use default mkfs options
mkfs.xfs /dev/target_volume
will provide best performance while ensuring data safety. Regarding mount options, the defaults should fit most cases. On some filesystems you can see a performance increase by adding the noatime mount option to the /etc/fstab.  For XFS filesystems the default atime behaviour is relatime, which has almost no overhead compared to noatime and still maintains sane atime values.  If you create an XFS file system on a LUN that has a battery backed, non-volatile cache, you can further increase the performance of the filesystem by disabling the write barrier with the mount option nobarrier. This helps you to avoid flushing data more often than necessary. If a BBU (backup battery unit) is not present, however, or you are unsure about it, leave barriers on, otherwise you may jeopardize data consistency. With this options on, an /etc/fstab file should look like the one below:
/dev/sda2 /datastore xfs defaults,nobarrier
/dev/sdb2 /binlog xfs defaults,nobarrier

ext4
ext4 has been developed as the successor to ext3 with added performance improvements. It is a solid option that will fit most workloads. We should note here that it supports files up to 16TB in size, a smaller limit than xfs. This is something you should consider if extreme table space size/growth is a requirement. Regarding mount options, the same considerations apply. We recommend the defaults for a robust filesystem without risks to data consistency. However, if an enterprise storage controller with a BBU cache is present, the following mount options will provide the best performance:
/dev/sda2 /datastore ext4 noatime,data=writeback,barrier=0,nobh,errors=remount-ro
/dev/sdb2 /binlog ext4 noatime,data=writeback,barrier=0,nobh,errors=remount-ro
Note: The data=writeback option results in only metadata being journaled, not actual file data. This has the risk of corrupting recently modified files in the event of a sudden power loss, a risk which is minimised with a presence of a BBU enabled controller. nobh only works with the data=writeback option enabled.
ZFS
ZFS is a filesystem and LVM combined enterprise storage solution with extended protection vs data corruption. There are certainly cases where the rich feature set of ZFS makes it an essential option to consider, most notably when advance volume management is a requirement. ZFS tuning for MySQL can be a complex topic and falls outside the scope of this blog. For further reference, there is a dedicated blog post on the subject by Yves Trudeau:
Disk Subsystem – I/O scheduler 
Most modern Linux distributions come with noop or deadline I/O schedulers by default, both providing better performance than the cfq and anticipatory ones. However it is always a good practice to check the scheduler for each device and if the value shown is different than noop or deadline the policy can change without rebooting the server:
# View the I/O scheduler setting. The value in square brackets shows the running scheduler
cat /sys/block/sdb/queue/scheduler
noop deadline [cfq]
# Change the setting
sudo echo noop > /sys/block/sdb/queue/scheduler
To make the change persistent, you must modify the GRUB configuration file:
# Change the line:
GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash”
# to:
GRUB_CMDLINE_LINUX_DEFAULT=”quiet splash elevator=noop”
AWS Note: There are cases where the I/O scheduler has a value of none, most notably in AWS VM instance types where EBS volumes are exposed as NVMe block devices. This is because the setting has no use in modern PCIe/NVMe devices. The reason is that they have a very large internal queue and they bypass the IO scheduler altogether. The setting in this case is none and it is the optimal in such disks.

Disk Subsystem – Volume optimization

Ideally different disk volumes should be used for the OS installation, binlog, data and the redo log, if this is possible. The separation of OS and data partitions, not just logically but physically, will improve database performance. The RAID level can also have an impact: RAID-5 should be avoided as the checksum needed to ensure integrity is costly. The best performance without making compromises to redundancy is achieved by the use of an advanced controller with a battery-backed cache unit and preferably RAID-10 volumes spanned across multiple disks.
AWS Note: For further information about EBS volumes and AWS storage optimisation, Amazon has documentation at the following links:
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/nvme-ebs-volumes.html
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/storage-optimized-instances.html
Database settings
System Architecture – NUMA settings
Non-uniform memory access (NUMA) is a memory design where an SMP’s system processor can access its own local memory faster than non-local memory (the one assigned local to other CPUs). This may result in suboptimal database performance and potentially swapping. When the buffer pool memory allocation is larger than size of the RAM available local to the node, and the default memory allocation policy is selected, swapping occurs. A NUMA enabled server will report different node distances between CPU nodes. A uniformed one will report a single distance:
# NUMA system
numactl –hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 65525 MB
node 0 free: 296 MB
node 1 cpus: 8 9 10 11 12 13 14 15
node 1 size: 65536 MB
node 1 free: 9538 MB
node 2 cpus: 16 17 18 19 20 21 22 23
node 2 size: 65536 MB
node 2 free: 12701 MB
node 3 cpus: 24 25 26 27 28 29 30 31
node 3 size: 65535 MB
node 3 free: 7166 MB
node distances:
node   0   1   2   3
  0:  10  20  20  20
  1:  20  10  20  20
  2:  20  20  10  20
  3:  20  20  20  10
# Uniformed system
numactl –hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 64509 MB
node 0 free: 4870 MB
node distances:
node   0
  0:  10
In the case of a NUMA system, where numactl shows different distances across nodes, the MySQL variable innodb_numa_interleave should be enabled to ensure memory interleaving. Percona Server provides improved NUMA support by introducing the flush_caches variable. When enabled, it will help with allocation fairness across nodes. To determine whether or not allocation is equal across nodes, you can examine numa_maps for the mysqld process with this script:
# The perl script numa_maps.pl will report memory allocation per CPU node:
# 3595 is the pid of the mysqld process
perl numa_maps.pl < /proc/3595/numa_maps
N0        :     16010293 ( 61.07 GB)
N1        :     10465257 ( 39.92 GB)
N2        :     13036896 ( 49.73 GB)
N3        :     14508505 ( 55.35 GB)
active    :          438 (  0.00 GB)
anon      :     54018275 (206.06 GB)
dirty     :     54018275 (206.06 GB)
kernelpagesize_kB:         4680 (  0.02 GB)
mapmax    :          787 (  0.00 GB)
mapped    :         2731 (  0.01 GB)

Conclusion
In this blog post we examined a few important OS related settings and explained how they can be tuned for better database performance.
While you are here …
You might also find value in this recorded webinar Troubleshooting Best Practices: Monitoring the Production Database Without Killing Performance
 
The post Linux OS Tuning for MySQL Database Performance 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.

Percona Monitoring and Management 1.12.0 Is Now Available

PMM (Percona Monitoring and Management) is a free and open-source platform for managing and monitoring MySQL and MongoDB performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL and MongoDB servers to ensure that your data works as efficiently as possible.
In release 1.12, we invested our efforts in the following areas:

Visual Explain in Query Analytics – Gain insight into MySQL’s query optimizer for your queries
New Dashboard – InnoDB Compression Metrics – Evaluate effectiveness of InnoDB Compression
New Dashboard – MySQL Command/Handler Compare – Contrast MySQL instances side by side
Updated Grafana to 5.1 – Fixed scrolling issues

We addressed 10 new features and improvements, and fixed 13 bugs.
Visual Explain in Query Analytics
We’re working on substantial changes to Query Analytics and the first part to roll out is something that users of Percona Toolkit may recognize – we’ve introduced a new element called Visual Explain based on pt-visual-explain.  This functionality transforms MySQL EXPLAIN output into a left-deep tree representation of a query plan, in order to mimic how the plan is represented inside MySQL.  This is of primary benefit when investigating tables that are joined in some logical way so that you can understand in what order the loops are executed by the MySQL query optimizer. In this example we are demonstrating the output of a single table lookup vs two table join:

Single Table Lookup
Two Tables via INNER JOIN

SELECT DISTINCT c
FROM sbtest13
WHERE id
BETWEEN 49808
AND 49907
ORDER BY c
SELECT sbtest3.c
FROM sbtest1
INNER JOIN sbtest3
ON sbtest1.id = sbtest3.id
WHERE sbtest3.c =’long-string’;

InnoDB Compression Metrics Dashboard
A great feature of MySQL’s InnoDB storage engine includes compression of data that is transparently handled by the database, saving you space on disk, while reducing the amount of I/O to disk as fewer disk blocks are required to store the same amount of data, thus allowing you to reduce your storage costs.  We’ve deployed a new dashboard that helps you understand the most important characteristics of InnoDB’s Compression.  Here’s a sample of visualizing Compression and Decompression attempts, alongside the overall Compression Success Ratio graph:

 
MySQL Command/Handler Compare Dashboard
We have introduced a new dashboard that lets you do side-by-side comparison of Command (Com_*) and Handler statistics.  A common use case would be to compare servers that share a similar workload, for example across MySQL instances in a pool of replicated slaves.  In this example I am comparing two servers under identical sysbench load, but exhibiting slightly different performance characteristics:

The number of servers you can select for comparison is unbounded, but depending on the screen resolution you might want to limit to 3 at a time for a 1080 screen size.
New Features & Improvements

PMM-2519: Display Visual Explain in Query Analytics

PMM-2019: Add new Dashboard InnoDB Compression metrics

PMM-2154: Add new Dashboard Compare Commands and Handler statistics

PMM-2530: Add timeout flags to mongodb_exporter (thank you unguiculus for your contribution!)

PMM-2569: Update the MySQL Golang driver for MySQL 8 compatibility

PMM-2561: Update to Grafana 5.1.3

PMM-2465: Improve pmm-admin debug output

PMM-2520: Explain Missing Charts from MySQL Dashboards

PMM-2119: Improve Query Analytics messaging when Host = All is passed

PMM-1956: Implement connection checking in mongodb_exporter

Bug Fixes

PMM-1704: Unable to connect to AtlasDB MongoDB

PMM-1950: pmm-admin (mongodb:metrics) doesn’t work well with SSL secured mongodb server

PMM-2134: rds_exporter exports memory in Kb with node_exporter labels which are in bytes

PMM-2157: Cannot connect to MongoDB using URI style

PMM-2175: Grafana singlestat doesn’t use consistent colour when unit is of type Time

PMM-2474: Data resolution on Dashboards became 15sec interval instead of 1sec

PMM-2581: Improve Travis CI tests by addressing pmm-admin check-network Time Drift

PMM-2582: Unable to scroll on “_PMM Add Instance” page when many RDS instances exist in an AWS account

PMM-2596: Set fixed height for panel content in PMM Add Instances

PMM-2600: InnoDB Checkpoint Age does not show data for MySQL

PMM-2620: Fix balancerIsEnabled & balancerChunksBalanced values

PMM-2634: pmm-admin cannot create user for MySQL 8

PMM-2635: Improve error message while adding metrics beyond “exit status 1”

Known Issues

PMM-2639: mysql:metrics does not work on Ubuntu 18.04 – We will address this in a subsequent release

How to get PMM Server
PMM is available for installation using three methods:

On Docker Hub – docker pull percona/pmm-server – Documentation

AWS Marketplace – Documentation

Open Virtualization Format (OVF) – Documentation

The post Percona Monitoring and Management 1.12.0 Is Now Available appeared first on Percona Database Performance Blog.

Webinar 6/28: Securing Database Servers From External Attacks

Please join Percona’s Chief Evangelist Colin Charles on Thursday, June 28th, 2018, as he presents Securing Database Servers From External attacks at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).
Register Now
 
A critical piece of your infrastructure is the database tier, yet people don’t pay enough attention to it judging by how many are bitten via poorly chosen defaults, or just a lack understanding of running a secure database tier. In this talk, I’ll focus on MySQL/MariaDB, PostgreSQL, and MongoDB, and cover external authentication, auditing, encryption, SSL, firewalls, replication, and more gems from over a decade of consulting in this space from Percona’s 4,000+ customers.
Register Now
 
Colin Charles
Chief Evangelist
Colin Charles is the Chief Evangelist at Percona. He was previously on the founding team of MariaDB Server in 2009, and had worked at MySQL since 2005, and been a MySQL user since 2000. Before joining MySQL, he worked actively on the Fedora and OpenOffice.org projects. He’s well known within open source communities in APAC, and has spoken at many conferences. Experienced technologist, well known in the open source world for work that spans nearly two decades within the community. Pays attention to emerging technologies from an integration standpoint. Prolific speaker at many industry-wide conferences delivering talks and tutorials with ease. Interests: application development, systems administration, database development, migration, Web-based technologies. Considered expert in Linux and Mac OS X usage/administration/roll-out’s. Specialties: MariaDB, MySQL, Linux, Open Source, Community, speaking & writing to technical audiences as well as business stakeholders.
The post Webinar 6/28: Securing Database Servers From External Attacks appeared first on Percona Database Performance Blog.

Running Percona XtraDB Cluster in Kubernetes/OpenShift

Kubernetes, and its most popular distribution OpenShift, receives a lot of interest as a container orchestration platform. However, databases remain a foreign entity, primarily because of their stateful nature, since container orchestration systems prefer stateless applications. That said, there has been good progress in support for StatefulSet applications and persistent storage, to the extent that it might be already comfortable to have a production database instance running in Kubernetes. With this in mind, we’ve been looking at running Percona XtraDB Cluster in Kubernetes/OpenShift.
While there are already many examples on the Internet of how to start a single MySQL instance in Kubernetes, for serious usage we need to provide:

High Availability: how can we guarantee availability when an instance (or Pod in Kubernetes terminology) crashes or becomes unresponsive?

Persistent storage: we do not want to lose our data in case of instance failure
Backup and recovery

Traffic routing: in the case of multiple instances, how do we direct an application to the correct one
Monitoring

Percona XtraDB Cluster in Kubernetes/OpenShift
Schematically it looks like this:

The picture highlights the components we are going to use

Percona XtraDB Cluster to provide High Availability. Although it is possible to use regular MySQL Replication, the need for automatic master failover makes it quite complicated. Shlomi describes some possible approaches here, and we may implement some of this in Percona Server for MySQL in the future.
(Optional) ProxySQL with proxysql-admin tools
(Optional) Percona Monitoring and Management (PMM) Server with clients installed on each node
Support for backup volumes

Running this in Kubernetes assumes a high degree of automation and minimal manual intervention.
We provide our proof of concept in this project: https://github.com/Percona-Lab/percona-openshift. Please treat it like a source for ideas and as an alpha-quality project, in no way it is production ready.
Details
In our implementation we rely on Helm, the package manager for Kubernetes.  Unfortunately OpenShift does not officially support Helm out of the box, but there is a guide from RedHat on how to make it work.
In the clustering setup, it is quite typical to use a service discovery software like Zookeeper, etcd or Consul. It may become necessary for our Percona XtraDB Cluster deployment, but for now, to simplify deployment, we are going to use the DNS service discovery mechanism provided by Kubernetes. It should be enough for our needs.
We also expect the Kubernetes deployment to provide Dynamic Storage Provisioning. The major cloud providers (like Google Cloud, Microsoft Azure or Amazon Cloud) should have it. Also, it might not be easy to have Dynamic Storage Provisioning for on-premise deployments. You may need to setup GlusterFS or Ceph to provide Dynamic Storage Provisioning.
The challenge with a distributed file system is how many copies of data you will end up having. Percona XtraDB Cluster by itself has three copies, and GlusterFS will also require at least two copies of the data, so in the end we will have six copies of the data. This can’t be good for write intensive applications, but it’s also not good from the capacity standpoint.
One possible approach is to have local data copies for Percona XtraDB Cluster deployments. This will provide better performance and less impact on the network, but in the case of a big dataset (100GB+ ) the node failure will require SST with a big impact on the cluster and network. So the individual solution should be tailored for your workload and your requirements.
Now, as we have a basic setup working, it would be good to understand the performance impact of running Percona XtraDB Cluster in Kubernetes.  Is the network and storage overhead acceptable or it is too big? We plan to look into this in the future.
Once again, our project is located at https://github.com/Percona-Lab/percona-openshift, we are looking for your feedback and for your experience of running databases in Kubernetes/OpenShift.
Before you leave …
Percona XtraDB Cluster
If this article has interested you and you would like to know more about Percona XtraDB Cluster, you might enjoy our recent series of webinar tutorials that introduce this software and how to use it.
The post Running Percona XtraDB Cluster in Kubernetes/OpenShift appeared first on Percona Database Performance Blog.

Percona XtraBackup 2.4.12 Is Now Available

Percona announces the GA release of Percona XtraBackup 2.4.12 on June 22, 2018. You can download it from our download site and apt and yum repositories.
Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, it drives down backup costs while providing unique features for MySQL backups.
New features and improvements:

Percona XtraBackup now prints used arguments to standard output. Bug fixed PXB-1494.

Bugs fixed

xtrabackup –copy-back didn’t read which encryption plugin to use from plugin-load setting of the my.cnf configuration file. Bug fixed PXB-1544.
xbstream was exiting with zero return code when it failed to create one or more target files instead of returning error code 1. Bug fixed PXB-1542.
Meeting a zero sized keyring file, Percona XtraBackup was removing and immediately recreating it, which could affect external software noticing this file had undergo manipulations. Bug fixed PXB-1540.

xtrabackup_checkpoints files were encrypted during a backup, which caused additional difficulties to take incremental backups. Bug fixed PXB-202.

Other bugs fixed: PXB-1526 “Test kill_long_selects.sh failing with MySQL 5.7.21”.
Release notes with all the improvements for version 2.4.12 are available in our online documentation. Please report any bugs to the issue tracker.
The post Percona XtraBackup 2.4.12 Is Now Available appeared first on Percona Database Performance Blog.

ProxySQL Experimental Feature: Native ProxySQL Clustering

ProxySQL 1.4.2 introduced native clustering, allowing several ProxySQL instances to communicate with and share configuration updates with each other. In this blog post, I’ll review this new feature and how we can start working with 3 nodes.
Before I continue, let’s review two common methods to installing ProxySQL.
ProxySQL as a centralized server
This is the most common installation, where ProxySQL is between application servers and the database. It is simple, but without any high availability. If ProxySQL goes down you lose all connectivity to the database.

ProxySQL on app instances
Another common setup is to install ProxySQL onto each application server. This is good because the loss of one ProxySQL/App server will not bring down the entire application.

For more information about the previous installation, please visit this link Where Do I Put ProxySQL?
Sometimes our application and databases grow fast. Maybe you need add a loadbalancer, for example, and in that moment you start thinking … “What could I do to configure and maintain all these ProxySQL nodes without mistakes?”
To do that, there are many tools like Ansible, Puppet, and Chef, but you will need write/create/maintain scripts to do those tasks. This is really difficult to administer for one person.
Now, there is a native solution, built into ProxySQL, to create and administer a cluster in an easy way.
At the moment this feature is EXPERIMENTAL and subject to change. Think very carefully before installing it in production, in fact I strongly recommend you wait. However, if you would like to start testing this feature, you need to install ProxySQL 1.4.2, or better.
This clustering feature is really useful if you have installed one ProxySQL per application instance, because all the changes in one of the ProxySQL nodes will be propagated to all the other ProxySQL nodes. You can also configure a “master-slave” style setup with ProxySQL clustering.
There are only 4 tables where you can make changes and propagate the configuration:

mysql_query_rules
mysql_servers
mysql_users
proxysql_servers

How does it work?
It’s easy. When you make a change like INSERT/DELETE/UPDATE on any of these tables, after running the command
LOAD … TO RUNTIME , ProxySQL creates a new checksum of the table’s data and increments the version number in the table runtime_checksums_values. Below we can see an example.admin ((none))>SELECT name, version, FROM_UNIXTIME(epoch), checksum FROM runtime_checksums_values ORDER BY name;
+——————-+———+———————-+——————–+
| name | version | FROM_UNIXTIME(epoch) | checksum |
+——————-+———+———————-+——————–+
| admin_variables | 0 | 1970-01-01 00:00:00 | |
| mysql_query_rules | 1 | 2018-04-26 15:58:23 | 0x0000000000000000 |
| mysql_servers | 1 | 2018-04-26 15:58:23 | 0x0000000000000000 |
| mysql_users | 4 | 2018-04-26 18:36:12 | 0x2F35CAB62143AE41 |
| mysql_variables | 0 | 1970-01-01 00:00:00 | |
| proxysql_servers | 1 | 2018-04-26 15:58:23 | 0x0000000000000000 |
+——————-+———+———————-+——————–+
Internally, all nodes are monitoring and communicating with all the other ProxySQL nodes. When another node detects a change in the checksum and version (both at the same time), each node will get a copy of the table that was modified, make the same changes locally, and apply the new config to RUNTIME to refresh the new config, make it visible to the applications connected and automatically save it to DISK for persistence.

The following setup creates a “synchronous cluster” so any changes to these 4 tables on any ProxySQL server will be replicated to all other ProxySQL nodes. Be careful!
How can I start testing this new feature?
1) To start we need to get at least 2 nodes. Download and install ProxySQL 1.4.2 or higher and start a clean version.
2) On all nodes, we need to update the following global variables. These changes will set the username and password used by each node’s internal communication to cluster1/clusterpass. These must be the same on all nodes in this cluster.
update global_variables set variable_value=’admin:admin;cluster1:clusterpass’ where variable_name=’admin-admin_credentials’;
update global_variables set variable_value=’cluster1′ where variable_name=’admin-cluster_username’;
update global_variables set variable_value=’clusterpass’ where variable_name=’admin-cluster_password’;
update global_variables set variable_value=200 where variable_name=’admin-cluster_check_interval_ms’;
update global_variables set variable_value=100 where variable_name=’admin-cluster_check_status_frequency’;
update global_variables set variable_value=’true’ where variable_name=’admin-cluster_mysql_query_rules_save_to_disk’;
update global_variables set variable_value=’true’ where variable_name=’admin-cluster_mysql_servers_save_to_disk’;
update global_variables set variable_value=’true’ where variable_name=’admin-cluster_mysql_users_save_to_disk’;
update global_variables set variable_value=’true’ where variable_name=’admin-cluster_proxysql_servers_save_to_disk’;
update global_variables set variable_value=3 where variable_name=’admin-cluster_mysql_query_rules_diffs_before_sync’;
update global_variables set variable_value=3 where variable_name=’admin-cluster_mysql_servers_diffs_before_sync’;
update global_variables set variable_value=3 where variable_name=’admin-cluster_mysql_users_diffs_before_sync’;
update global_variables set variable_value=3 where variable_name=’admin-cluster_proxysql_servers_diffs_before_sync’;
load admin variables to RUNTIME;
save admin variables to disk;
3) Add all IPs from the other ProxySQL nodes into each other node:
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES (‘10.138.180.183′,6032,100,’PRIMARY’);
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES (‘10.138.244.108′,6032,99,’SECONDARY’);
INSERT INTO proxysql_servers (hostname,port,weight,comment) VALUES (‘10.138.244.244′,6032,98,’SECONDARY’);
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;
At this moment, we have all nodes synced.
In the next example from the log file, we can see when node1 detected node2.
[root@proxysql1 ~]# $ tail /var/lib/proxysql/proxysql.log

2018-05-10 11:19:51 [INFO] Cluster: Fetching ProxySQL Servers from peer 10.138.244.108:6032 started
2018-05-10 11:19:51 [INFO] Cluster: Fetching ProxySQL Servers from peer 10.138.244.108:6032 completed
2018-05-10 11:19:51 [INFO] Cluster: Loading to runtime ProxySQL Servers from peer 10.138.244.108:6032
2018-05-10 11:19:51 [INFO] Destroyed Cluster Node Entry for host 10.138.148.242:6032
2018-05-10 11:19:51 [INFO] Cluster: Saving to disk ProxySQL Servers from peer 10.138.244.108:6032
2018-05-10 11:19:52 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 10.138.180.183:6032, version 6, epoch 1525951191, checksum 0x3D819A34C06EF4EA . Not syncing yet …
2018-05-10 11:19:52 [INFO] Cluster: checksum for proxysql_servers from peer 10.138.180.183:6032 matches with local checksum 0x3D819A34C06EF4EA , we won’t sync.
2018-05-10 11:19:52 [INFO] Cluster: closing thread for peer 10.138.148.242:6032
2018-05-10 11:19:52 [INFO] Cluster: detected a new checksum for proxysql_servers from peer 10.138.244.244:6032, version 4, epoch 1525951163, checksum 0x3D819A34C06EF4EA . Not syncing yet …
2018-05-10 11:19:52 [INFO] Cluster: checksum for proxysql_servers from peer 10.138.244.244:6032 matches with local checksum 0x3D819A34C06EF4EA , we won’t sync

Another example is to add users to the table mysql_users. Remember these users are to enable MySQL connections between the application (frontend) and MySQL (backend).
We will add a new username and password on any server; in my test I’ll use node2:
admin proxysql2 ((none))>INSERT INTO mysql_users(username,password) VALUES (‘user1′,’crazyPassword’);
Query OK, 1 row affected (0.00 sec)
admin proxysql2 ((none))>LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
In the log file from node3, we can see the update immediately:
[root@proxysql3 ~]# $ tail /var/lib/proxysql/proxysql.log

2018-05-10 11:30:57 [INFO] Cluster: detected a new checksum for mysql_users from peer 10.138.244.108:6032, version 2, epoch 1525951873, checksum 0x2AF43564C9985EC7 . Not syncing yet …
2018-05-10 11:30:57 [INFO] Cluster: detected a peer 10.138.244.108:6032 with mysql_users version 2, epoch 1525951873, diff_check 3. Own version: 1, epoch: 1525950968. Proceeding with remote sync
2018-05-10 11:30:57 [INFO] Cluster: detected a peer 10.138.244.108:6032 with mysql_users version 2, epoch 1525951873, diff_check 4. Own version: 1, epoch: 1525950968. Proceeding with remote sync
2018-05-10 11:30:57 [INFO] Cluster: detected peer 10.138.244.108:6032 with mysql_users version 2, epoch 1525951873
2018-05-10 11:30:57 [INFO] Cluster: Fetching MySQL Users from peer 10.138.244.108:6032 started
2018-05-10 11:30:57 [INFO] Cluster: Fetching MySQL Users from peer 10.138.244.108:6032 completed
2018-05-10 11:30:57 [INFO] Cluster: Loading to runtime MySQL Users from peer 10.138.244.108:6032
2018-05-10 11:30:57 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 10.138.244.108:6032
2018-05-10 11:30:57 [INFO] Cluster: detected a new checksum for mysql_users from peer 10.138.244.244:6032, version 2, epoch 1525951857, checksum 0x2AF43564C9985EC7 . Not syncing yet …
2018-05-10 11:30:57 [INFO] Cluster: checksum for mysql_users from peer 10.138.244.244:6032 matches with local checksum 0x2AF43564C9985EC7 , we won’t sync.
2018-05-10 11:30:57 [INFO] Cluster: detected a new checksum for mysql_users from peer 10.138.180.183:6032, version 2, epoch 1525951886, checksum 0x2AF43564C9985EC7 . Not syncing yet …
2018-05-10 11:30:57 [INFO] Cluster: checksum for mysql_users from peer 10.138.180.183:6032 matches with local checksum 0x2AF43564C9985EC7 , we won’t sync.

What happens if some node is down?
In this example, we will see and find out what happens if one node is down or has a network glitch, or other issue. I’ll stop ProxySQL node3:
[root@proxysql3 ~]# service proxysql stop
Shutting down ProxySQL: DONE!
On ProxySQL node1, we can check that node3 is unreachable:
[root@proxysql1 ~]# tailf /var/lib/proxysql/proxysql.log
2018-05-10 11:57:33 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.138.244.244:6032 . Error: Can’t connect to MySQL server on ‘10.138.244.244’ (107)
2018-05-10 11:57:33 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.138.244.244:6032 . Error: Can’t connect to MySQL server on ‘10.138.244.244’ (107)
2018-05-10 11:57:33 ProxySQL_Cluster.cpp:180:ProxySQL_Cluster_Monitor_thread(): [WARNING] Cluster: unable to connect to peer 10.138.244.244:6032 . Error: Can’t connect to MySQL server on ‘10.138.244.244’ (107)
And another check can be run in any ProxySQL node like node2, for example:
admin proxysql2 ((none))>SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name=’proxysql_servers’ ORDER BY hostname;
+—————-+——————–+———————+———————+
| hostname | checksum | changed_at | updated_at |
+—————-+——————–+———————+———————+
| 10.138.180.183 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 | 2018-05-10 12:01:59 |
| 10.138.244.108 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:38 | 2018-05-10 12:01:59 |
| 10.138.244.244 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 | 2018-05-10 11:56:59 |
+—————-+——————–+———————+———————+
3 rows in set (0.00 sec)
In the previous result, we can see node3 (10.138.244.244) is not being updated; the column updated_at should have a later datetime. This means that node3 is not running (or is down or network glitch).
At this point, any change to any of the tables, mysql_query_rules, mysql_servers, mysql_users, proxysql_servers, will be replicated between nodes 1 & 2.
In this next example, while node3 is offline, we will add another user to mysql_users table.
admin proxysql2 ((none))>INSERT INTO mysql_users(username,password) VALUES (‘user2′,’passwordCrazy’);
Query OK, 1 row affected (0.00 sec)
admin proxysql2 ((none))>LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
That change was propagated to node1:
[root@proxysql3 ~]# $ tail /var/lib/proxysql/proxysql.log

2018-05-10 12:12:36 [INFO] Cluster: detected a peer 10.138.244.108:6032 with mysql_users version 3, epoch 1525954343, diff_check 4. Own version: 2, epoch: 1525951886. Proceeding with remote sync
2018-05-10 12:12:36 [INFO] Cluster: detected peer 10.138.244.108:6032 with mysql_users version 3, epoch 1525954343
2018-05-10 12:12:36 [INFO] Cluster: Fetching MySQL Users from peer 10.138.244.108:6032 started
2018-05-10 12:12:36 [INFO] Cluster: Fetching MySQL Users from peer 10.138.244.108:6032 completed
2018-05-10 12:12:36 [INFO] Cluster: Loading to runtime MySQL Users from peer 10.138.244.108:6032
2018-05-10 12:12:36 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 10.138.244.108:6032

We keep seeing node3 is out of sync about 25 minutes ago.
admin proxysql2 ((none))>SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name=’mysql_users’ ORDER BY hostname;
+—————-+——————–+———————+———————+
| hostname | checksum | changed_at | updated_at |
+—————-+——————–+———————+———————+
| 10.138.180.183 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 | 2018-05-10 12:21:35 |
| 10.138.244.108 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:38 |2018-05-10 12:21:35 |
| 10.138.244.244 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 |2018-05-10 12:21:35 |
+—————-+——————–+———————+———————+
3 rows in set (0.00 sec)
Let’s start node3 and check if the sync works. node3 should connect to the other nodes and get the last changes.
[root@proxysql3 ~]# tail /var/lib/proxysql/proxysql.log

2018-05-10 12:30:02 [INFO] Cluster: detected a peer 10.138.244.108:6032 with mysql_users version 3, epoch 1525954343, diff_check 3. Own version: 1, epoch: 1525955402. Proceeding with remote sync
2018-05-10 12:30:02 [INFO] Cluster: detected a peer 10.138.180.183:6032 with mysql_users version 3, epoch 1525954356, diff_check 3. Own version: 1, epoch: 1525955402. Proceeding with remote sync

2018-05-10 12:30:03 [INFO] Cluster: detected peer 10.138.180.183:6032 with mysql_users version 3, epoch 1525954356
2018-05-10 12:30:03 [INFO] Cluster: Fetching MySQL Users from peer 10.138.180.183:6032 started
2018-05-10 12:30:03 [INFO] Cluster: Fetching MySQL Users from peer 10.138.180.183:6032 completed
2018-05-10 12:30:03 [INFO] Cluster: Loading to runtime MySQL Users from peer 10.138.180.183:6032
2018-05-10 12:30:03 [INFO] Cluster: Saving to disk MySQL Query Rules from peer 10.138.180.183:6032
Looking at the status from the checksum table, we can see node3 is now up to date.
admin proxysql2 ((none))>SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name=’mysql_users’ ORDER BY hostname;
+—————-+——————–+———————+———————+
| hostname | checksum | changed_at | updated_at |
+—————-+——————–+———————+———————+
| 10.138.180.183 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 | 2018-05-10 12:21:35 |
| 10.138.244.108 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:38 |2018-05-10 12:21:35 |
| 10.138.244.244 | 0x3D819A34C06EF4EA | 2018-05-10 11:19:39 |2018-05-10 12:21:35 |
+—————-+——————–+———————+———————+
3 rows in set (0.00 sec)admin proxysql2 ((none))>SELECT hostname, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at FROM stats_proxysql_servers_checksums WHERE name=’mysql_users’ ORDER BY hostname;
+—————-+——————–+———————+———————+
| hostname | checksum | changed_at | updated_at |
+—————-+——————–+———————+———————+
| 10.138.180.183 | 0x3928F574AFFF4C65 | 2018-05-10 12:12:24 | 2018-05-10 12:31:58 |
| 10.138.244.108 | 0x3928F574AFFF4C65 | 2018-05-10 12:12:23 | 2018-05-10 12:31:58 |
| 10.138.244.244 | 0x3928F574AFFF4C65 | 2018-05-10 12:30:19 | 2018-05-10 12:31:58 |
+—————-+——————–+———————+———————+
3 rows in set (0.00 sec)
Now we have 3 ProxySQL nodes up to date. This example didn’t add any MySQL servers, hostgroups, etc, because the functionality is the same. The post is intended as an introduction to this new feature and how you can create and test a ProxySQL cluster.
Just remember that this is still an experimental feature and is subject to change with newer versions of ProxySQL.
Summary
This feature is really needed if you have more than one ProxySQL running for the same application in different instances. It is easy to maintain and configure for a single person and is easy to create and attach new nodes.
Hope you find this post helpful!
References
http://www.proxysql.com/blog/proxysql-clusterhttp://www.proxysql.com/blog/proxysql-cluster-part2http://www.proxysql.com/blog/proxysql-cluster-part3-mysql-servershttps://github.com/sysown/proxysql/wiki/ProxySQL-Cluster
The post ProxySQL Experimental Feature: Native ProxySQL Clustering appeared first on Percona Database Performance Blog.

Percona Server for MySQL 5.6.40-84.0 Is Now Available

Percona announces the release of Percona Server for MySQL 5.6.40-84.0 on May 30, 2018 (downloads are available here and from the Percona Software Repositories). Based on MySQL 5.6.40, including all the bug fixes in it, Percona Server for MySQL 5.6.40-84.0 is now the current GA release in the 5.6 series. All of Percona’s software is open-source and free.
New Features

A new string variable version_suffix allows to change suffix for the Percona Server version string returned by the read-only version variable. This allows to append the version number for the server with a custom suffix to reflect some build or configuration specifics. Also version_comment (default value of which is taken from the CMake COMPILATION_COMMENT option) is converted from a global read-only to a global read-write variable and thereby it is now cutomizable.
Query response time plugin now can be disabled at session level with use of a new variable query_response_time_session_stats.

Bugs Fixed

Compilation warning was fixed for -DWITH_QUERY_RESPONSE_TIME=ON CMake compilation option, which makes QRT to be linked statically. Bug fixed #3841.
A code clean-up was done to fix clang 6 specific compilation warnings and errors (bug fixed #3893, upstream #90111).
Using -DWITHOUT_<PLUGIN>=ON CMake variable to exclude a plugin from the build didn’t work for some plugins, including a number of storage engines. Bug fixed #3901.
A clean-up in Percona Server binlog-related code was made to avoid uninitialized memory comparison. Bug fixed #3925 (upstream #90238).
Temporary file I/O was not instrumented for Performance Schema. Bug fixed  #3937  (upstream  #90264).
A key_block_size value was set automatically by the Improved MEMORY Storage Engine, which resulted in warnings when changing the engine type to InnoDB, and constantly growing key_block_size during alter operations. Bugs fixed #3936, #3940, and #3943.
Percona Server Debian packages description included reference to /etc/mysql/my.cnf file, which is not actually present in these packages. Bug fixed #2046.
Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build, retaining compatibility with old compiler versions, including GCC 4.4. Bugs fixed #3950 and #4471.
A typo in plugin.cmake file prevented to compile plugins statically into the server. Bug fixed #3871 (upstream #89766).
-DWITH_NUMA=ON build option was silently ignored by CMake when NUMA development package was not installed, instead of exiting by error. Bug fixed #4487.
Variables innodb_buffer_pool_populate and numa_interleave mapped to the upstream innodb_numa_interleave variable in 5.6.27-75.0 were reverted to their original implementation due to upstream variant being less effective in memory allocation. Now buffer pool is allocated with MAP_POPULATE, forcing NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
Synchronization between between innodb_kill_idle_transaction and kill_idle_transaction system variables was broken because of the regression in Percona Server 5.6.40-83.2. Bug fixed #3955.
Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
ALTER TABLE … COMMENT = … statement caused TokuDB to rebuild the whole table, which is not needed, as only FRM metadata should be changed. The fix was provided as a contribution by Fungo Wang. Bugs fixed #4280 and #4292.
A number of Percona Server 8.0 TokuDB fixes have been backported to Percona Server 5.6 in preparation for using MySQL 8.0. Bugs fixed  #4379,  #4380,  #4387,  #4378,  #4383,  #4384,  #4386,  #4382, #4391, #4390, #4392, and #4381.

TokuDB Changes and Fixes

Two new variables, tokudb_enable_fast_update and tokudb_enable_fast_upsert, were introduced to facilitate the TokuDB fast updates feature, which involves queries optimization to avoid random reads during their execution. Bug fixed #4365.
A data race was fixed in minicron utility of the PerconaFT, as a contribution by Rik Prohaska. Bug fixed #4281.
Row count and cardinality decrease to zero took place after long-running REPLACE load, ending up with full table scans for any action.

Other Bugs Fixed

#3818 “Orphaned file mysql-test/suite/innodb/r/percona_innodb_kill_idle_trx.result”

#3926 “Potentially truncated bitmap file name in log_online_open_bitmap_file_read_only() (storage/innobase/log/log0online.cc)”

#2204 “Test main.audit_log_default_db is unstable”

#3767 “Fix compilation warnings/errors with clang”

#3773 “Incorrect key file for table frequently for tokudb”

#3794 “MTR test main.percona_show_temp_tables_stress does not wait for events to start”

#3798 “MTR test innodb.percona_extended_innodb_status fails if InnoDB status contains unquoted special characters”

#3887 “TokuDB does not compile with -DWITH_PERFSCHEMA_STORAGE_ENGINE=OFF”

#4388 “5.7 code still has TOKU_INCLUDE_OPTION_STRUCTS which is a MariaDB specific construct”

#4265 “TDB-114 (Change use of MySQL HASH to unordered_map) introduces memory leak”

#4277 “memory leaks in TDB-2 and TDB-89 tests”

#4276 “Data race on cache table attributes detected by the thread sanitizer”

#4451 “Implement better compression algo testing”

#4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”

#4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”

#1131 “User_var_log_event::User_var_log_event(const char*, uint, const Format_description_log_event*): Assertion `(bytes_read == (data_written – ((old_pre_checksum_fd || (description_event->checksum_alg == BINLOG_CHECKSUM_ALG_OFF)) ? 0 : 4))) || ((“.

Find the release notes for Percona Server for MySQL 5.6.40-84.0 in our online documentation. Report bugs in the Jira bug tracker.
The post Percona Server for MySQL 5.6.40-84.0 Is Now Available appeared first on Percona Database Performance Blog.

Capturing Per-Process Metrics with Percona Monitoring and Management (PMM)

In this blog post, I will show you how to use Percona Monitoring and Management (PMM) to capture per-process metrics in five minutes or less.
While Percona Monitoring and Management (PMM) captures a lot of host metrics, it currently falls short providing per-process information, such as which particular process uses a lot of CPU resources, causes Disk IO or consumes a lot of memory.
In our database performance optimization and troubleshooting practice, this information has proven quite useful in many cases: batch jobs taking much more resources than developers would estimate and misconfigured Percona XtraBackup or Percona Toolkit are among the most common offenders.
Per-process metrics information can also be very helpful when troubleshooting database software memory leaks or memory fragmentation.
You don’t know which processes cause you problems at the outset, so it is important to capture information about all of the processes (or specifically exclude the processes you do not want to capture information about) rather than capture information about selected few.
While capturing such helpful information is not available in PMM out of the box (yet), you can easily achieve it using PMM’s External Exporter support and the excellent Prometheus Process Exporter by Nick Cabatoff.
These instructions are for Debian/Ubuntu  Linux Distributions but they should work with RedHat/CentOS based versions as well – just use RPM package instead of DEB
1: Download the process exporter packages from GitHub:
wget https://github.com/ncabatoff/process-exporter/releases/download/v0.2.11/process-exporter_0.2.11_linux_amd64.deb
2: Install the package
(Note: the file will be different depending on the platform and current release.)
dpkg -i process-exporter_0.2.11_linux_amd64.deb
3: Run the Exporter
service process-exporter start
4: Register Exporter with Percona Monitoring and Management
Assuming the current node is already monitored by PMM you just need one command:
pmm-admin add external:service processes-my-host –service-port=9256 –interval=10s
This captures process metrics every 10 seconds (adjust interval if desired).
Important note: due to some internal limitations, you need to use a different service name (“processes-my-host”)  for each host. I suggest just adding the hostname to the descriptive name “processes” for simplicity.
5: Get Matching Dashboard from Grafana.com
While you can browse the data captured by the Advanced Data Exploration Dashboard, it is not any fun. I created a PMM-style dashboard and published it on Grafana.com. I based it on Nick’s original dashboard.
To add this dashboard to your PMM Server, click Dashboard Search on your PMM Server.

From there, click on “Import Dashboard”. Use 6033 as the Grafana.com Dashboard ID.

6: You’re done!
You should have data flowing, and you should be able to see the data on the graphs.

In this example, I have pt-query-digest (shown as Perl) parsing the log file and pushing MySQL Server away from memory.
Note, as you likely have many processes on the system, the graphs are designed to show only the top processes. All running processes, however, are available in the drop-down if you want to access the history for a specific process.
Let us know what you think. We are looking at how to integrate this functionality directly into Percona Monitoring and Management!
The post Capturing Per-Process Metrics with Percona Monitoring and Management (PMM) appeared first on Percona Database Performance Blog.

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