-
Presenting on new MySQL Cluster 7.1 features at MySQL UC (and discount code!)
Together with Berndt I’ll be presenting on the new features in MySQL Cluster 7.1 at this year’s MySQL Cluster User Conference – Santa Clara, on April 12th. If you’re interested in using MySQL Cluster but aren’t sure how to get started (or you’ve used it but would like some tips) then this is a great opportunity. Check out the presentation description.
If you register by 15 March then you get the early-bird price and if you use this ‘friend of a speaker’ code then you get an additional 25% off: mys10fsp
mys10fsp
-
MySQL Cluster on Windows – webinar replay available
If you missed the recent webinar on running MySQL Cluster on Windows then you can watch/listen to the replay at http://www.mysql.com/news-and-events/on-demand-webinars/display-od-517.html
-
Things to monitor on MySQL, the user’s perspective
Working on mycheckpoint, I have the intention of adding custom monitoring. That is, letting the user define things to monitor. I have my own thoughts, I would be grateful to get more input!
What would the user want to monitor?
Monitoring for the number of SELECT statements per second, InnoDB locks, slave replication lag etc. is very important, and monitoring utilities provide with this information. But what does that tell the end user? Not much.
The experienced DBA may gain a lot. The user would be more interested in completely other kind of information. In between, some information is relevant to both.
Say we were managing an on-line store. We want to monitor the health of the database. But the health of the database is inseparable from the health of the application. I mean, having little to no disk usage is fine, unless… something is wrong with the application, which leads to no new purchases.
And so a user would be interested in monitoring the number of purchases per hour, or the time passed since last successful purchase. This kind of data can only be generated by a user’s specific query. Looking at the charts, the user would then feel safer and confident in the wellness of his store app.
But let’s dig further. We want the store’s website to provide with good response. In particular, the query which returns the items in a customer’s cart must react quickly. Our user would not only want to see that purchases get along, but also that page load times (as in our example) are quick for those critical parts. And so a user should be able to monitor the time it took to execute a given query.
It can be of further interest to know how many times per second a given query is executed. This part is not easily done on the server side, and requires the user’s cooperation (or else we must analyze the general log, sniff, or set up a proxy). If the user is willing, she can log to some table each time she executes a certain query. Then we’re back to monitoring a regular table, as with the first example.
It is also possible to monitor for a query’s execution plan. Is it full scan? How many rows are expected? But given that we can monitor the time it took to execute a query, I’m not sure this is useful. If everything runs fast enough — who cares about how it executes?
Some of the above can be monitored on an altogether higher level: if we’re talking about some web application, then we can use our Apache logs to determine load time for pages, or number of requests to our “cart items” page. But not always do we work with web servers, and we may be interested in checking the specific queries behind the scenes.
Summary
Custom monitoring can include:
User defined queries (number of concurrent visitors; count of successful operations per second; number of rows per given table or condition; …)
Execution time for user defined queries (time it takes to return cart items; find rows matching condition; sort a table; …)
Number of executions for a given query, per second.
I intend to incorporate the above into mycheckpoint as part of its standard monitoring scheme.
Please share your thought below.
-
Its a cheat! Get Linux performance information from your MySQL database without shell access.
System administrators familiar with the Linux operating system use the tools in the 'procps' toolset all the time. Tools which read from /proc include top, iostat, vmstat, sar and others. The files in /proc contain useful information about the performance of the system. Most of the files are documented in the Linux kernel documentation. You can also check man 5 proc.Most performance monitoring tools invoke other tools like iostat to collect performance information instead of reading from the /proc filesytem itself. This begs the question, what can you do if you don't have access to those tools? Perhaps you are using a hosted Linux database and have no access to the underlying shell to execute tools like iostat or top? How could you gather information about the performance of the actual system without being allowed to run the tools?MySQL includes a command called LOAD DATA INFILE which can read the contents of a delimited text file and store the contents into a database table. The contents of /proc are world readable, so your MySQL database should have access to this information as long as it is running on a Linux server. Lets start by collecting and reporting on some CPU performance information.
CREATE TEMPORARY TABLE test.proc_stat (
seq tinyint auto_increment primary key,
the_key char(25) NOT NULL,
user bigint,
nice bigint,
system bigint,
idle bigint,
iowait bigint,
irq bigint,
softirq bigint,
steal bigint,
guest bigint,
other bigint
);
/* MySQL treats consecutive delimiters as separate fields, so some fancy footwork
is required to load the file successfully. The file includes a cpu field followed
by two spaces which is the sum of all the individual CPUs in the system.
To account for this each row is read into some MySQL variables. Those variables
are examined to determine which field holds the correct value.
*/
LOAD DATA INFILE '/proc/stat'
IGNORE INTO TABLE test.proc_stat
FIELDS TERMINATED BY ' '
(@the_key, @val1, @val2, @val3, @val4, @val5, @val6, @val7, @val8, @val9, @val10)
SET other = IF(@the_key like 'cpu%', NULL, @val1),
the_key = @the_key,
user = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val1, 0), IFNULL(@val2,0))),
nice = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val2, 0), IFNULL(@val3,0))),
system = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val3, 0), IFNULL(@val4,0))),
idle = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val4, 0), IFNULL(@val5,0))),
iowait = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val5, 0), IFNULL(@val6,0))),
irq = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val6, 0), IFNULL(@val7,0))),
softirq = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val7, 0), IFNULL(@val8,0))),
steal = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val8, 0), IFNULL(@val9,0))),
guest = IF(@the_key NOT LIKE 'cpu%', NULL, IF(@the_key != 'cpu', IFNULL(@val9, 0), IFNULL(@val10,0)));
Depending on your kernel version you may get 1 or more warnings about unexpected numbers of columns. You can safely ignore these.
mysql> select * from test.proc_stat;
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
| seq | the_key | user | nice | system | idle | iowait | irq | softirq | steal | guest | other |
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
| 1 | cpu | 378340 | 33588 | 82489 | 1838257830 | 75444 | 750 | 23065 | 0 | 0 | NULL |
| 2 | cpu0 | 4152 | 125 | 1613 | 114920899 | 624 | 0 | 869 | 0 | 0 | NULL |
| 3 | cpu1 | 2182 | 78 | 1474 | 114924477 | 50 | 2 | 3 | 0 | 0 | NULL |
| 4 | cpu2 | 6037 | 5418 | 2289 | 114914024 | 55 | 34 | 401 | 0 | 0 | NULL |
| 5 | cpu3 | 3519 | 55 | 842 | 114923794 | 37 | 1 | 1 | 0 | 0 | NULL |
| 6 | cpu4 | 71851 | 5443 | 6656 | 114840363 | 3197 | 11 | 720 | 0 | 0 | NULL |
| 7 | cpu5 | 2435 | 5 | 801 | 114924963 | 29 | 2 | 0 | 0 | 0 | NULL |
| 8 | cpu6 | 136246 | 4711 | 36628 | 114690032 | 46119 | 20 | 14471 | 0 | 0 | NULL |
| 9 | cpu7 | 1119 | 2 | 366 | 114926691 | 40 | 1 | 0 | 0 | 0 | NULL |
| 10 | cpu8 | 4126 | 34 | 2772 | 114920032 | 92 | 1 | 1153 | 0 | 0 | NULL |
| 11 | cpu9 | 1618 | 2 | 694 | 114925811 | 77 | 1 | 0 | 0 | 0 | NULL |
| 12 | cpu10 | 18096 | 8735 | 6823 | 114891588 | 396 | 179 | 2379 | 0 | 0 | NULL |
| 13 | cpu11 | 7243 | 2583 | 3559 | 114914559 | 241 | 1 | 2 | 0 | 0 | NULL |
| 14 | cpu12 | 5215 | 2380 | 2776 | 114915814 | 417 | 342 | 1237 | 0 | 0 | NULL |
| 15 | cpu13 | 3224 | 28 | 1507 | 114923336 | 77 | 2 | 0 | 0 | 0 | NULL |
| 16 | cpu14 | 109818 | 3979 | 13071 | 114775431 | 23901 | 143 | 1823 | 0 | 0 | NULL |
| 17 | cpu15 | 1450 | 1 | 612 | 114926010 | 83 | 1 | 0 | 0 | 0 | NULL |
| 18 | intr | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1176485951 |
| 19 | ctxt | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 171220339 |
| 20 | btime | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1267061074 |
| 21 | processes | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 168510 |
| 22 | procs_running | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 |
| 23 | procs_blocked | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0 |
+-----+---------------+--------+-------+--------+------------+--------+------+---------+-------+-------+------------+
23 rows in set (0.00 sec)
Now that you know you can collect that information, then you can emulate top to calculate the current total CPU usage. I'll show you how to do that in my next blog post.
-
Do you need more data in the slow query log?
Imagine you tried to use the slow query log to debug a performance problem. Does the current format have enough details?
# Time: 100309 18:48:23
# User@Host: root[root] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1
I have added Thread_id, Errno, Start and End. Thread_id can be used to find similar data from SHOW PROCESSLIST and the binlog. Errno is useful in many cases. Start and End are there for convenience. Can you suggest anything else that would be easy to add? Note that Rows_sent and Rows_examined are always zero for insert, update and delete statements. Feature request 49756 is open to change that. Maybe that is easy to fix.
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1\
Thread_id: 3 Errno: 0 Start: 18:48:23 End: 18:48:23
|
MY SQL