Buy Percona SupportBuy Now

I’m Colin Charles, and I’m here to evangelize open source databases!

Latest MySQL Performance Blog posts - August 16, 2016 - 7:02am

Let me introduce myself, I’m Colin Charles.

Percona turns ten years old this year. To me, there is no better time to join the company as the Chief Evangelist in the CTO office.

I’ve been in the MySQL world a tad longer than Percona has, and have had the pleasure of working on MySQL at MySQL AB and Sun Microsystems. Most recently I was one of the founding team members for MariaDB Server in 2009. I watched that grow into the MariaDB Corporation (after the merger with SkySQL) and the MariaDB Foundation.

For me, it’s about the right server for the right job. Today they all support a myriad of different features and different storage engines. Each server has its own community that supports and discusses their pros and cons. This is now true for both the MySQL and MongoDB ecosystems.

I’ve always had a lot of respect for the work Percona does — pragmatic engineering, deeply technical consulting (and blog posts) and amazing conferences. A big deal for me, and a big reason why I’m now here, is that Percona truly believes in the spirit of open source software development. Their obvious support of the open source community is a great pull factor for users as well.

I just spent time on the Percona Live Europe conference committee. (I’ve been involved in MySQL-related conferences since 2006, and was even Program Chair for a couple of years). There, I got to see how the conference is evolving beyond just stock MySQL to also include MongoDB and other open source databases

Recently I visited a customer who was not just interested in using a database, but also in offering a database-as-a-service to their internal customers. I discussed OpenStack with them, and knowing that Percona, the company I now represent, can support the architecture and deployment too? That’s kind of priceless.

We’re all crazy about databases and their position in the overall IT structure. They provide us with cool apps, internet functionality, and all sorts of short cuts to our daily lives. Percona’s role in providing solutions that address the issues that infrastructure faces is what really excites me about my new journey here.

percona 5.7 use 100% one cpu core without and queries.

Lastest Forum Posts - August 16, 2016 - 1:59am
I run latest percona 5.7 from official repo on ubuntu 16.04 . I have same problem on 2 servers.
Mysql dont have any queries in queue, iotop dont show any disk io, service mysql restart dont help.


mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
| 4 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | 0 |
+----+------+-----------+------+---------+------+----------+------------------+-----------+---------------+
1 row in set (0.00 sec)

Htop
https://habrastorage.org/files/d50/2...d8aefb61d7.png

Top
https://habrastorage.org/files/5de/b...786f42f377.png

Iotop
https://habrastorage.org/files/282/9...e4c648b000.png

strace -p 32183
strace: Process 32183 attached
strace: [ Process PID=32183 runs in x32 mode. ]
strace: [ Process PID=32183 runs in 64 bit mode. ]
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6888) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6890) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6892) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6894) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6896) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1
futex(0x2f3deec, FUTEX_CMP_REQUEUE_PRIVATE, 1, 2147483647, 0x2f3dec0, 6898) = 1
futex(0x2f3dec0, FUTEX_WAKE_PRIVATE, 1) = 1

is it possible to merge multiple incrementals as single one?

Lastest Forum Posts - August 15, 2016 - 11:17pm
Say there are base, inc1, inc2, inc3.... Can I create another new incremental = inc1 + inc2 + inc3?

Percona Live Europe 2016 Schedule Now Live

Latest MySQL Performance Blog posts - August 15, 2016 - 10:01am

This post reveals the full Percona Live Europe 2016 schedule for Amsterdam this October 3-5.

The official The Percona Live Open Source Database Conference Europe 2016 schedule is now live, and you can find it here.

The schedule demonstrates that this conference has something for everyone! Whether your interest is in MySQL, MongoDB or other open source database, there are talks that will interest you.

The Percona Live Open Source Database Conference is the premier event for the diverse and active open source database community, as well as businesses that develop and use open source database software. The conferences have a technical focus with an emphasis on the core topics of MySQL, MongoDB, and other open source databases. Tackling subjects such as analytics, architecture and design, security, operations, scalability and performance, Percona Live provides in-depth discussions for your high-availability, IoT, cloud, big data and other changing business needs. This conference is an opportunity to network with peers and technology professionals by bringing together accomplished DBA’s, system architects and developers from around the world to share their knowledge and experience – all to help you learn how to tackle your open source database challenges in a whole new way.

Some of the talks for each area are:

MySQL

MongoDB

Open Source Databases

Check out the full schedule now!

 

Advanced Tickets

Purchase your passes now and get the advanced tickets discount. The earlier you buy, the better the value. You can register for Percona Live Europe here.

Sponsor Percona Live

Sponsor the Percona Live Open Source Database Performance Conference Europe 2016. Sponsorship gets you bigger visibility at the most important open source database conference in Europe. Benefits to sponsorship include:

  • Worldwide Audience: Made up of DBAs, developers, CTOs, CEOs, technology evangelists, entrepreneurs, and technology vendors.
  • Perfect Location: In Amsterdam City Centre, walking distance from Amsterdam Central Station.
  • Perfect Event: The showcase event for the rich and diverse MySQL, MongoDB and open source database markets in Europe.

Click here to sponsor now.

Tuning Linux for MongoDB

Latest MySQL Performance Blog posts - August 12, 2016 - 12:36pm

In this post, we’ll discuss tuning Linux for MongoDB deployments.

By far the most common operating system you’ll see MongoDB running on is Linux 2.6 and 3.x. Linux flavors such as CentOS and Debian do a fantastic job of being a stable, general-purpose operating system. Linux runs software on hardware ranging from tiny computers like the Raspberry Pi up to massive data center servers. To make this flexibility work, however, Linux defaults to some “lowest common denominator” tunings so that the OS will boot on anything.

Working with databases, we often focus on the queries, patterns and tunings that happen inside the database process itself. This means we sometimes forget that the operating system below it is the life-support of database, the air that it breathes so-to-speak. Of course, a highly-scalable database such as MongoDB runs fine on these general-purpose defaults without complaints, but the efficiency can be equivalent to running in regular shoes instead of sleek runners. At small scale, you might not notice the lost efficiency, but at large scale (especially when data exceeds RAM) improved tunings equate to fewer servers and less operational costs. For all use cases and scale, good OS tunings also provide some improvement in response times and removes extra “what if…?” questions when troubleshooting.

Overall, memory, network and disk are the system resources important to MongoDB. This article covers how to optimize each of these areas. Of course, while we have successfully deployed these tunings to many live systems, it’s always best to test before applying changes to your servers.

If you plan on applying these changes, I suggest performing them with one full reboot of the host. Some of these changes don’t require a reboot, but test that they get re-applied if you reboot in the future. MongoDB’s clustered nature should make this relatively painless, plus it might be a good time to do that dreaded “yum upgrade” / “aptitude upgrade“, too.

Linux Ulimit

To prevent a single user from impacting the entire system, Linux has a facility to implement some system resource constraints on processes, file handles and other system resources on a per-user-basis. For medium-high-usage MongoDB deployments, the default limits are almost always too low. Considering MongoDB generally uses dedicated hardware, it makes sense to allow the Linux user running MongoDB (e.g., “mongod”) to use a majority of the available resources.

Now you might be thinking: “Why not disable the limit (or set it to unlimited)?” This is a common recommendation for database servers. I think you should avoid this for two reasons:

  • If you hit a problem, a lack of a limit on system resources can allow a relatively smaller problem to spiral out of control, often bringing down other services (such as SSH) crucial to solving the original problem.
  • All systems DO have an upper-limit, and understanding those limitations instead of masking them is an important exercise.

In most cases, a limit of 64,000 “max user processes” and 64,000 “open files” (both have defaults of 1024) will suffice. To be more exact you need to do some math on the number of applications/clients, the maximum size of their connection pools and some case-by-case tuning for the number of inter-node connections between replica set members and sharding processes. (We might address this in a future blog post.)

You can deploy these limits by adding a file in “/etc/security/limits.d” (or appending to “/etc/security/limits.conf” if there is no “limits.d”). Below is an example file for the Linux user “mongod”, raising open-file and max-user-process limits to 64,000:

mongod       soft        nproc        64000 mongod       hard        nproc        64000 mongod       soft        nofile       64000 mongod       hard        nofile       64000

Note: this change only applies to new shells, meaning you must restart “mongod” or “mongos” to apply this change!

Virtual Memory Dirty Ratio

The “dirty_ratio” is the percentage of total system memory that can hold dirty pages. The default on most Linux hosts is between 20-30%. When you exceed the limit the dirty pages are committed to disk, creating a small pause. To avoid this hard pause there is a second ratio: “dirty_background_ratio” (default 10-15%) which tells the kernel to start flushing dirty pages to disk in the background without any pause.

20-30% is a good general default for “dirty_ratio”, but on large-memory database servers this can be a lot of memory! For example, on a 128GB-memory host this can allow up to 38.4GB of dirty pages. The background ratio won’t kick in until 12.8GB! We recommend that you lower this setting and monitor the impact to query performance and disk IO. The goal is reducing memory usage without impacting query performance negatively. Reducing caches sizes also guarantees data gets written to disk in smaller batches more frequently, which increases disk throughput (than huge bulk writes less often).

A recommended setting for dirty ratios on large-memory (64GB+ perhaps) database servers is: “vm.dirty_ratio = 15″ and vm.dirty_background_ratio = 5″, or possibly less. (Red Hat recommends lower ratios of 10 and 3 for high-performance/large-memory servers.)

You can set this by adding the following lines to /etc/sysctl.conf”:

vm.dirty_ratio = 15 vm.dirty_background_ratio = 5

To check these current running values:

$ sysctl -a | egrep "vm.dirty.*_ratio" vm.dirty_background_ratio = 5 vm.dirty_ratio = 15

Swappiness

“Swappiness” is a Linux kernel setting that influences the behavior of the Virtual Memory manager when it needs to allocate a swap, ranging from 0-100. A setting of 0 tells the kernel to swap only to avoid out-of-memory problems. A setting of 100 tells it to swap aggressively to disk. The Linux default is usually 60, which is not ideal for database usage.

It is common to see a setting of 0″ (or sometimes “10”) on database servers, telling the kernel to prefer to swap to memory for better response times. However, Ovais Tariq details a known bug (or feature) when using a setting of 0 in this blog post: https://www.percona.com/blog/2014/04/28/oom-relation-vm-swappiness0-new-kernel/.

Due to this bug, we recommended using a setting of 1″ (or “10” if you  prefer some disk swapping) by adding the following to your /etc/sysctl.conf”:

vm.swappiness = 1

To check the current swappiness:

$ sysctl vm.swappiness vm.swappiness = 1

Note: you must run the command “/sbin/sysctl -p” as root/sudo (or reboot) to apply a dirty_ratio or swappiness change!

Transparent HugePages

*Does not apply to Debian/Ubuntu or CentOS/RedHat 5 and lower*

Transparent HugePages is an optimization introduced in CentOS/RedHat 6.0, with the goal of reducing overhead on systems with large amounts of memory. However, due to the way MongoDB uses memory, this feature actually does more harm than good as memory access are rarely contiguous.

Disabled THP entirely by adding the following flag below to your Linux kernel boot options:

transparent_hugepage=never

Usually this requires changes to the GRUB boot-loader config in the directory /boot/grub” or /etc/grub.d” on newer systems. Red Hat covers this in more detail in this article (same method on CentOS): https://access.redhat.com/solutions/46111.

Note: We recommended rebooting the system to clear out any previous huge pages and validate that the setting will persist on reboot.

NUMA (Non-Uniform Memory Access) Architecture

Non-Uniform Memory Access is a recent memory architecture that takes into account the locality of caches and CPUs for lower latency. Unfortunately, MongoDB is not “NUMA-aware” and leaving NUMA setup in the default behavior can cause severe memory in-balance.

There are two ways to disable NUMA: one is via an on/off switch in the system BIOS config, the 2nd is using the numactl” command to set NUMA-interleaved-mode (similar effect to disabling NUMA) when starting MongoDB. Both methods achieve the same result. I lean towards using the numactl” command due to future-proofing yourself for the mostly inevitable addition of NUMA awareness. On CentOS 7+ you may need to install the numactl” yum/rpm package.

To make mongod start using interleaved-mode, add numactl –interleave=all” before your regular mongod” command:

$ numactl --interleave=all mongod <options here>

To check mongod’s NUMA setting:

$ sudo numastat -p $(pidof mongod) Per-node process memory usage (in MBs) for PID 7516 (mongod) Node 0 Total --------------- --------------- Huge 0.00 0.00 Heap 28.53 28.53 Stack 0.20 0.20 Private 7.55 7.55 ---------------- --------------- --------------- Total 36.29 36.29

If you see only 1 x NUMA-node column (“Node0”) NUMA is disabled. If you see more than 1 x NUMA-node, make sure the metric numbers (Heap”, etc.) are balanced between nodes. Otherwise, NUMA is NOT in “interleave” mode.

Note: some MongoDB packages already ship logic to disable NUMA in the init/startup script. Check for this using “grep” first. Your hardware or BIOS manual should cover disabling NUMA via the system BIOS.

Block Device IO Scheduler and Read-Ahead

For tuning flexibility, we recommended that MongoDB data sits on its own disk volume, preferably with its own dedicated disks/RAID array. While it may complicate backups, for the best performance you can also dedicate a separate volume for the MongoDB journal to separate it’s disk activity noise from the main data set. The journal does not yet have it’s own config/command-line setting, so you’ll need to mount a volume to the journal” directory inside the dbPath. For example, /var/lib/mongo/journal” would be the journal mount-path if the dbPath was set to /var/lib/mongo”.

Aside from good hardware, the block device MongoDB stores its data on can benefit from 2 x major adjustments:

IO Scheduler

The IO scheduler is an algorithm the kernel will use to commit reads and writes to disk. By default most Linux installs use the CFQ (Completely-Fair Queue) scheduler. This is designed to work well for many general use cases, but with little latency guarantees. Two other popular schedulers are deadline” and noop”. Deadline excels at latency-sensitive use cases (like databases) and noop is closer to no scheduling at all.

We generally suggest using the deadline” IO scheduler for cases where you have real, non-virtualised disks under MongoDB. (For example, a “bare metal” server.) In some cases I’ve seen noop” perform better with certain hardware RAID controllers, however. The difference between deadline” and cfq” can be massive for disk-bound deployments.

If you are running MongoDB inside a VM (which has it’s own IO scheduler beneath it) it is best to use noop” and let the virtualization layer take care of the IO scheduling itself.

Read-Ahead

Read-ahead is a per-block device performance tuning in Linux that causes data ahead of a requested block on disk to be read and then cached into the filesystem cache. Read-ahead assumes that there is a sequential read pattern and something will benefit from those extra blocks being cached. MongoDB tends to have very random disk patterns and often does not benefit from the default read-ahead setting, wasting memory that could be used for more hot data. Most Linux systems have a default setting of 128KB/256 sectors (128KB = 256 x 512-byte sectors). This means if MongoDB fetches a 64kb document from disk, 128kb of filesystem cache is used and maybe the extra 64kb is never accessed later, wasting memory.

For this setting, we suggest a starting-point of 32 sectors (=16KB) for most MongoDB workloads. From there you can test increasing/reducing this setting and then monitor a combination of query performance, cached memory usage and disk read activity to find a better balance. You should aim to use as little cached memory as possible without dropping the query performance or causing significant disk activity.

Both the IO scheduler and read-ahead can be changed by adding a file to the udev configuration at /etc/udev/rules.d”. In this example I am assuming the block device serving mongo data is named /dev/sda” and I am setting “deadline” as the IO scheduler and 16kb/32-sectors as read-ahead:

# set deadline scheduler and 16kb read-ahead for /dev/sda ACTION=="add|change", KERNEL=="sda", ATTR{queue/scheduler}="deadline", ATTR{bdi/read_ahead_kb}="16"

To check the IO scheduler was applied ([square-brackets] = enabled scheduler):

$ cat /sys/block/sda/queue/scheduler noop [deadline] cfq

To check the current read-ahead setting:

$ sudo blockdev --getra /dev/sda 32

Note: this change should be applied and tested with a full system reboot!

Filesystem and Options

It is recommended that MongoDB uses only the ext4 or XFS filesystems for on-disk database data. ext3 should be avoided due to its poor pre-allocation performance. If you’re using WiredTiger (MongoDB 3.0+) as a storage engine, it is strongly advised that you ONLY use XFS due to serious stability issues on ext4.

Each time you read a file, the filesystems perform an access-time metadata update by default. However, MongoDB (and most applications) does not use this access-time information. This means you can disable access-time updates on MongoDB’s data volume. A small amount of disk IO activity that the access-time updates cause stops in this case.

You can disable access-time updates by adding the flag noatime” to the filesystem options field in the file /etc/fstab” (4th field) for the disk serving MongoDB data:

/dev/mapper/data-mongodb /var/lib/mongo ext4 defaults,noatime 0 0

Use noatime” to verify the volume is currently mounted:

$ grep "/var/lib/mongo" /proc/mounts /dev/mapper/data-mongodb /var/lib/mongo ext4 rw,seclabel,noatime,data=ordered 0 0

Note: to apply a filesystem-options change, you must remount (umount + mount) the volume again after stopping MongoDB, or reboot.

Network Stack

Several defaults of the Linux kernel network tunings are either not optimal for MongoDB, limit a typical host with 1000mbps network interfaces (or better) or cause unpredictable behavior with routers and load balancers. We suggest some increases to the relatively low throughput settings (net.core.somaxconn and net.ipv4.tcp_max_syn_backlog) and a decrease in keepalive settings, seen below.

Make these changes permanent by adding the following to /etc/sysctl.conf” (or a new file /etc/sysctl.d/mongodb-sysctl.conf – if /etc/sysctl.d exists):

net.core.somaxconn = 4096 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_keepalive_intvl = 30 net.ipv4.tcp_keepalive_time = 120 net.ipv4.tcp_max_syn_backlog = 4096

To check the current values of any of these settings:

$ sysctl net.core.somaxconn net.core.somaxconn = 4096

Note: you must run the command “/sbin/sysctl -p” as root/sudo (or reboot) to apply this change!

NTP Daemon

All of these deeper tunings make it easy to forget about something as simple as your clock source. As MongoDB is a cluster, it relies on a consistent time across nodes. Thus the NTP Daemon should run permanently on all MongoDB hosts, mongos and arbiters included. Be sure to check the time syncing won’t fight with any guest-based virtualization tools like “VMWare tools” and “VirtualBox Guest Additions”.

This is installed on RedHat/CentOS with:

$ sudo yum install ntp

And on Debian/Ubuntu:

$ sudo apt-get install ntp

Note: Start and enable the NTP Daemon (for starting on reboots) after installation. The commands to do this vary by OS and OS version, so please consult your documentation.

Security-Enhanced Linux (SELinux)

Security-Enhanced Linux is a kernel-level security access control module that has an unfortunate tendency to be disabled or set to warn-only on Linux deployments. As SELinux is a strict access control system, sometimes it can cause unexpected errors (permission denied, etc.) with applications that were not configured properly for SELinux. Often people disable SELinux to resolve the issue and forget about it entirely. While implementing SELinux is not an end-all solution, it massively reduces the local attack surface of the server. We recommend deploying MongoDB with SELinus Enforcing” mode on.

The modes of SELinux are:

  1. Enforcing – Block and log policy violations.
  2. Permissive – Log policy violations only.
  3. Disabled – Completely disabled.

As database servers are usually dedicated to one purpose, such as running MongoDB, the work of setting up SELinux is a lot simpler than a multi-use server with many processes and users (such as an application/web server, etc.). The OS access pattern of a database server should be extremely predictable. Introducing Enforcing” mode at the very beginning of your testing/installation instead of after-the-fact avoids a lot of gotchas with SELinux. Logging for SELinux is directed to /var/log/audit/audit.log” and the configuration is at /etc/selinux”.

Luckily, Percona Server for MongoDB RPM packages (CentOS/RedHat) are SELinux “Enforcing” mode compatible as they install/enable an SELinux policy at RPM install time! Debian/Ubuntu SELinux support is still in planning.

Here you can see the SELinux policy shipped in the Percona Server for MongoDB version 3.2 server package:

$ rpm -ql Percona-Server-MongoDB-32-server | grep selinux /etc/selinux/targeted/modules/active/modules/mongod.pp

To change the SELinux mode to Enforcing”:

$ sudo setenforce Enforcing

To check the running SELinux mode:

$ sudo getenforce Enforcing

Linux Kernel and Glibc Version

The version of the Linux kernel and Glibc itself may be more important than you think. Some community benchmarks show a significant improvement on OLTP throughput benchmarks with the recent Linux 3.x kernels versus the 2.6 still widely deployed. To avoid serious bugs, MongoDB should at minimum use Linux 2.6.36 and Glibc 2.13 or newer.

I hope to create a follow-up post on the specific differences seen under MongoDB workloads on Linux 3.2+ versus 2.6. Until then, I recommend you test the difference using your own workloads and any results/feedback are appreciated.

What’s Next?

What’s the next thing to tune? At this point, tuning becomes case-by-case and open-ended. I appreciate any comments on use-case/tunings pairings that worked for you. Also, look out for follow-ups to this article for a few tunings I excluded due to lack of testing.

Not knowing the next step might mean you’re done tuning, or that you need more visibility into your stack to find the next bottleneck. Good monitoring and data visibility are invaluable for this type of investigation. Look out for future posts regarding monitoring your MongoDB (or MySQL) deployment and consider using Percona Monitoring and Management as an all-in-one monitoring solution. You could also try using Percona-Lab/prometheus_mongodb_exporterprometheus/node_exporter and Percona-Lab/grafana_mongodb_dashboards for monitoring MongoDB/Linux with Prometheus and Grafana.

The road to an efficient database stack requires patience, analysis and iteration. Tomorrow a new hardware architecture or change in kernel behavior could come, be the first to spot the next bottleneck! Happy hunting.

Percona XtraDB Cluster 5.7.12 RC1 is now available

Latest MySQL Performance Blog posts - August 11, 2016 - 4:01pm

Percona announces the first release candidate (RC1) in the Percona XtraDB Cluster 5.7 series on August 9, 2016. Binaries are available from the downloads area or our software repositories.

Percona XtraDB Cluster 5.7.12-5rc1-26.16 is based on the following:

This release includes all changes from upstream releases and the following:

New Features

  • PXC Strict Mode: Use the pxc_strict_mode variable in the configuration file or the –pxc-strict-mode option during mysqld startup.
  • Galera instruments exposed in Performance Schema: This includes mutexes, condition variables, file instances, and threads.

Bug Fixes

  • Fixed error messages.
  • Fixed the failure of SST via mysqldump with gtid_mode=ON.
  • Added check for TOI that ensures node readiness to process DDL+DML before starting the execution.
  • Removed protection against repeated calls of wsrep->pause() on the same node to allow parallel RSU operation.
  • Changed wsrep_row_upd_check_foreign_constraints to ensure that fk-reference-tableis open before marking it open.
  • Fixed error when running SHOW STATUS during group state update.
  • Corrected the return code of sst_flush_tables() function to return a non-negative error code and thus pass assertion.
  • Fixed memory leak and stale pointer due to stats not freeing when toggling the wsrep_providervariable.
  • Fixed failure of ROLLBACK to register wsrep_handler
  • Fixed failure of symmetric encryption during SST.

Other Changes

  • Added support for sending the keyring when performing encrypted SST.
  • Changed the code of THD_PROC_INFO to reflect what the thread is currently doing.
  • Using XtraBackup as the SST method now requires Percona XtraBackup 2.4.4 or later.
  • Improved rollback process to ensure that when a transaction is rolled back, any statements open by the transaction are also rolled back.
  • Removed the sst_special_dirs variable.
  • Disabled switching of slave_preserve_commit_order to ON when running PXC in cluster mode, as it conflicts with existing multi-master commit ordering resolution algorithm in Galera.
  • Changed the default my.cnf configuration.
  • Other low-level fixes and improvements for better stability.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

MySQL showing incorrect version?

Lastest Forum Posts - August 11, 2016 - 10:36am
I recently did a fresh install of MySQL 5.6.23 and overwrote my previous 5.6.17 basedir, datadir, and init files with content from the Percona 5.6.23 directory. However, now that everything has been installed, the logs and mysql client are still somehow identifying itself as MySQL 5.6.17.

MySQL CLI
Code: mysql> SELECT VERSION(); +-----------------+ | VERSION() | +-----------------+ | 5.6.17-66.0-log | +-----------------+ 1 row in set (0.00 sec) Linux CLI
Code: root@localhost-akalaj:/home/akalaj# mysql --version mysql Ver 14.14 Distrib 5.6.23-72.1, for Linux (x86_64) using EditLine wrapper Base Dir Config
Code: root@localhost-akalaj:/home/akalaj# /bin/ls -lah /usr/local | egrep "mysql|Percona" lrwxrwxrwx 1 root staff 54 Aug 11 08:57 mysql -> /usr/local/Percona-Server-5.6.23-rel72.1-Linux.x86_64/ drwxr-xr-x 13 root root 4.0K Aug 11 08:15 Percona-Server-5.6.23-rel72.1-Linux.x86_64 This version conflict becomes a problem when using MySQL utilities as it causes me to run "--skip-version" on many things.

Where is MySQL checking the version from? How can I ensure that MySQL properly identifies the version it's on??

Percona Memory Engine for MongoDB

Latest MySQL Performance Blog posts - August 11, 2016 - 10:19am

This post discusses Percona Server for MongoDB’s new in-memory storage engine, Percona Memory Engine for MongoDB.

Percona Server for MongoDB introduced the Memory Engine starting with the 3.2.8-2.0 version. To use it, run Percona Server for MongoDB with the --storageEngine=inMemory option.

In-memory is a special configuration of WiredTiger that doesn’t store user data on disk. With this engine, data fully resides in the virtual memory of the system (and might get lost on server shutdown).

Despite the fact that the engine is purely in-memory, it writes a small amount of diagnostic data and statistics to disk. The latter can be controlled with the --inMemoryStatisticsLogDelaySecs option. The --dbpath option controls where to store the files. Generally, in-memory cannot run on the database directory previously used by any other engine (including WiredTiger).

The engine uses the desired amount of memory when configured with the --inMemorySizeGB option. This option takes fractional numbers to allow precise memory size specification. When you reach the specified memory limit, aWT_CACHE_FULL error is returned for all kinds of operations that cause user data size to grow. These include inserting new documents, creating indexes, updating documents by adding or extending fields, running aggregation workflow and others. However, you can still perform read queries on a full engine.

Since Percona Memory Engine executes fewer operations and makes no disk I/O system calls, it performs better compared to conventional durable storage engines, including WiredTiger’s standard disk-based configuration.

Performance

The following graphs show Percona Memory Engine versus WiredTiger performance. Both engines use the default configuration with 140GB cache size specified. The hardware is 56-core Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz with 256GB of RAM and RAID1 2xHDD. Test data set is about cache size and fully fits in memory.

You can clearly see that Percona Memory Engine has better throughput and less jitter on all kinds of workloads. Checkpointing, however, can cause jitters in WiredTiger, and are absent in Percona Memory Engine as there’s no need to periodically sync in-memory data structures with their on-disk representations.

However, the performance of Percona Memory Engine drops when it’s about to become full (currently, when it’s 99% full). We’ve marked this issue as fixed (https://jira.mongodb.org/browse/SERVER-24580) but it still crops up in extreme cases.

Percona Memory Engine might use up to 1.5 times more memory above the set configuration when it’s close to full. WiredTiger almost never exceeds the specified cache memory limit. This might change in future versions. But current users should avoid possible swapping or OOM-killing of the server with Percona Memory Engine if (mis)configured to use all or close to all of available system RAM.

You can download the latest version of Percona Server for MongoDB, which includes the new Percona Memory Engine feature, here.

Percona Server for MongoDB 3.2.8-2.0 is now available

Latest MySQL Performance Blog posts - August 11, 2016 - 8:48am

Percona announces the release of Percona Server for MongoDB 3.2.8-2.0 on August 11, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Percona Server for MongoDB 3.2.8-2.0 is an enhanced, open-source, fully compatible, highly scalable, zero-maintenance downtime database supporting the MongoDB v3.2 protocol and drivers. It extends MongoDB with MongoRocks, Percona Memory Engine, and PerconaFT storage engine, as well as enterprise-grade features like external authentication and audit logging at no extra cost. Percona Server for MongoDB requires no changes to MongoDB applications or code.

Note:

We deprecated the PerconaFT storage engine. It will not be available in future releases.

This release is based on MongoDB 3.2.8, and includes the following additional changes:

  • Introducing the new Percona Memory Engine, which is based on a special configuration of WiredTiger that stores data in memory instead of the disk.
  • --auditDestination can now be set to file, syslog, or console.
  • --auditFormat can now be set to JSON or BSON.

    Note

    For more information, see Audit Logging.

  • The MongoRocks engine now supports LZ4 compression. This is an upstream feature of MongoRocks contributed by Percona.To enable it, use the --rocksdbCompression option when running PSMDB with the MongoRocks storage engine. For example:
    ./mongod --dbpath=./data --storageEngine=rocksdb --rocksdbCompression=lz4
    For a high-compression variant of LZ4:
    ./mongod --dbpath=./data --storageEngine=rocksdb --rocksdbCompression=lz4hc

    Note

    If you want to configure this permanently, set the following parameters in the /etc/mongod.conf file:

    storage: engine: rocksdb rocksdb: compression: lz4

The release notes are available in the official documentation.

 

Introducing Percona Memory Engine for MongoDB

Latest MySQL Performance Blog posts - August 11, 2016 - 8:13am

I’m pleased to announce the latest Percona Server for MongoDB feature: Percona Memory Engine for MongoDB.

Everybody understands that memory is much faster than disk – even the fastest solid state storage can’t compete with it. As such the choice for the most demanding workloads, where performance and predictable latency are paramount, is in-memory computing.

MongoDB is no exception. MongoDB can benefit from a storage engine option that stores data in memory. In fact, MongoDB introduced it in the 3.2 release with their In-Memory Storage Engine. Unfortunately, their engine is only available in their closed source MongoDB Enterprise Edition. Users of their open source MongoDB Community Edition were out of luck. Until now.

At Percona we strive to provide the best open source MongoDB variant software with Percona Server for MongoDB. To meet this goal, we spent the last few months working on an open source implementation of an in-memory storage engine: introducing Percona Memory Engine for MongoDB!

Percona Memory Engine for MongoDB provides the same performance gains as the current implementation of MongoDB’s in-memory engine. Both are based on WiredTiger, but optimize it for cases where data fits in memory and does not need to be persistent.

To make migrating from MongoDB Enterprise Edition to Percona Server for MongoDB as simple as possible, we made our command line and configuration options as compatible as possible with the MongoDB In-Memory Storage Engine.

Look for more blog posts showing the performance advantages of Percona Memory Engine for MongoDB compared to conventional disk-based engines, as well as some use cases and best practices for using Percona Memory Engine in your MongoDB deployments. Below is a quick list of advantages that in-memory processing provides:

  • Reduced costs. Storing data in memory means you do not have to have additional costs for high-performance storage, which provides a great advantage for cloud systems (where high-performance storage comes at a premium).
  • Very high performance reads. In-memory processing provides highly predictable latency as all reads come from memory instead of being pulled from a disk.
  • Very high performance writes. In-memory processing removes the need for persisted data on disk, which very useful for cases where data durability is not critical.

From a developer standpoint, Percona Memory Engine addresses several practical use cases:

  • Application cache. Replace services such as memcached and custom application-level data structures with the full power of MongoDB features.
  • Sophisticated data manipulation. Augment performance for data manipulation operations such as aggregation and map reduction.
  • Session management. Decrease application response times by keeping active user sessions in memory.
  • Transient Runtime State. Store application stateful runtime data that doesn’t require on-disk storage.
  • Real-time Analytics. Use in-memory computing in situations where response time is more critical than persistence.
  • Multi-tier object sharing. Facilitate data sharing in multi-tier/multi-language applications.
  • Application Testing. Reduce turnaround time for automated application tests.

I’m including a simple benchmark result for very intensive write workloads that compares Percona Memory Engine and WiredTiger. As you can see, you can get dramatically better performance with Percona Memory Engine!

Download Percona Memory Engine for MongoDB here.

drop collection or database does not free up disk space

Lastest Forum Posts - August 10, 2016 - 11:05pm
We have a large MongoDB database (about 400GB data file each day), MongoDB 3.2.6/3.2.7, engine rocksdb, operating system redhat 6.3.
After dropping the collection or database, the disk space used on the machine was not reclaimed.
After rm some sst files which i think should be dropped by rocksdb,mongodb can still work fine.
By the way,show dbs is not corresponding to du in linux.
Best Regards

Small innodb_page_size as a performance boost for SSD

Latest MySQL Performance Blog posts - August 10, 2016 - 5:13pm

In this blog post, we’ll discuss how a small innodb_page_size can create a performance boost for SSD.

In my previous post Testing Samsung storage in tpcc-mysql benchmark of Percona Server I compared different Samsung devices. Most solid state drives (SSDs) use 4KiB as an internal page size, and the InnoDB default page size is 16KiB. I wondered how using a different innodb_page_size might affect the overall performance.

Fortunately, MySQL 5.7 comes with the option innodb_page_size, so you can set different InnoDB page sizes than the standard 16KiB. This option is still quite inconvenient to use, however. You can’t change innodb_page_size for the existing database. Instead, you need to create a brand new database with a different innodb_page_size and reload whole data set. This is a serious showstopper for production adoption. Specifying innodb_page_size for individual tables or indexes would be a welcome addition, and you could change it with a simple ALTER TABLE foo page_size=4k.

Anyway, this doesn’t stop us from using innodb_page_size=4k in the testing environment. Let’s see how it affects the results using the same conditions described in my previous post.

Again we see that the PM1725 outperforms the SM863 when we have a limited memory, and the result is almost equal when we have plenty of memory.

But what about innodb_page_size 4k vs 16k.?

Here is a direct comparison chart:

Tabular results (in NOTPM, more is better):

Buffer Pool, GiB pm1725_16k pm1725_4k sam850_16k sam850_4k sam863_16k sam863_4k pm1725 4k/16k 5 42427.57 73287.07 1931.54 2682.29 14709.69 48841.04 1.73 15 78991.67 134466.86 2750.85 6587.72 31655.18 93880.36 1.70 25 108077.56 173988.05 5156.72 10817.23 56777.82 133215.30 1.61 35 122582.17 195116.80 8986.15 11922.59 93828.48 164281.55 1.59 45 127828.82 209513.65 12136.51 20316.91 123979.99 192215.27 1.64 55 130724.59 216793.99 19547.81 24476.74 127971.30 212647.97 1.66 65 131901.38 224729.32 27653.94 23989.01 131020.07 220569.86 1.70 75 133184.70 229089.61 38210.94 23457.18 131410.40 223103.07 1.72 85 133058.50 227588.18 39669.90 24400.27 131657.16 227295.54 1.71 95 133553.49 226241.41 39519.18 24327.22 132882.29 223963.99 1.69 105 134021.26 224831.81 39631.03 24273.07 132126.29 222796.25 1.68 115 134037.09 225632.80 39469.34 24073.36 132683.55 221446.90 1.68

 

It’s interesting to see that 4k pages help to improve the performance up to 70%, but only for the PM1725 and SM863. For the low-end Samsung 850 Pro, using a 4k innodb_page_size actually makes things worse when using a high amount of memory.

I think a 70% performance gain is too significant to ignore, even if manipulating innodb_page_size requires extra work. I think it is worthwhile to evaluate if using different innodb_page_size settings help a fast SSD under your workload.

And hopefully MySQL 8.0 makes it easier to use different page sizes!

TokuMX - range locking and Lock not granted

Lastest Forum Posts - August 10, 2016 - 4:15pm

Hello,

In our application we make heavy use of transactions. Recently we started getting Lock not granted messages with multiple concurrent users. Before getting that message tokumx becomes unresponsive and afterwards can leave the data in inconsistent state.We are using Python and MongoEngine.

Below are the relevant log excerpts. I see two potential causes:
1. The bounds for locks are not set per document if the $in operator is used, but on the whole collection subrange. Can this be confirmed? What is the preferred way to update many documents in TokuMX? Would it be better to issue one update command per document in this case?


2. Very often the next operation after lock not granted is insert collection.system.indexes, which apparently requires write lock. Does this message mean that a new document is being added to the index or this means adding a new index? We are not adding any new indexes, but maybe this is related to changing type to multi key indexes?


I would appreciate any help with this.

Thanks

Maricn


Wed Aug 10 18:05:23.586 [conn10421] insert collection.changes keyUpdates:0 exception: Lock not granted. Try restarting the transaction. code:16759 lockNotGranted: { index: "collection.changes.$_cls_1_contract_1_item_parent _1_item_1", requestingTxnid:
3746827, blockingTxnid: 3744908, bounds: [ { _cls: "ContractChange", contract: ObjectId('57ab65cc135ff11f57c6cc0e'), item_parent: ObjectId('57ab65cd135ff11f57c6cc26'), item: ObjectId('57ab65cd135ff11f57c6cc24'), $primaryKey: MinKey }, { _cls: "ContractC
hange", contract: ObjectId('57ab65cc135ff11f57c6cc0e'), item_parent: ObjectId('57ab65cd135ff11f57c6cc26'), item: ObjectId('57ab65cd135ff11f57c6cc24'), $primaryKey: MaxKey } ], blockingOp: { opid: 2444313, active: false, op: "query", ns: "", query: { $msg
: "query not recording (too large)" }, client: "172.31.42.160:36492", desc: "conn10527", threadId: "0x7f86f37fb700", connectionId: 10527, rootTxnid: 3744908, locks: { ^collection: "R" }, context: "query (/mnt/workspace/percona-tokumx-2.0-debian-binary/
label_exp/vps-ubuntu-trusty-x64-04/tokumx-enterprise-2.0.2/src/mongo/db/ops/query.cpp:1003)", waitingForLock: true, lockStats: { timeLockedMicros: {}, timeAcquiringMicros: {} } } } locks(micros) r:10000248 10000ms
Wed Aug 10 18:05:23.586 [conn10438] insert collection.system.indexes ninserted:1 keyUpdates:0 locks(micros) r:75 w:86 3794ms



Wed Aug 10 18:03:20.345 [conn10529] update collection.changes query: { _cls: "ContractChange", contract: ObjectId('57ab6c141ff701555fb4cb8d'), cid: 15 } update: { $set: { occurrence_counter: 1 } } nscanned:0 nupdated:0 keyUpdates:0 lockNotGranted: { in
dex: "collection.changes.$_cls_1_contract_1_item_1" , requestingTxnid: 3732428, blockingTxnid: 3730785, bounds: [ { _cls: "ContractChange", contract: ObjectId('57ab6c141ff701555fb4cb8d'), item: MinKey, $primaryKey: MinKey }, { _cls: "ContractChange", co
ntract: ObjectId('57ab6c141ff701555fb4cb8d'), item: MaxKey, $primaryKey: MaxKey } ], blockingOp: { opid: 2425666, active: true, secs_running: 19, op: "insert", ns: "collection.system.indexes", insert: {}, client: "172.31.29.55:51530", desc: "conn10424"
, threadId: "0x7f87435fd700", connectionId: 10424, rootTxnid: 3730785, locks: { ^: "w", ^collection: "W" }, context: "insert (/mnt/workspace/percona-tokumx-2.0-debian-binary/label_exp/vps-ubuntu-trusty-x64-04/tokumx-enterprise-2.0.2/src/mongo/db/instan
ce.cpp:997)", waitingForLock: false, lockStats: { timeLockedMicros: { r: 107, w: 0 }, timeAcquiringMicros: { r: 2, w: 19217213 } } } } locks(micros) r:20000719 20000ms




Wed Aug 10 18:02:47.491 [conn10424] update collection.changes query: { _cls: "ContractChange", contract: ObjectId('57ab6badaa44d67627da7b80'), cid: 15 } update: { $set: { occurrence_counter: 1 } } nscanned:0 nupdated:0 keyUpdates:0 lockNotGranted: { in
dex: "collection.changes.$_cls_1_contract_1_item_parent _1_item_1", requestingTxnid: 3730785, blockingTxnid: 3731261, bounds: [ { _cls: "ContractChange", contract: ObjectId('57ab6badaa44d67627da7b80'), item_parent: MinKey, item: MinKey, $primaryKey: Min
Key }, { _cls: "ContractChange", contract: ObjectId('57ab6badaa44d67627da7b80'), item_parent: MaxKey, item: MaxKey, $primaryKey: MaxKey } ], blockingOp: { opid: 2420278, active: true, secs_running: 49, op: "insert", ns: "collection.system.indexes", ins
ert: {}, client: "172.31.25.197:51859", desc: "conn10414", threadId: "0x7f86ea2fe700", connectionId: 10414, rootTxnid: 3731261, locks: { ^: "w", ^collection: "W" }, context: "insert (/mnt/workspace/percona-tokumx-2.0-debian-binary/label_exp/vps-ubuntu-
trusty-x64-04/tokumx-enterprise-2.0.2/src/mongo/db/instance.cpp:997)", waitingForLock: false, lockStats: { timeLockedMicros: { r: 68, w: 0 }, timeAcquiringMicros: { r: 1, w: 49172164 } } } } locks(micros) r:50001297 50001ms
Wed Aug 10 18:02:47.491 [conn10414] insert collection.system.indexes ninserted:1 keyUpdates:0 locks(micros) r:68 w:98 49172ms



Wed Aug 10 18:01:36.733 [conn10366] query collection.tags query: { section: { $in: [ ObjectId('57ab56d11ff701555fb4b168'), ObjectId('57ab56d11ff701555fb4b15f'), ObjectId('57ab56d11ff701555fb4b160'), ObjectId('57ab56d11ff701555fb4b161'), ObjectId('57ab5
6d11ff701555fb4b120'), ObjectId('57ab56d11ff701555fb4b121'), ObjectId('57ab56d11ff701555fb4b12f'), ObjectId('57ab56d21ff701555fb4b18c'), ObjectId('57ab56d21ff701555fb4b18b'), ObjectId('57ab56d11ff701555fb4b130'), ObjectId('57ab56cd1ff701555fb4b057'), Obj
ectId('57ab56d11ff701555fb4b131'), ObjectId('57ab56d11ff701555fb4b169'), ObjectId('57ab56d11ff701555fb4b12d'), ObjectId('57ab56ce1ff701555fb4b063'), ObjectId('57ab56d11ff701555fb4b162'), ObjectId('57ab56d11ff701555fb4b124'), ObjectId('57ab56d11ff701555fb
4b163'), ObjectId('57ab56d11ff701555fb4b132'), ObjectId('57ab56d11ff701555fb4b133'), ObjectId('57ab56d21ff701555fb4b16d'), ObjectId('57ab56d21ff701555fb4b172'), ObjectId('57ab56d21ff701555fb4b174'), ObjectId('57ab56d01ff701555fb4b0f9'), ObjectId('57ab56d
11ff701555fb4b139'), ObjectId('57ab56d11ff701555fb4b164'), ObjectId('57ab56d11ff701555fb4b13a'), ObjectId('57ab56d11ff701555fb4b13b'), ObjectId('57ab56d11ff701555fb4b13c'), ObjectId('57ab56d11ff701555fb4b165'), ObjectId('57ab56ce1ff701555fb4b085'), Objec
tId('57ab56ce1ff701555fb4b08d'), ObjectId('57ab56ce1ff701555fb4b08f'), ObjectId('57ab56ce1ff701555fb4b092'), ObjectId('57ab56d11ff701555fb4b127'), ObjectId('57ab56d11ff701555fb4b134'), ObjectId('57ab56d11ff701555fb4b135'), ObjectId('57ab56cf1ff701555fb4b0ed'), ObjectId('5
7ab56cf1ff701555fb4b0c5'), ObjectId('57ab56cf1ff701555fb4b0c6'), ObjectId('57ab56cf1ff701555fb4b0c7'), ObjectId('57ab56d01ff701555fb4b0fc'), ObjectId('57ab56d01ff701555fb4b0fd'), ObjectId('57ab56d01ff701555fb4b0fe'), ObjectId('57ab56d11ff701555fb4b151'), Objec
tId('57ab56d11ff701555fb4b14a'), ObjectId('57ab56d11ff701555fb4b14b'), ObjectId('57ab56d11ff701555fb4b14c'), ObjectId('57ab56cf1ff701555fb4b0eb'), ObjectId('57ab56d11ff701555fb4b14d'), ObjectId('57ab56d11ff701555fb4b14e'), ObjectId('57ab56cf1ff701555fb4b
0ec'), ObjectId('57ab56d11ff701555fb4b14f'), ObjectId('57ab56d11ff701555fb4b150') ] }, _cls: { $in: [ "Tag", "Tag.ValueTag" ] }, contract: ObjectId('57ab56c91ff701555fb4aecd') } ntoreturn:0 keyUpdates:0 exception: Lock not granted. Try restarting the transaction. code:16759 lockNotGranted: { inde
x: "collection.ns", requestingTxnid: 3732456, blockingTxnid: 3730785, bounds: [ { ns: "collection.tags" }, { ns: "collection.tags" } ], blockingOp: { opid: 2419580, active: false, op: "query", ns: "", query: { $msg: "query not recording (too large)
" }, client: "172.31.29.55:51530", desc: "conn10424", threadId: "0x7f87435fd700", connectionId: 10424, rootTxnid: 3730785, locks: { ^collection: "R" }, context: "query (/mnt/workspace/percona-tokumx-2.0-debian-binary/label_exp/vps-ubuntu-trusty-x64-04/
tokumx-enterprise-2.0.2/src/mongo/db/ops/query.cpp:1003)", waitingForLock: true, lockStats: { timeLockedMicros: {}, timeAcquiringMicros: {} } } } locks(micros) r:10000290 reslen:95 10000ms
Wed Aug 10 18:01:36.733 [conn10412] assertion 16759 Lock not granted. Try restarting the transaction. ns:collection.tags query:{ _id: { $in: [ ObjectId('57aabb61aa44d6450d2221a6') ] } }



Current ops view of the indexing op:


{
"opid" : 10674100,
"active" : true,
"secs_running" : 380,
"op" : "insert",
"ns" : "contractroom.system.indexes",
"insert" : {

},
"client" : "172.31.47.18:47304",
"desc" : "conn86929",
"threadId" : "0x7fae17af9700",
"connectionId" : 86929,
"rootTxnid" : 14824514,
"locks" : {
"^contractroom" : "W"
},
"context" : "insert (/mnt/workspace/percona-tokumx-2.0-debian-binary/label_exp/vps-ubuntu-trusty-x64-04/tokumx-enterprise-2.0.2/src/mongo/db/instance.cpp:997)",
"waitingForLock" : true,
"lockStats" : {
"timeLockedMicros" : {
"r" : NumberLong(74),
"w" : NumberLong(0)
},
"timeAcquiringMicros" : {
"r" : NumberLong(1),
"w" : NumberLong(0)
}
}
},



Monitoring RDS Instances

Lastest Forum Posts - August 10, 2016 - 6:13am
Can you please explain as how to install pmm-client on RDS instaces to start monitoring. I am following the documentation here >> https://www.percona.com/doc/percona-...tml#amazon-rds
but am not sure how to install the client and thereby use the pmm-admin tool to add instances for monitoring.

Thanks.

Can't create thread error

Lastest Forum Posts - August 10, 2016 - 6:01am
Hi,

We recently upgraded to Percona Server 5.7 on Ubuntu 16.04 (from 5.6 on 12.04) and started getting the Code: Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug error.

This error is very well reported but all the solutions I find are so far related to sort_buffer_size being too large or ulimit max processes set too low, and I can't find any evidence for either on our server.

Does anyone know of any other reasons for getting this error?

Regards,
Patrik

The, from what I can tell, relevant system information:
Code: $ cat /proc/`pidof mysqld`/limits | egrep "(processes|files)" Max processes 515161 515161 processes Max open files 65536 65536 files $ cat /proc/sys/kernel/threads-max 1030322 $ free -h total used free shared buff/cache available Mem: 125G 86G 18G 62M 21G 38G Swap: 4.7G 2.2G 2.5G mysql> SHOW GLOBAL VARIABLES LIKE 'key_buffer_size'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | key_buffer_size | 536870912 | +-----------------+-----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'read_buffer_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | read_buffer_size | 2097152 | +------------------+---------+ 1 row in set (0.01 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | sort_buffer_size | 262144 | +------------------+--------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> SHOW GLOBAL STATUS LIKE 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 71 | | Threads_connected | 35 | | Threads_created | 95096 | | Threads_running | 2 | +-------------------+-------+ 4 rows in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'open_files'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_files | 696 | +---------------+-------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'max_used_connections'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 474 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL STATUS LIKE 'connections'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | Connections | 288807853 | +---------------+-----------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'open_files_limit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 65536 | +------------------+-------+ 1 row in set (0.00 sec)

tpcc-mysql benchmark tool: less random with multi-schema support

Latest MySQL Performance Blog posts - August 9, 2016 - 3:34pm

In this blog post, I’ll discuss changes I’ve made to the tpcc-mysql benchmark tool. These changes make it less random and support multi-schema.

This post might only be interesting to performance researchers. The tpcc-mysql benchmark to is what I use to test different hardware (as an example, see my previous post: https://www.percona.com/blog/2016/07/26/testing-samsung-storage-in-tpcc-mysql-benchmark-percona-server/).

The first change is support for multiple schemas, rather than just one schema. Supporting only one schema creates too much internal locking in MySQL on the same rows or the same index. Locking is fine if we want to compare different MySQL server versions. But it limits comparing different hardware or Linux kernels. In this case, we want to push MySQL as much as possible to load the underlying components. One solution is to partition several tables, But since MySQL still does not support Foreign Keys over partitioning tables, we would need to remove Foreign Key as well. A better solution is using multiple schemas (which is sort of like artificial partitioning). I’ve implemented this updated in the latest code of tpcc-mysql: https://github.com/Percona-Lab/tpcc-mysql.

The second change I proposed is replacing fully random text fields with generated text, something similar to what is used in the TPC-H benchmark. The problem with fully random strings is that they take a majority of the space in tpcc-mysql schemas, but they are aren’t at all compressible. This makes it is hard to use tpcc-mysql to compare compression methods in InnoDB (as well as different compression algorithms). This implementation is available in a different branch for now: https://github.com/Percona-Lab/tpcc-mysql/tree/less_random.

If you are using tpcc-mysql, please test these changes.

Webinar Thursday 8/11 at 10 am: InnoDB Troubleshooting

Latest MySQL Performance Blog posts - August 9, 2016 - 1:48pm

Join Sveta Smirnova Thursday, August 11 at 10 am PDT (UTC-7) for a webinar on InnoDB Troubleshooting.

InnoDB is one of the most popular database engines. This general-purpose storage engine is widely used, has been MySQL’s default engine since version 5.6, and holds MySQL system tables since 5.7. It is hard to find a MySQL installation that doesn’t have at least one InnoDB table.

InnoDB is not a simple engine. It has its own locks, transactions, log files, monitoring, options and more. It is also under active development. Some of the latest features introduced in 5.6 are read-only transactions and multiple buffer pools (which now can persist on the disk between restarts). In 5.7, InnoDB added spatial indexes and general tablespaces (which can be created to hold table data per user choice). InnoDB development continues forward today.

Its features provide a great deal of power for users, but at the same time make troubleshooting a complex task.

This webinar will try to make InnoDB troubleshooting easier. You will learn specific tools in InnoDB, how and when to use them, how to get useful information from numerous InnoDB metrics and how to decode the engine status.

Register for this webinar here.

Sveta Smirnova, Principal Technical Services Engineer

Sveta joined Percona in 2015. Her main professional interests are problem-solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as Support Engineer in MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of book “MySQL Troubleshooting” and JSON UDF functions for MySQL.

How to validate a backup

Lastest Forum Posts - August 9, 2016 - 5:46am
Dear all,

During a scheduled backup procedure, how can I be sure that a backup is correct?
I used to work with Oracle Databases. With Oracle, we used to check size of files to determine the backup is valid or not. If the size of backup files were smaller than the actual size, we found out that we should run the backup procedure again.
As I know so far, the best solution for this issue is restoring the whole backup and running 'table checks' but restoring and checking tables take a lot of time. Is there any other solution to solve this problem without restoring?

XtraBackup --compact and stream=tar compatibles with each other?

Lastest Forum Posts - August 9, 2016 - 1:07am
Hi everybody,

I'm using innobackupex and I want to perform a backup with --compact and --stream=tar options but when I restore this bak doesn't work... and I don't know why.
It seems as if the secondary indexes didn't create correctly before the retoration though I use the option '--rebuild-indexes'.

Backup command:

Code: innobackupex --user=$db_user --password=$db_pass --compact --stream=tar ./ --no-timestamp | gzip - > $WORKING_DIR/$BCK_FILE Restore command:

Code: tar -zxif $src -C $TMP_DIR/database innobackupex --apply-log --rebuild-indexes $TMP_DIR/database innobackupex --copy-back $TMP_DIR/database The return of the instruction --apply-log is always different from zero...

Anybody knows if this two methods are compatible with each other?
Somebody could give an example with all the steps to be performed this kind of backup?

Thank you so much.

Best regards,

Boadrius

Docker Images for MySQL Group Replication 5.7.14

Latest MySQL Performance Blog posts - August 8, 2016 - 9:06am

In this post, I will point you to Docker images for MySQL Group Replication testing.

There is a new release of MySQL Group Replication plugin for MySQL 5.7.14. It’s a “beta” plugin and it is probably the last (or at lease one of the final pre-release packages) before Group Replication goes GA (during Oracle OpenWorld 2016, in our best guess).

Since it is close to GA, it would be great to get a better understanding of this new technology. Unfortunately, MySQL Group Replication installation process isn’t very user-friendly.

Or, to put it another way, totally un-user-friendly! It consists of a mere “50 easy steps” – by which I think they mean “easy” to mess up.

Matt Lord, in his post http://mysqlhighavailability.com/mysql-group-replication-a-quick-start-guide/, acknowledges: “getting a working MySQL service consisting of 3 Group Replication members is not an easy “point and click” or automated single command style operation.”

I’m not providing a review of MySQL Group Replication 5.7.14 yet – I need to play around with it a lot more. To make this process easier for myself, and hopefully more helpful to you, I’ve prepared Docker images for the testing of MySQL Group Replication.

Docker Images

To start the first node, run:

docker run -d --net=cluster1 --name=node1 perconalab/mysql-group-replication --group_replication_bootstrap_group=ON

To join all following nodes:

docker run -d --net=cluster1 --name=node2 perconalab/mysql-group-replication --group_replication_group_seeds='node1:6606'

Of course, you need to have Docker Network running:

docker network create cluster1

I hope this will make the testing process easier!



General Inquiries

For general inquiries, please send us your question and someone will contact you.