-
Webinar: SQL Query Patterns, Optimized
Next Friday, May 31 at 10 a.m. Pacific, I’ll present Percona’s next webinar, “SQL Query Patterns, Optimized.”Based on my experiences solving tough SQL problems for Percona training and consulting, I’ll classify several common types of queries with which developers struggle. I’ll test several SQL solutions for each type of query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query efficiency. The discussion will cover optimizer reports, query profiling, and session status to measure performance.The query patterns will include:Exclusion JoinRandom SelectionGreatest-Per-GroupDynamic PivotRelational DivisionPlease register for this webinar and join me next Friday!The post Webinar: SQL Query Patterns, Optimized appeared first on MySQL Performance Blog.
-
New Article on Performance Schema on Dr.Dobbs
Interested in an introduction to using Performance Schema to profile statement activity on your MySQL instances? Head on over to Detailed Profiling of SQL Activity in MySQL 5.6, which was recently published on the Dr.Dobbs site!
-
Easier Overview of Current Performance Schema Setting
While I prepared for my Hands-On Lab about the Performance Schema at MySQL Connect last year, one of the things that occurred to me was how difficult it was quickly getting an overview of which consumers, instruments, actors, etc. are actually enabled. For the consumers things are made more complicated as the effective setting also depends on parents in the hierarchy. So my thought was: “How difficult can it be to write a stored procedure that outputs a tree of the hierarchies.” Well, simple enough in principle, but trying to be general ended up making it into a lengthy project and as it was a hobby project, it often ended up being put aside for more urgent tasks.
However here around eight months later, it is starting to shape up. While there definitely still is work to be done, e.g. creating the full tree and outputting it in text mode (more on modes later) takes around one minute on my test system – granted I am using a standard laptop and MySQL is running in a VM, so it is nothing sophisticated.
The current routines can be found in ps_tools.sql.gz – it may later be merged into Mark Leith’s ps_helper to try to keep the Performance Schema tools collected in one place.
Note: This far the routines have only been tested in Linux on MySQL 5.6.11. Particularly line endings may give problems on Windows and Mac.
Description of the ps_tools Routines
The current status are two views, four stored procedure, and four functions – not including several functions and procedures that does all the hard work:
Views:
setup_consumers – Displays whether each consumer is enabled and whether the consumer actually will be collected based on the hierarchy rules described in Pre-Filtering by Consumer in the Reference Manual.
accounts_enabled – Displays whether each account defined in the mysql.user table has instrumentation enabled based on the rows in performance_schema.setup_actors.
Procedures:
setup_tree_consumers(format, color) – Create a tree based on setup_consumers displaying whether each consumer is effectively enabled. The arguments are:
format is the output format and can be either (see also below).:
Text: Left-Right
Text: Top-Bottom
Dot: Left-Right
Dot: Top-Bottom
color is whether to add bash color escapes sequences around the consumer names when used a Text format (ignored for Dot outputs).
setup_tree_instruments(format, color, only_enabled, regex_filter) – Create a tree based on setup_instruments displaying whether each instrument is enabled. The tree is creating by splitting the instrument names at each /. The arguments are:
format is the output format and can be either:
Text: Left-Right
Dot: Left-Right
Dot: Top-Bottom
color is whether to add bash color escapes sequences around the instrument names when used a Text format (ignored for Dot outputs).
type – whether to base the tree on the ENABLED or TIMED column of setup_instruments.
only_enabled – if TRUE only the enabled instruments are included.
regex_filter – if set to a non-empty string only instruments that match the regex will be included.
setup_tree_actors_by_host(format, color) – Create a tree of each account defined in mysql.user and whether they are enabled; grouped by host. The arguments are:
format is the output format and can be either:
Text: Left-Right
Dot: Left-Right
Dot: Top-Bottom
color is whether to add bash color escape sequences around the names when used a Text format (ignored for Dot outputs).
setup_tree_actors_by_user – Create a tree of each account defined in mysql.user and whether they are enabled; grouped by username. The arguments are:
format is the output format and can be either:
Text: Left-Right
Dot: Left-Right
Dot: Top-Bottom
color is whether to add bash color escape sequences around the names when used a Text format (ignored for Dot outputs).
Functions:
is_consumer_enabled(consumer_name) – Returns whether a given consumer is effectively enabled.
is_account_enabled(host, user) – Returns whether a given account (host, user) is enabled according to setup_actors.
substr_count(haystack, needle, offset, length) – The number of times a given substring occurs in a string. A port of the PHP function of the same name.
substr_by_delim(set, delimiter, pos) – Returns the Nth element from a delimiter string.
The two functions substr_count() and substr_by_delim() was also described in an earlier blog.
The formats for the four stored procedures consists of two parts: whether it is Text or Dot and the direction. Text is a tree that can be viewed directly either in the mysql command line client (coloured output not supported) or the shell (colours supported for bash). Dot will output a DOT graph file in the same way as dump_thread_stack() in ps_helper. The direction is as defined in the DOT language, so e.g. Left-Right will have the first level furthest to the left, then add each new level to the right of the parent level.
Examples
As the source code – including comments – is more than 1600 lines, I will not discuss it here, but rather go through some examples.
setup_tree_consumers
Using the coloured output:
or the same using a non-coloured output:
setup_tree_instruments
Here a small part of the tree is selected using a regex.
setup_tree_actors_%
With only root@localhost and root@127.0.0.1 enabled, the outputs of setup_tree_actors_by_host and setup_tree_actors_by_user gives respectively:
DOT Graph of setup_instruments
The full tree of setup_instruments can be created using the following sequence of steps (I am using graphviz to get support for dot files):MySQL 5.6.11$ echo -e "$(mysql -NBe "CALL ps_tools.setup_tree_instruments('Dot: Left-Right', FALSE, 'Enabled', FALSE, '')")" > /tmp/setup_instruments.dot
MySQL 5.6.11$ dot -Tpng /tmp/setup_instruments.dot -o /tmp/setup_instruments.pngThe full output is rather large (6.7M). If you want to see if you can get to it at http://mysql.wisborg.dk/wp-content/uploads/2013/05/setup_tree_instruments_dot_lr.png.
Views
mysql> SELECT * FROM ps_tools.setup_consumers;
+--------------------------------+---------+----------+
| NAME | ENABLED | COLLECTS |
+--------------------------------+---------+----------+
| events_stages_current | NO | NO |
| events_stages_history | NO | NO |
| events_stages_history_long | NO | NO |
| events_statements_current | YES | YES |
| events_statements_history | NO | NO |
| events_statements_history_long | NO | NO |
| events_waits_current | NO | NO |
| events_waits_history | NO | NO |
| events_waits_history_long | NO | NO |
| global_instrumentation | YES | YES |
| thread_instrumentation | YES | YES |
| statements_digest | YES | YES |
+--------------------------------+---------+----------+
12 rows in set (0.00 sec)
mysql> SELECT * FROM ps_tools.accounts_enabled;
+-------------+-----------+---------+
| User | Host | Enabled |
+-------------+-----------+---------+
| replication | 127.0.0.1 | NO |
| root | 127.0.0.1 | YES |
| root | ::1 | NO |
| meb | localhost | NO |
| memagent | localhost | NO |
| root | localhost | YES |
+-------------+-----------+---------+
6 rows in set (0.00 sec)
Conclusion
There is definitely more work to do on making the Performance Schema easier to access. ps_helper and ps_tools are a great start to what I am sure will be an extensive library of useful diagnostic queries and tools.
-
Experimenting with MySQL 5.7
I was playing around with MySQL 5.7 this weekend and before having read the changelog, I managed to spot these two little gems.
Duplicate Indexes
“The server now issues a warning if an index is created that duplicates an existing index, or an error in strict SQL mode.” Bug #37520
Example Testcase:
mysql> SHOW CREATE TABLE city\G
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> ALTER TABLE city add index (countrycode);
ERROR 1831 (HY000): Duplicate index 'CountryCode_2' defined on the table 'world.city'.
This is deprecated and will be disallowed in a future release.
Pretty cool - I know this previously caught a lot of people.
Control-C support in the client
“Previously, Control+C in mysql interrupted the current statement if there was one, or exited mysql if not. Now Control+C interrupts the current statement if there was one, or cancels any partial input line otherwise, but does not exit.” Bug #66583
Example Testcase:
mysql> this is a test
-> test
-> test
-> ^C
So if I want to quit, I can now control-C then type “quit”. This is much more intuitive.
-
What's the deal with NoSQL?
Everybody seems to be looking at and debating NoSQL these days, and so am I and I thought I'd say a few words about it. Which is not to say I haven't said stuff before, bit them I was mainly targeting specific attributes of many NoSQL solutions (like "eventual consistency" or, as you might call it, "instant inconsistency", What I was opposing is that "eventual consistency" has anything to do with just that, consistency. Rather, what this means is that at any point in time the system is inconsistent, and even if it might be consistent, you cannot rely on it being so. Which is fine, but don't call it consistency, call it inconsistency. Allowing a database to be somewhat inconsistent doesn't necessarily mean that it's something wrong with it).All this said, what is going on here, why are we MySQL and MariaDB users seeing so many MongoDB, Cassandra and LevelDB applications pop up? Come on, these are typically less functional implementations of a database than even the most basic MySQL setup? No transactions, no joins, no standards etc. etc. And the answer, if you want to hear what I have to say, is ease of use. So let's explore that a bit.Following the Object Orientation frenzy of the 1990s, when any application project ended up consisting of endless sessions modeling objects, usually involving expensive consultants, dresses in expensive, blue suits. And when that was done (which took years!) you had a way cool object model, but no money left to do the actual implementation, i.e. do the real programming (shiver), and you went to some other project and the nicely dressed object design consultant left to see another OO sucker.Now, objects are much more standard, even non-OO languages have a big chunk of OO features, and these are used enhance programmer productivity and better code and design. Which is fine (except that if you were one of those OO consultants, which means you are now out of a job, as such mundane tasks of writing is not something you would ever do, such dirty stuff is better left to "programmers". Oh no, I forgot that you are now an ITIL consultant, that just slipped my mind) but how does this relate to MySQL and MariaDB. The answer is that MySQL, which was once considered real easy to use, no longer is as easy as it used to be. The Relational data model is still brilliant when you look at data as data, and that is how many of us look at it, so we go through the process of mapping data to objects, if that is what it takes. SQL and Java, PHP or whatever merges, and the application now contains a layer mapping objects to real data. Or we use hibernate, which does this automatically for us.But a new cadre of developers are emerging, and they look at OO as natural and they look at objects as data (it's not. Data, in my mind, should be independent from the application using it, objects on the other hand, are closely tied to the application at hand). With which I do not mean that there is something wrong with building applications using objects, quite the opposite. But if all you know is objects, then using relational technology turns difficult, and SQL, for all the good things with it, seems old-fashioned and arcane, which it is (but it is so widely used you cannot avoid it). So you go with something that looks at objects as all you need, and present that in some object format. Like JSON.And again, there is nothing wrong with that. But if we who are on the SQL and Relational track just discards these NoSQL technologies, we are not making any friends. We have to accept that MySQL and MariaDB really aren't that easy to use anymore, at least not for newcomers.And then there is another thing: Some data, like Big Data, has attributes that really doesn't fit well in a relational model. Data where the attribute of a value can't easily be determined once and for all, and you need to reprocess that data (large test objects, images and maps are some examples). In this case, you really need to extend the relational model, somehow.But SQL-based relational isn't going away. The Relational model is still one of the best ways to look at data, it's just that we also need some other ways of looking at data. And it needs to be easier to access. And we shouldn't really have to push SQL down the throat of every single developer, trying to develop an application using some OO technology. The answer is we need both. And these technologies needs to interoperate. I want to use SQL for my data. But I also want JSON and REST for my data. And there shouldn't be much of a performance overhead. All in all, we SQL folks need to wake up and data easier to use again. We know data better than the Cassandra and MongoDB folks. We know transactions better than them too. But they know how to work with developers who doesn't know who The Beatles were and make Relational easy to use for them, without them having to learn JSON (and now having to listen to a tirade about todays youngsters not knowing what real music is and that it died with John Lennon! What? You don't know who John Lennon was! That's exactly what I mean, you have no taste at all!).Just my 2 cents.../Karlsson
|