I recently blogged about tracking metadata locks in the latest MySQL, and now I want to discuss how to track these metadata locks in MariaDB.

In MySQL 5.7, there is a table named `metadata_locks` added to the performance_schema (performance_schema must be enabled *and* the metadata_locks instrument must be specifically enabled as well.

In the MariaDB 10.0 implementation (as of 10.0.7), there is a table named METADATA_LOCK_INFO added to the *information_schema*. This is a new plugin, so the plugin must be installed, but that is very simple with:

INSTALL SONAME 'metadata_lock_info';

Then, you will have the table.

To see it in action:

Connection #1:

mysql> create table t (id int) engine=myisam;
mysql> begin;
mysql> select * from t;

Connection #2:

mysql> alter table t add index …

William Zola over at MongoDB gave a great talk called “The (Only) Three Reasons for Slow MongoDB Performance”. It reminded me of an interesting characteristic of updates in MongoDB. Because MongoDB’s main data store is a flat file and secondary indexes store offsets into the flat file (as I explain here), if the location of a document changes, corresponding entries in secondary indexes must also change. So, an update to an unindexed field that causes the document to move also causes modifications to every secondary index, which, as William points out, can be expensive. If a document has indexed an array, this problem is exacerbated.

What …

Here in Percona Support, we’re receiving several requests per day for help with Percona Cloud Tools installation steps.

So I decided to prepare a step-by-step example of the installation process with some comments based on experience.  Percona Cloud Tools is a hosted service providing access to query performance insights for all MySQL uses. After a brief setup, you’ll unlock new information about your database and how to improve your applications. You can sign up here to request access to the free beta, currently under way.

Some notes

• It’s recommended to do the installation under root.
• If you’re installing pt-agent as root then .pt-agent.conf should be placed in root $HOME
• You could became root …

I came across a scenario where we were concerned about the size of a table, a 134G table, its a pretty huge table and growing fast. The nature of the table – its a table which contains logs , sort of a audit table. Some fundamental questions around this table’s use: 1) would it have a lot of reads ?  — Answer –  No 2) is it always insert only? — Answer – Yes 3) current strategy to restrict size ? — Answer – delete data from table. 4) does one need backup of the table, i.e. is it very important ?  — Answer – No Well based on the above, it would seem that logging is the theme. Schema of the table: CREATE TABLE `SystemLogs` (   `column1` varchar(255) DEFAULT NULL,   `column2` varchar(255) DEFAULT NULL,   `column3` varchar(255) DEFAULT NULL,   `column4` varchar(255) DEFAULT NULL,   `column5` text,   `column6` text,   `timestamp` datetime DEFAULT NULL,   `id` int(11) …

This is the first in a series of posts highlighting a few of the seldom-used but still handy Percona Toolkit tools.

Have you ever had a problem understanding the EXPLAIN statement output? And are you the type of person who would rather use the command line than a GUI application? Then I would recommend that you use Percona’s pt-visual-explain toolkit. This is one of many Percona Toolkit tools that is useful for those who want to have a different view and an easier time understanding the EXPLAIN output aside from the usual table and vertical views.

As described in the documentation – http://www.percona.com/doc/percona-toolkit/2.2/pt-visual-explain.html#description

“pt-visual-explain reverse-engineers …

Whether you are a database administrator, database application developer, web developer, system administrator or system architect, if you are working with a database system such as the MySQL Server, you will want to ensure the best possible performance. 

To learn about the great performance you can get from a MySQL Database, visit Dimitri's blog.

If you have experience maintaining a database server, can use MySQL tools and have knowledge of general SQL statements and SQL tuning principles, consider taking the MySQL Performance Tuning course. In this 4-day instructor-led course, you will learn about:

• What to tune and why
• Monitoring, benchmarking and stress tools …

You can watch the video of my linux.conf.au 2014 talk here: http://mirror.linux.org.au/linux.conf.au/2014/Wednesday/28-Past_Present_and_future_of_MySQL_and_variants_-_Stewart_Smith.mp4

But let’s talk about things in blog form rather than video form :)

Back in 1979, there was UNIREG. A text UI to records (rows) in a database (err, table). The reason I mention UNIREG is that it had FoRMs which as you may have guessed by my capitalization there is where the FRM file comes from.

In 1986, UNIREG came to UNIX. That’s right kids, the 80×24 VT100 interface to ISAM (Index Sequential Access Method – basically rows are written in insert order and indexes point to them) came to UNIX. There was no generic query language, just FoRMs and reports. In fact, to this day, that …

Like a lot of developers I started using a MacBook Pro around the time of Tiger.  I instantly loved it:  simple, fast, and virtually no system administration overhead. The genius of OS X was that it never got in the way. You opened the box, pulled out the machine, and got to work. It had a great user interface, excellent  development tools (Eclipse in my case) and the command utilities like ssh, rsync, and bash worked seamlessly with Linux systems.

Well, that was then and this is now. Starting with Lion I began to spend an increasing amount of time fighting OS X instead of getting work done. I'm now using Mavericks and have not seen much improvement, in fact quite the contrary. Here are just a few of the problems after the Lion to Mavericks upgrade:

• Spotlight indexes destroyed; need 2 days to regenerate
• AppleMail access to Gmail IMAP  broken
• Time Machine stuck in …

February 3, 2014 By Severalnines

Liferay is an open-source content management system written in Java. It is used by a number of high traffic sites, as this survey suggests. 

Clustering Liferay and other components such as the database and the file system is a good way to handle the performance requirements of a high traffic site. The latest Liferay version has introduced features that simplify clustering, such as built-in support for Ehcache clustering, Lucene replication, read/write splitting capabilities for database (in case if you run on master-slave architecture) and support for various file systems for the portal repository. 

In this post, we are going to show you how to cluster Liferay in a multi-node load-balanced setup. The database backend will be based on Galera Cluster for MySQL, and the file …

If you’re running MySQL 5.7, you might have noticed the ibtmp1 file located in the datadir, and you might be wondering exactly what this file is.

In 5.7, InnoDB added a separate tablespace for all non-compressed InnoDB temporary tables. This new tablespace is named ibtmp1 and is located in the datadir by default.

“The new tablespace is always recreated on server startup. … A newly added configuration file option, innodb_temp_data_file_path, allows for a user-defined temporary data file path. For related information, see InnoDB Temporary Table Undo Logs.”

You can read that, and the full changelog entry, here:

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-1.html

And the full variable description is here:

…