784031 - Use facter to Audit MyISAM usage

Status: RESOLVED FIXED

(Data & BI Services Team :: DB: MySQL, task)

Description

[Sheeri Cabral [:sheeri]]

MyISAM is usually not the appropriate storage engine for most Mozilla projects. Bad performance and replication breakages can be caused by this.

Let's audit the use of the MyISAM tables that exist, and work on converting the inappropriate databases.

Comment 1

[Sheeri Cabral [:sheeri]]

Rob, would it be possible to generate a list of what machines have MyISAM tables and how many tables there are for each machine? This can be done with a MySQL query like:

"SELECT COUNT(*),TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' GROUP BY TABLE_SCHEMA";

and somehow saving the hostname too. Did we decide facter would work for this kind of thing?

If so, it would be very useful to get a similar list of:
 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME!='utf8';

Comment 2

[Sheeri Cabral [:sheeri]]

(for reference, by end of Q1 2013, generate a list of the MyISAM tables)

Comment 3

[Rob Tucker [:rtucker]]

(In reply to Sheeri Cabral [:sheeri] from comment #2)
> (for reference, by end of Q1 2013, generate a list of the MyISAM tables)

I don't see why this wouldn't be possible with facter

Comment 4

[Rob Tucker [:rtucker]]

To get the count of MyISAM tables this bash one liner works:

echo "SELECT COUNT(*),TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' GROUP BY TABLE_SCHEMA;" | mysql | tail -n 1 | awk '{print $1}'

Comment 5

[Rob Tucker [:rtucker]]

To get the databases without the a default character set of utf8

echo "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME!='utf8';" | mysql | tail -n +2

Comment 6

[Rob Tucker [:rtucker]]

I've got these 2 new facts staged and ready to push. Let me know when you're available for me to push and to test/confirm.

Comment 7

[Sheeri Cabral [:sheeri]]

Ready when you are! awesome quick awesomeness.

Comment 8

[Rob Tucker [:rtucker]]

Pushed to svn. 

Committed revision 55818.

Should take a while for these to start being available as facts.

Comment 9

[Sheeri Cabral [:sheeri]]

update to the query in comment 4:

echo "SELECT COUNT(*),TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql','performance_schema','percona') GROUP BY TABLE_SCHEMA;" | mysql | tail -n 1 | awk '{print $1}'

Comment 10

[Rob Tucker [:rtucker]]

Updated the query and commited.

Comment 11

[Sheeri Cabral [:sheeri]]

One more revision:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','percona');

I added in "information_schema" in the list to ignore and took out the grouping, since facter is only getting in the last line anyway.

Comment 12

[Rob Tucker [:rtucker]]

Fixed and Committed revision 55823.

Comment 13

[Sheeri Cabral [:sheeri]]

Works!

[[e-mail]1 ~]#  facter -p  | grep -i mysql
mysql_databases => information_schema,addons_mozilla_org,mysql,percona
mysql_isam_table_count => 1
mysql_non_utf8_databases => addons_mozilla_org
mysql_server_version => 5.1.65
[[e-mail]1 ~]# 

[root@support1 ~]# facter -p | grep mysql
mysql_databases => information_schema,mysql,openfire_chat,percona,support_mozilla_com
mysql_isam_table_count => 74
mysql_non_utf8_databases => openfire_chat,percona,support_mozilla_com
mysql_server_version => 5.1.66

w00t!