Use facter to Audit MyISAM usage

RESOLVED FIXED

Status

Data & BI Services Team
DB: MySQL
RESOLVED FIXED
6 years ago
4 years ago

People

(Reporter: sheeri, Assigned: rtucker)

Tracking

Details

(Whiteboard: [2013q1])

(Reporter)

Description

6 years ago
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.
(Reporter)

Updated

6 years ago
Assignee: server-ops-database → scabral
See Also: → bug 784027
(Reporter)

Updated

6 years ago
Depends on: 804136
(Reporter)

Comment 1

5 years ago
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';
Whiteboard: [2013q1]
(Reporter)

Comment 2

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

Comment 3

5 years ago
(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
(Assignee)

Updated

5 years ago
Assignee: scabral → rtucker
(Assignee)

Comment 4

5 years ago
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}'
(Assignee)

Comment 5

5 years ago
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
(Assignee)

Comment 6

5 years ago
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.
(Reporter)

Comment 7

5 years ago
Ready when you are! awesome quick awesomeness.
(Assignee)

Comment 8

5 years ago
Pushed to svn. 

Committed revision 55818.

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

Comment 9

5 years ago
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}'
(Assignee)

Comment 10

5 years ago
Updated the query and commited.
(Reporter)

Comment 11

5 years ago
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.
(Assignee)

Comment 12

5 years ago
Fixed and Committed revision 55823.
(Reporter)

Comment 13

5 years ago
Works!

[root@addons1.db.phx1 ~]#  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
[root@addons1.db.phx1 ~]# 

[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!
Status: NEW → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
(Reporter)

Updated

5 years ago
Summary: Audit MyISAM usage → Use facter to Audit MyISAM usage
(Reporter)

Updated

5 years ago
Blocks: 828450
Product: mozilla.org → Data & BI Services Team
You need to log in before you can comment on or make changes to this bug.