Jeremy Cole has an excellent article on why a mysqld instance will swap despite proper memory settings on modern hardware here. A quick hand-wave of a summary goes: in modern architecture, every process is, by default, limited to a 1/Nth of the available memory on a machine, where N is the number of cores, and will start to swap on disk when all the available memory in that 1/Nth is consumed.

The eventual solution for a large, single-instance mysqld-running machine is to enable numa_interleave in the mysqld_safe section of the config:

Implicitly, when that flag is set, mysqld is being started by mysqld_safe with a numactl flag that will spread the memory …

You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.

Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.

There are two main ways to kill queries in MySQL: 1. use custom scripts that match on a regular expression, or 2. use a tool written and supported by Percona that is designed to kill queries based on matching conditions. Below is one script that will help you to kill those queries. The script will take the process list from MySQL and …

November 19, 2014 By Severalnines

Following the release of ClusterControl 1.2.8 with a range of cool new features, we have now also published a new ClusterControl User Guide that provides all the steps, tips & tricks to follow in order to successfully deploy, monitor, manage and scale database clusters.  

This user guide predominantly covers ClusterControl with MySQL-based clusters, namely:

• Percona XtraDB Cluster
• MariaDB Cluster
• Galera Cluster for MySQL (Codership)
• MySQL Cluster
• MySQL Replication
• A pool of MySQL single instances

New features in ClusterControl 1.2.8 include: 

• Deployment and scaling of …

Optimizing MySQL performance requires the ability to inspect production query traffic. If you’re not seeing your application’s production workload, you’re missing a vital part of the picture. In particular, there are lots of performance optimizations your systems might be doing that you’re not aware of. One of these is using prepared statements for queries.

What Are Prepared Statements?

A prepared statement is a SQL statement with parameter placeholders which is sent to the database server and prepared for repeated execution. It’s a performance optimization as well as a security measure; it protects against attacks such as SQL injection, where an attacker hijacks unguarded string concatenation to produce malicious queries.

In MySQL, as well as in most databases, you first send the SQL to the server and ask for it to be prepared with placeholders for bind parameters. The server responds with a statement ID. You …

Hi Apache Friends!

We just released new versions of XAMPP for all platforms with latest PHP versions: 5.5.19 and 5.6.3. We simplified the versioning for XAMPP releases and we now base the version number on the bundled PHP version, so you can more easily tell what is in the stack.

In addition to the component upgrades below, we also included a new beta Welcome page, which includes new tutorials. We will continue to add more and would love to get your feedback!

The following guides are available for Windows and Linux in the current versions:

• Backup and Restore MySQL Databases
• Configure FTP Access
• Configure Virtual Hosts
• Configure Wildcard-Based Subdomains
• Deploy an Application Using Git
• Get Started Quickly with WordPress
• Reset the MySQL Root Password
• Send Mail with PHP
• Troubleshoot Apache …

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.

Truths
* Direct MySQL ALTER table locks for duration of change (pre-5.6)
* Online DDL in MySQL 5.6 is not always online and may incurr locks
* Even with Percona Toolkit‘s pt-online-schema-change there are several workloads that can experience blocking

Here on the Percona MySQL …

Starting with MySQL 5.6 there is an INNODB_METRICS table available in INFORMATION_SCHEMA which contains some additional information than provided in the SHOW GLOBAL STATUS output – yet might be more lightweight than PERFORMANCE_SCHEMA.

Too bad INNODB_METRICS was designed during the Oracle-Sun split under MySQL leadership and so it covers only InnoDB counters. I think this would be a great replacement to all counters that are currently provided though SHOW STATUS – it captures more information such as providing MIN/MAX counts for variables as well as providing the type of the counter (whenever it is current or commutative) as well as human readable comment – describing what such counter means.

The examples of data you can get only from the INNODB_METRICS table includes information about InnoDB Page Splits and merging (which can …

Recently I had a need to determine the session value of a MySQL plugin variable in a core file.  Here I use the word plugin variable to refer to MySQL system variables provided by plugins.  While this is not a very difficult task, it is not as straight forward as printing a global variable. It took some time to figure out the layout of the plugin variables and get the needed information. This short article is to share this gdb tip with other interested MySQL developers and support engineers.

In this article, let us inspect the session value of the plugin variable innodb_strict_mode, which is of type boolean. Quite obviously, this plugin variable is provided by InnoDB storage engine.

The Quick Answer

…

Multi-threaded replication is a new feature introduced in MySQL 5.6 and MariaDB 10.0. In traditional single-threaded replication, the slaves have a disadvantage as they have to process in sequence what a master executed in parallel. This, plus the fact that slaves usually have a lot of read-only connections to deal with as well, can easily create performance problems. That is, a single-threaded slave needs to be set to allow fewer connections, otherwise there’s a higher risk of it not being able to keep up with the replication stream. There is no exact rule for this, as it relates to general I/O capacity and fsync latency, as well as general CPU and RAM considerations and query …

Percona is pleased to announce the availability of Percona Toolkit 2.2.12.  Released on November 14, Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the Percona Software …