The latest from the MySQL community

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 …

The Query Analyzer (QUAN) is one of the flagship features of the MySQL Enterprise Monitor (MEM). I wanted to take a few minutes to walkthrough a complete simulated example in order to demonstrate just how incredibly useful it is for becoming aware of problems, identifying the cause, and in finally fixing the issue.

First, I created a simple MEM test environment:
[root@mylab matt]# cat createsimpletestenv.sh  #!/bin/sh
echo -n "Stopping any default mysqld instance..." /etc/init.d/mysqld stop  echo " done."
echo -n "Setting up fresh mysqld setup..." cp -R /var/lib/mysqlfresh /var/lib/mysql chown -R mysql:mysql /var/lib/mysql* echo " done."
echo -n "Starting fresh default mysqld instance..." /etc/init.d/mysqld start echo " …

MongoDB has always been about ease of use. With nothing more than the mongod binary, starting a MongoDB server is as simple as:
./mongod --dbpath=/path/to/dataAs a long time user of Oracle and MySQL I'm extremely impressed by just how simple this is. It certainly encourages new users to try it out.

In MongoDB 2.6 and earlier there has only been a single "storage engine" available in the server. That storage engine has very few tunable parameters, so the defaults are fine for most users. If you don't like the defaults you can probably change them with a little review of the documentation.

MongoDB 2.8 adds the ability to support an unlimited number of storage engines via a storage engine API. Using the alternative WiredTiger storage engine is as simple as asking for it on the command line:
./mongod --dbpath=/path/to/data --storageEngine …

Even if a Galera node looks like a regular MySQL server, the underlying replication mechanism is very different. This implies some changes in the way you have to configure the Galera nodes. Here are some of the most common misconceptions about Galera when using Percona XtraDB Cluster.

Q: Why should I enable binary logging as it is not needed by Galera replication?
Unlike for regular asynchronous MySQL replication, it is true that you don’t need to enable binary logging to use Galera replication. However what if someone runs an accidental DROP TABLE?

In this case, the statement will be replicated immediately on all nodes. Then your main option to recover lost data is to use a backup. But if binary logging is not …