Today at the #ukoug_tech14 I had the chance to attend to the Christo Kutrovky's session @kutrovsky (Pythian) about "Measuring Performance in Oracle Solaris & Oracle Linux". This session aimed to present how to measure system utilization in the Linux and Oracle Solaris operating systems and how to use these information for tuning and capacity planning. During this session we had a very good introduction to a bunch of performance monitoring tools that can be categorized in four categories (non exhaustive list of tool):

1. CPU:

• top
• vmstat
• time
• mpstat

2. Memory - RAM

2.1 Global

• /proc/meminfo
• vmstat
• ipcs

Disclaimer: Do this at your own risk! It doesn’t apply if you’re using Pluggable authentication and certainly won’t be usable if/when MySQL system tables are stored on InnoDB

Recover your root password with care!

What is the situation?

The situation is the classic “need to recover MySQL root password” but you cannot restart MySQL (because it is the master production server, or any other reason), which makes the –skip-grant-tables solution as a no-no possibility.

 What can I do?

There is a workaround, which is the following:

•  Launch another instance of mysqld, a small one (without innodb).
•  Copy your user.[frm|MYD|MYI] files from the original datadir to the datadir of the new instance.
• Modify them and then copy …

Oracle DBAs have has the luxury of their V$ variables for a long time while we MySQL DBAs pretended we were not envious. With MySQL 5.6 and 5.7 we were gifted with the PERFORMANCE_SCHEMA tables. But there is such a wealth of information in those tables that it is intimidating to plunge in to them.  Thankfully Mark Leith has given us the SYS Schema. The SYS Schema is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

The first step is to get a copy of the SYS SCHEMA files.
git clone https://github.com/MarkLeith/mysql-sys


Next install the SYS Schema (here for MySQL 5.7)
mysql -u root -p < ./sys_57.sql

Now run MySQL and look at this …

I had an enjoyable time last night at Twitter with local MySQL DBAs and developers. We had an attendee who has no experience with SQL or programming at all. She is interested in organizing her collection of recipes and had heard a rumor that MySQL was a good tool to use for this task. She indicated that her desktop runs Windows 7. I think I’m going to encourage her to turn her concept in to a community project, as she is not the first person I’ve met who wants to organize recipes!

We were hosted by Rob at Twitter, who used to work with Lisa back before she retired. He’s a member of the site reliability team and keeps the fail whale from rearing its blubbery head.

Pizza was provided by my dear friend and long-time open source buddy Gerry Narvaja with the assistance of the folks in the kitchen at Zeek’s.

We discussed new techniques in the areas of load …

Taxonomy upgrade extras: galerareplicationDDLTOIRSU

We have recently seen a case where the following command was executed on a Galera Cluster node:

SQL> GRANT SUPER ON userdb.* TO root@127.0.0.111;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

2014-12-09 14:53:55 7457 [Warning] Did not write failed 'GRANT SUPER ON userdb.* TO root@127.0.0.111' into binary log while granting/revoking privileges in databases.
2014-12-09 14:53:55 7457 [ERROR] Slave SQL: Error 'Incorrect usage of DB GRANT and GLOBAL PRIVILEGES' on query. Default database: ''. Query: 'GRANT …

In MySQL 5.6 Oracle introduced a Transportable Tablespace feature (copying tablespaces to another server) and Percona Server adopted it for partial backups which means you can now take individual database or table backups and your destination server can be a vanilla MySQL server. Moreover, since Percona Server 5.6, innodb_import_table_from_xtrabackup is obsolete as Percona Server also implemented Oracle MySQL’s transportable tablespaces feature which as I mentioned gives you the ability to copy tablespace (table.ibd) between servers. Let me demonstrate this through one example where I am going to take partial backup of selective tables instead of an entire MySQL server and restore it on a running MySQL …

Introducing MQ system based on MySQL (canal + roma)
Using this system to subscribe message between different platforms.

View this PDF roma_system
Also download this PDF from slideshare

Have you ever experienced a situation where one moment you can connect to the MySQL database and the next moment  you cannot, only to be able to connect again a second later? As you may know one cannot open infinite connections with MySQL. There’s a practical limit and more often than not it is imposed by the underlying operating system. If you’re getting:

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.10' (99)

…there’s a good chance you’re hitting such limit. What might be misleading in the information above is whom (which side) is preventing the connection from being established.

Understanding the problem at hand

Whenever a client uses the network to connect to a service running on a given port of a server this connection is established through the creation of a socket:

…

Our recent blog post on finding wasted prepare/execute/close cycles showed how per-statement analysis can find individual queries that are not being repeatedly executed after being prepared. To recap, we found a number of statements in our own application that were being prepared, executed, and then never re-executed. This means 3 network round-trips instead of just executing the statement directly, which takes only 1 round-trip. By fixing this, we materially decreased load on our servers and improved latency for the service that issued the query.

But Can’t You Already Do This?

Can’t you just do this with the SHOW STATUS counters? What if you do…

show global status like 'Com_stmt_%';

And compare the numbers of prepared, executed and …

This should sound familiar:

One of the great lies about NoSQL databases is that they’re simple. Simplicity done wrong makes things a lot harder and more complicated to develop and operate. Programmers and operations staff end up reimplementing (badly) things the database should do.

Nobody argued this line of reasoning more vigorously than when trying to defend relational databases, especially during the darkest years (ca. 2009-2010), when NoSQL still meant NO SQL DAMMIT, all sorts of NoSQL databases were sprouting, and most of them were massively overhyped. But as valid as those arguments against NoSQL’s “false economy” simplicity were and are, the arguments against relational databases’ complexity hold true, too.

The truth is that no database is really simple. Databases have a lot of functionality and behaviors—even the “simple” databases do—and require deep knowledge to use well when …