Using the MySQL 5.6 Performance Schema it is very easy to see what is actually running on your MySQL instance. No more sampling or installing software or worrying about disk I/O performance with techniques like SHOW PROCESSLIST, enabling the general query log or sniffing the TCP/IP stack.

The following SQL is used to give me a quick 60 second view on a running MySQL system of ALL statements executed.

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from events_statements_summary_by_digest order by 2 desc;
update setup_consumers set …

Users are complaining about slowness in your system, MySQL load is always high… The more your database has access, the more it may get slow or worse: slowness even if it is running with low load. You are starting to get desperate! The consequences of slowness and high load are disastrous: If your site is slow,... Read More

The post MySQL Performance and Tuning Best Practices appeared first on Devops for Dummies.

Today before Ivan’s tutorial, he told me that in the 10.0.5 virtual machine images he created, he couldn’t find the Cassandra storage engine. I told him it had to be installed separately, and this is true – you have to install some engines separately!

When you do a yum install MariaDB-server MariaDB-client like the installation instructions tell you to do, you don’t get all storage engines (so running SHOW ENGINES might have you wondering what happened to a bunch of engines). This can easily be seen by doing a yum search MariaDB. On a CentOS 6.4 server with the MariaDB 10.0 repository configured, you should see the following:

MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL …

Join Web and Telecom companies who have adopted MySQL Cluster to facilitate application in the following areas:

Web:

• High volume OLTP
• eCommerce
• User profile management
• Session management and caching
• Content management
• On-line gaming

Telecoms:

• Subscriber databases (HLR/HSS)
• Service deliver platforms
• VAS: VoIP, IPTV and VoD
• Mobile content delivery
• Mobile payments
• LTE access

To come up to speed on MySQL Cluster, take the 3-day MySQL Cluster training course. Events already on the schedule include:

8PM. One of the servers found a critical bug. Hop online and discuss, log bug. 10PM. Patch ready. 10:30PM. New build ready. 10:45PM. New RQG run initiated. This was by no means an uncommon sight during the months of testing that went into Percona Server 5.6, in fact it was commonplace.

At a certain point, we had 3 very high end servers (modern cpu’s, heaps of cores and memory), all equipped with either fast SSD’s or Fusion-io flash storage, executing thousands of trials, 8 in parallel per server, each executing 1 to 25 mysql threads per running mysqld instance.

And that was just the final months of testing. Before that much work was done on finding “every last bug out there”. We discovered many bugs in both upstream (Oracle’s MySQL 5.6) and in Percona Server 5.6. I personally logged around 100 bugs, but the total count would be much higher still.

My colleague …

How do we ensure that, when we have 35+ testable option combinations for mysqld, we test each and every combination of them? For example: will a different innodb_log_file_size combined with more innodb_log_files_in_group and a modified innodb_fast_shutdown setting truly not affect Percona’s log archiving feature?

Most option-related bugs are caused by the setting of 1 or 2 mysqld options to a non-standard value. Maybe in an odd situation 3 mysqld options need to be set in combination. So, starting with 2 option combinations (1 option set is easy to calculate: it matches the number of options to be tested), let’s see how many combinations we would have to run: 35^2 = 1225 combinations. aa, ab, ac, …. ba, bb, bc… etc.

In real life mysqld testing, this is not entirely true as one would never specify “aa” in relation to …

This post builds on the How to obtain the “LES” (Last Executed Statement) from an Optimized Core Dump? post written about a year ago.

A day after that post was released, Shane Bester wrote an improved version, How to obtain all executing queries from a core file on his blog. Reading that post is key to understanding what follows.

I am faced with some complex bugs which would do well with SQL testcases. Extracting the last executed statement (and maybe all queries running at the time of the crash/asserts) is crucial to generate testcases well. E.g. you may have a full SQL trace from RQG or …

There is a new feature in the MariaDB 10 Beta which caught my eye: support for returning a result set on delete.

With a 'regular' DELETE operation you only get to know the number of affected rows. To get more info or actions you have to use a trigger or a foreign key. Anoter posibility is doing a SELECT and then a DELETE and with the correct transaction isolation a transactional support this will work.

With the support for the RETURNING keyword this has become easier to do and it will probably bennefit performance and save you a few roundtrips and a few lines of code.

There is already support for RETURNING in PostgreSQL. And PostgreSQL has an other nifty feature for which RETURNING really helps: CTE or common table expressions or the WITH keyword. I really hope to see CTE support in MySQL or MariaDB some day.

…

Attending Percona Live in London next week?

Don’t miss the chance to hear Tokutek’s Vice President of Engineering, Tim Callaghan, discuss how to use your MySQL knowledge to become an instant MongoDB Guru and the advantages of using Fractal Tree® indexes in MySQL and MongoDB. Tim will be speaking about these topics in two separate sessions at 12:00pm and 5:00pm on November 12.

For more information on these sessions and Percona Live-London, visit https://www.percona.com/live/london-2013/users/tim-callaghan.