OurSQL Episode 136: Digging for Information

We discuss INFORMATION_SCHEMA with plenty of examples on useful queries. In this week's Ear Candy, we talk about resident versus virtual memory on Linux, and in At the Movies has a video about MongoDB.

Events
DB Hangops in May will be Wed May 8th and 22nd noon pacific time.
Upcoming MySQL tech tours

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

INFORMATION_SCHEMA
INFORMATION_SCHEMA in the MySQL manual
TABLES
Find the top 5 largest tables:

SELECT table_name, data_length, index_length, (data_length + index_length) as total_size
FROM INFORMATION_SCHEMA.TABLES
ORDER BY total_size DESC LIMIT 5;

Total disk usage by schema:

SELECT table_schema, sum(data_length), sum(index_length), sum(data_length) + sum(index_length)
FROM tables
GROUP by table_schema;

Maintenance script to OPTIMIZE TABLE on the tables smaller than 10 million bytes (about 9 Mb):

SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';')
INTO OUTFILE '/tmp/optimize.sql'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'schema' AND data_length < 10000000;
SOURCE /tmp/optimize.sql

Same maintenance script, but done on commandline:

mysql -B -N information_schema -e "SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';') FROM tables WHERE table_schema = 'schema' AND data_length < 10000000" > optimize.sql
mysql < optimize.sql

See where your MyISAM tables are:

SELECT TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM' AND TABLE_SCHEMA not in ('mysql','information_schema','performance_schema');

See what storage engines are being used, with counts:

SELECT ENGINE,COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema')
GROUP BY ENGINE;

Group it by database:

SELECT TABLE_SCHEMA,ENGINE,COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema')
GROUP BY TABLE_SCHEMA,ENGINE;

Find databases without utf8 as their default character set:

SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE DEFAULT_CHARACTER_SET_NAME!='utf8';

Maintenance script to change these databases to have a default character set of utf8:

SELECT CONCAT('ALTER DATABASE ', SCHEMA_NAME, ' CHARACTER SET utf8;')
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE DEFAULT_CHARACTER_SET_NAME!='utf8';

GLOBAL_STATUS and SESSION_STATUS tables in INFORMATION_SCHEMA

Query status variables for number queries:

SELECT *
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name in ('com_insert', 'com_select', 'com_update', 'com_delete', 'com_replace', 'queries');
SELECT SLEEP(10);<code>

<A HREF="http://bit.ly/oursql132">OurSQL episode 132, where we talked about the new connection_error counters in MySQL 5.6.</A>
Find connection errors:
<code>SELECT * FROM GLOBAL_STATUS WHERE variable_name LIKE 'connection_errors%';

mysqltuner 2.0, allows you to make comparisons based on system and status variables by using the variable name in a calculation.
PalominoDB Nagios plugin, based on mysqltuner 2.0, which allows you to monitor arbitrary calculations which include system and status variables.

Find information about the InnoDB buffer pool:

SELECT *
FROM GLOBAL_STATUS
WHERE variable_name LIKE 'innodb_buffer_pool_pages_%';

Look at InnoDB activity:

SELECT *
FROM GLOBAL_STATUS
WHERE variable_name LIKE 'innodb_data_%';

MySQL status variables

PROCESSLIST table in INFORMATION_SCHEMA

Top 5 slowest active (non-sleeping) queries:

SELECT id, user, host, db, time, info
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE Command!='Sleep'
ORDER BY TIME DESC LIMIT 5;

Top 5 slowest active queries, not including replication (example shows 'slave_user' as the replication user):

SELECT id, user, host, db, time, info
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE Command!='Sleep' AND user NOT IN ('system user','slave_user')
ORDER BY TIME DESC LIMIT 5;

Find out what time replication is at, for lagging replication:

SELECT NOW()-INTERVAL TIME SECOND
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE USER='SYSTEM USER';

ROUTINES table in INFORMATION_SCHEMA for seeing stored functions and stored routines

Show all stored functions and procedures:

SELECT routine_schema, routine_name, routine_type, created, last_altered
FROM ROUTINES;

REFERENTIAL_CONSTRAINTS table of INFORMATION_SCHEMA

Drop foreign key constraints script:

SELECT CONCAT('ALTER TABLE ', constraint_schema, '.', table_name, ' DROP FOREIGN KEY ', constraint_name, ';')
FROM referential_constraints
WHERE constraint_schema = 'schema';

Find out what foreign keys reference this_table:

SELECT constraint_schema,table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE referenced_table_name='this_table';

The new INFORMATION_SCHEMA tables in MySQL 5.6:
Optimizer trace example including how to read a trace from the information_schema OPTIMIZER_TRACE table.

INFORMATION_SCHEMA.FILES

Ear Candy
memory consumption using a metaphor of roommates sharing expenses

SELECT *
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE variable_name LIKE 'innodb_buffer_pool_pages_%';

In the OS:
ps -eo pid,rss,vsz,user,command | grep [m]ysqld

At the Movies
This week in At the Movies we present Introduction to MongoDB by Waldo Grunenwald, delivered a month ago at the Open Database Camp in conjunction with Northeast LinuxFest.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1240