What do I want in a MySQL monitoring solution?

I've been actively working with MySQL for over two years now as DBA and I find it amusing how many tools I need to monitor what is going on in the databases.

Tools I use:

HeidiSQL (when using Windows)
SequelPro (when using a Mac)
MySQLWorkbench (both Windows and Mac)
JetProfiler (free version which has limitations)
Diffing tool (I use a few different ones, Xcode on a Mac has one called "FileMerge")
Text Editor (I like Sublime)
PuttyManager for when I'm on a Windows system  (MTPutty)
Box.com RainGauge
Box.com Anemometer
Various Command Level Tools (sysctl, top, vmstat, iostat, mpstat, lsof, df, netstat, pidof, flock)
MySQL command level Tools (mysql, mysqladmin, mysqlbinlog)
Percona Toolkit (online schema change, digest, deadlock-logger, heartbeat, stalk...)
Trend tool with charts (Zabbix, Ganglia, Cacti, etc)
Alerting Tools (Nagios, Zabbix, etc)
Custom tools (Bash Scripts, PHP scripts)
Monitoring tools for clusters (I don't have a tool for this yet as I'm still working on setting up Galera clusters and we don't use NDB cluster)

Many of these tools are for more than just monitoring but mot allow so little bit of monitoring that comes for free. I didn't realize how spoiled I was as an Oracle DBA with Enterprise Manager that does most of what was needed in one tool.

You may notice that all of the above tools are FREE.

I've done evaluations on paid monitoring tools with MonYong, MySQL Enterprise Monitor, and VividCortex. It is impressive what these tools can do but I don't have the budget to pay for them. I really like how JetProfiler does profiling and I've love to have tool that can profile constantly and keep that info for a long time like VividCortex. I really like what Box.com's anemometer can do in this regard but it still didn't perfect and required a number of other tools to make it all work. MonYog's profiler has some similarities to JetProfiler but not as good for profiling and their tier 2 support was located across the globe and slow to respond to my requests.

One of the things I really liked with MonYog/MySQL Enterprise monitor is how it tracks config variables and automatically recommends performance improvements and provides security warnings. I also liked how easy it was to compare configs from server to another in MonYog.

Oracle's MySQL Enterprise Monitor did a good job of tracking metrics but I thought the graphs were too hard to read, probably I had just gotten so used to reading charts in Cacti that I thought the Oracle graphs were kinda weird. Oracle has good alerting, as does MonYog but I didn't need it because we already have a tool for that using Nagios.

VividCortex is pretty good but I'd like to see it include the same features that JetProfiler has. I really like Box.com's anemometer and Box.com's rain guage but they are open source software that you have to setup and manage yourself and procuring resources is difficult. I think VividCortex has come the closest so far to combining them all into one tool but at this point, I'm not really happy with any of them and so I have to run a dozen separate tools to accomplish what I want. And then for SQL Server and MongoDB and other NoSQL solutions I have separate monitoring solutions. Also, the licensing is too expensive when your company setups MySQL instances like crazy so even if one tool met all the requirements, I don't think I'd ever get a purchase order signed to use it across the board meaning I'd still have to use lots of different tools. Percona has made a number of enhancements to MySQL in Percona Server which MonYog has done a good job of including in their monitoring metrics but I haven't seen that in other monitoring tools.

For MySQL some of the things I'd like to have in a tool

1. Box.com rainguage to track how often and all the metrics associated with a specific type of event happens
2. Box.com anemometer to track historical trends for specific queries
3. Trend analysis tool like Zabbix/Cacti/Ganglia for seeing historical trends of the OS/MySQL counters
4. Profiler tool like JetProfiler but one that is always on
5. Custom tools we created to track config changes, sizes, row counts, compare changes
6. Various command level tools
7. MySQL workbench to do admin type of work
8. Want to be able to monitor galera clusters
9. Want to be able to monitor Percona specific additions
10. Want to see currently locking/blocking queries like how Oracle Enterprise Manager does it for Oracle databases
11. Want the tool to keep track of the size and count of all my tables and tell me how much they grew or changes day by day, week by week, month by month
12. Intelligent recommendations telling me where I'm missing indexes, which queries need tuning
13. Tracking of which queries I've already reviewed and don't want to see anymore in the profiler, a history showing when the last time I reviewed this specific query

utf8 issues and Illegal mix of collations error

I have a table at work in a legacy database. It is part of a sharded database setup so there are thousands upon thousands of these tables with different data across all our customers. We allow customers via the app to put whatever they want in this table. So it ends up with crap data some times. What is worse, is that the table doesn't support utf8 characters but clients somehow still end up trying to write utf8 characters into it. Below is an example of the table and the type of table that might be in the table. As you can see there is a unique constraint on the "custom_id" column and the "value" column. If you look through the list through you will see what looks like a space at the end of some of the words which is actually some type of blank character which doesn't break the unique constraint.

SET NAMES 'latin1';

CREATE TABLE `my_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `custom_id` int(10) unsigned NOT NULL DEFAULT '0',
  `value` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uq_cutom_id_value` (`custom_id`,`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `my_table` (`custom_id`, `value`)
VALUES
 (34, 'Outside of the U.S.'),
 (142, 'AutomotiveÊ'),
 (142, 'ConstructionÊ'),
 (142, 'EnergyÊ'),
 (142, 'InsuranceÊ'),
 (142, 'ManufacturingÊ'),
 (142, 'Non-ProfitÊ'),
 (142, 'Real EstateÊ'),
 (142, 'RetailÊ'),
 (142, 'TelecommÊ'),
 (142, 'Manufacturing'),
 (142, 'Telecomm'),
 (142, 'Automotive'),
 (34, 'Outside of the U.S.Ê'),
 (142, 'Insurance'),
 (142, 'Real Estate'),
 (142, 'Construction'),
 (142, 'Non-Profit'),
 (142, 'Energy'),
 (142, 'Retail');

This is what the data looks like after sorted by custom_id and value:

The app has code which write a MySQL statement like this:

SET NAMES 'utf8';

SELECT value
FROM my_table
WHERE custom_id = 142 AND value = 'Manufacturing�'

Because of that funky utf8 character in the end of the SELECT statement we frequently get the following ERROR:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

From a DBA perspective, I'm thinking what am I suppose to do here to fix this problem? The table and column the query is working against doesn't support utf8 so I keep getting that error.

If I convert the table and column to utf8 then I stop the error from occurring like this:

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Then my data would look like this:

But then I will still have all that left over crap data (which might be fine). I came up with a couple ideas but not sure how amazing they are. Either delete the rows with the bad data or update them to clean up the bad data and then add something else in to make them unique.

Here I clean up the data by converting if from latin to binary and then utf8. This gets rid of the junk data but then creates duplicates so I won't be able to add the unique key back. In my case there are child tables that reference the id column so I cannot delete the rows. Here I just add a period at the end of the row.

ALTER TABLE `my_table` DROP INDEX `idx_uq_cutom_id_value`;

ALTER TABLE my_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

UPDATE my_table SET
value = CONVERT(CAST(CONVERT(value USING latin1) AS BINARY) USING utf8);

UPDATE my_table SET
value = CONVERT(CAST(CONVERT(value USING latin1) AS BINARY) USING utf8);

UPDATE my_table SET
value = CONCAT(value,'.')
WHERE id IN (SELECT * FROM (SELECT c2.max
FROM my_table c1
INNER JOIN (SELECT id, custom_id, MAX(id) as max, count(*) as count FROM my_table GROUP BY custom_id, value HAVING count > 1) c2 ON c1.id = c2.id
GROUP BY c1.custom_id, value) t);

ALTER TABLE `my_table` ADD UNIQUE INDEX `idx_uq_cutom_id_value` (`custom_id`, `value`);

If it was permissible to DELETE the data duplicates I could do so like this:

DELETE FROM my_table
WHERE id IN (SELECT * FROM (SELECT c2.max
FROM my_table c1
INNER JOIN (SELECT id, custom_id, MAX(id) as max, count(*) as count FROM my_table GROUP BY custom_id, value HAVING count > 1) c2 ON c1.id = c2.id
GROUP BY c1.custom_id, value) t);

Evaluation of VividCortex

The sales guys at VividCortex have been hounding me to do a trial with them so I got approval from my manager to setup their agents on our database servers. Overall,  really like the monitoring provided and the support from the VividCortex has been excellent. Even Baron the CEO got on the phone to speak with us. Sadly, my company has little interest in monitoring the database servers this closely. The profiler provided by VividCortex has been the best I've seen. There have been a few annoying things with the website which I wish could change but the support people said that they were not interested. For example, we have a database server with far too many schemas on it. Over 1000 database schemas for a SAAS app. The VividCortex dashboard would only allow me to see the top 50 busiest schemas. I know it is unusual to have such a large number of schema on a database server so that makes sense but I still wish I could have seen more on their dashboard.

While reviewing queries in the profiler, I wanted to be able to hide or snooze queries I had already looked at it. If I am going to look at this everyday and try to find improvements, I don't want to see the exact same queries everyday that I know development will never fix and just want to skip onto the other ones.

I liked how the agent could catch warnings that normally would be lost of only caught on the application side. It looked like every single query was being caught each second. When I've used other profiler like JetProfiler, it isn't able to catch everything.

There wasn't much interest in development in reviewing their queries on production. I kind of thought the developers would be more excited to see how their app is performing in production. Perhaps they are too busy and don't entirely feel any responsibility for the operations side at this point.

The price point was also too difficult for my company to accept. They seem to be satisfied with a piece of junk software like Ganglia which averages out out database performance metrics every hour and doesn't even provide query analytics because it is free.

chmod converter

I'm not a systems engineer and figuring out Linux permissions sometimes gets me confused. I like sites like this that allow me to easily input what permissions I want it and will show me the octal and the permissions.

https://chmod-converter.com/
http://permissions-calculator.org/

Mysterious foreign key errors - MySQL Error 1215: Cannot add foreign key constraint"

I was trying to add a foreign key to table and I kept getting that silly MySQL error that doesn't provide much information: "MySQL Error 1215: Cannot add foreign key constraint".

The constraints were all done correctly, the data types all matched and I tested this on a different system with just the two tables involved in the foreign key constraint and it worked fine. However, when trying to add the foreign key constraint on the actual system I kept getting errors. I did some reading and someone posted here to look at SHOW ENGINE INNODB STATUS;

Near the top was an error from a table we had stopped using but was some how still related to the one of the tables that I was trying to add the foreign key constraint to. I dropped that other table because it had been deprecated and re-ran the code to add the foreign key constraint and voila it worked. :)