Google Patents

So I came across Google Patents the other day and thought that it was a pretty cool idea! Searching for whether a patent already exists had been a pretty hard job in the past, so having a readily available resource such as Google is a fantastic step forwards.

Especially in light of the recent Microsoft ‘FUD’.

Here’s a patent I came across whilst browsing around the various database related patents that exist out there:

http://www.google.com/patents?id=zLsYAAAAEBAJ&dq

Electronic shopping and merchandising system

Abstract
The present invention provides a merchant system for online shopping and merchandising. The merchant system architecture provides great flexibility for a merchant to adapt the merchant system to their existing business practices, promotions and databases. The merchant system includes a dynamic page generator, a configurable order processing module and a database module capable of retrieving data from the database without regard to its schema. The present invention enables merchants to create electronic orders which are easily adaptable for different sales situations. The order processing module includes multiple configurable stages to process a merchant’s electronic orders. The merchant system is capable of generating pages dynamically using templates having embedded directives. The database module and the dynamic page generator allow merchants to modify their databases and page displays without having to reengineer the merchant system.

Wow, it looks to me like Microsoft patented the Online shopping cart! There’s a lot of interesting patents that Microsoft seem to have taken out. Google makes searching for these relatively easily.

There’s some great other patents as well out there too:

The OUIJA-BOABD
The FLYING-MACHINE

And every geek’s favourite!

The POCKET PROTECTOR

Aggregating SHOW STATUS Output

When I first joined MySQL one of the things that was evident was the Support Engineers spent quite some time with customers issues that were focused on performance tuning. Performance tuning issues generally start with a engineer requesting a bunch of information from the customer such as:

  • SHOW GLOBAL VARIABLES
  • SHOW GLOBAL STATUS (a number of times, to give us some rate information)
  • SHOW FULL PROCESSLIST
  • SHOW INNODB STATUS (if InnoDB is widely being used)
  • vmstat output for a number of short periods
  • iostat -dx output for a number of short periods

A lot of this output is pretty easy to go through when you know what you are looking for. However where we spend a lot of our time is looking through the SHOW GLOBAL STATUS output – trying to piece together the rates of change etc. so that we can get more insight in to what is hurting the database.

I also had never used Python – and wanted to give it a try – and said some time early last year “One of these days I’m going to create a Python script that aggregates all of this stuff for us“. Well, towards the end of last year (and with some extra stuff added early this year), I actually went ahead and took the dive and created such a script. Now, I know there are some scripts out there that already do some of this from other people (such as the pretty good one put together over on hackmysql.com) – however these are scripts that all connect to a running database, and get stats to aggregate in that way. This really didn’t help us in the Support Group – as we needed customers to send us stats in files generally (although sometimes we do connect and troubleshoot that way). It also didn’t serve the purpose of my wanting to try Python 🙂

I’ve reached a point with this script now where I’m ready to shove it out to the world to use as they wish, and request feedback on it. The current version I am releasing here is actually a little behind – I’m working on an ‘interactive’ version as well (more like mysqlreport et al), but more on that later.

Here’s a sample of what the report spits out now:

[markleith@medusa:~/Development] $ ./statpack.py statsfiles/mysql_collect_200701071916.txt statsfiles/mysql_collect_200701071921.txt

Number of Snapshots: 2

====================================================================================================
Uptime: 12 hours 29 min Snapshot Period 1: 4 minute interval
====================================================================================================
Variable Delta/Percentage Per Second Total
====================================================================================================

=========================================Database Activity==========================================

Connections: 12,193 43.70 1,496,977
Threads Connected: 9 432
Threads Running: 26 284
Questions: 1,235,947 4,429.92 159,441,275
Bytes Recieved: 771,889,505 2,766,629.05 99,484,958,519
Bytes Sent: 4,531,361,061 16,241,437.49 575,614,342,973
Aborted Clients: 0 0.00 368
Aborted Connects: 0 0.00 1

=========================================Statement Activity=========================================

SELECT: 197,280 707.10 26,224,157
INSERT: 13,061 46.81 1,568,744
UPDATE: 14,932 53.52 1,774,077
DELETE: 12,848 46.05 1,585,598
REPLACE: 0 0.00 0
INSERT ... SELECT: 0 0.00 0
REPLACE ... SELECT: 0 0.00 0
Multi UPDATE: 0 0.00 0
Multi DELETE: 0 0.00 0
COMMIT: 21 0.08 2,459
ROLLBACK: 4 0.01 447

========================================Prepared Statements=========================================

Prepared Statement Count: Status variable not available in your MySQL version
PREPARE: 0 0.00 0
EXECUTE: 0 0.00 0
DEALLOCATE PREPARE: 0 0.00 0
Fetch Roundtrips: 0 0.00 0
Send Long Data: 0 0.00 0

===========================================Admin Commands===========================================

KILL: 0 0.00 0
FLUSH: 0 0.00 0
ANALYZE TABLE: 0 0.00 0
OPTIMIZE TABLE: 0 0.00 0
CHECK TABLE: 0 0.00 0
REPAIR TABLE: 0 0.00 0

==========================================Temporary Space===========================================

tmp_table_size Efficiency: 11.64%
Memory Temp Tables: 6,599 23.65 978,895
Disk Temp Tables: 5,912 21.19 864,973
Temp Files: 0 0.00 5

==========================================MyISAM Key Cache==========================================

Cache Read Efficiency: 99.98%
Cache Write Efficiency: 51.94%
Memory Used: 64Kb 57Mb
Memory Free: -64Kb 780Mb
Key Reads: 76 0.27 60,426
Key Read Requests: 2,241,530 8,034.16 283,675,567
Key Writes: 5,399 19.35 863,132
Key Write Requests: 15,455 55.39 1,795,774
Blocks Not Flushed: 12 0.04 15

============================================Table Cache=============================================

table_cache Efficiency: 99.49%
Open Tables: 0 0.00 1,357
Opened Tables: 0 0.00 1,363

=========================================InnoDB Buffer Pool=========================================

Buffer Pool Read Efficiency: 100.00%
Memory Total: 0Kb 1,024Mb
Memory Free: -1,280Kb 984Mb
Memory Data: 1,264Kb 38Mb
Memory Dirty: -592Kb 0Mb
Data Read: 1,232Kb 37Mb
Data Written: 16,665Kb 1,793Mb
Buffer Pool Reads: 76 0.27 1,178
Buffer Pool Read Requests: 460,617 1,650.96 66,374,804
Buffer Pool Write Requests: 1,584 5.68 183,697

==========================================InnoDB Log Files==========================================

Log Data Written: 114Kb 16Mb
Log Writes: 57 0.20 7,124
Log Write Requests: 182 0.65 32,096
Log Waits: 0 0.00 0

============================================Query Cache=============================================

QCache Hits / SELECT: 81.76%
QCache Hit/Qcache Insert: 83.71%
Qcache Hits/Invalidations: 23.00%
SELECTs: 197,280 707.10 26,224,157
Query Cache Hits: 913,101 3,272.76 117,563,266
Query Cache Inserts: 164,677 590.24 22,869,761
Queries Not Cached: 32,566 116.72 3,356,727
Cache Low Memory Prunes: 151,782 544.02 19,606,315
Total Cache Blocks: -1,126 -4.04 301,180
Queries In Cache: -280 -1.00 123,023
Cache Free Blocks: -539 -1.93 47,065

============================================Index Usage=============================================

Index Efficiency: 90.96%
Full Index Scans: 7,652 27.43 875,018
Full Table Scans: 30 0.11 4,289
Full Join Scans: 0 0.00 0
Handler_read_first: 7,652 27.43 875,018 (0.21%)
Handler_read_key: 841,736 3,016.97 116,933,614 (27.52%)
Handler_read_next: 2,569,564 9,209.91 268,699,389 (63.24%)
Handler_read_prev: 0 0.00 0 (0.00%)
Handler_read_rnd: 97,563 349.69 13,331,811 (3.14%)
Handler_read_rnd_next: 43,357 155.40 25,075,543 (5.90%)

==========================================Lock Contention===========================================

Percent of Locks Waited: 13.08%
Table Locks Waited: 47,482 170.19 4,394,859
Table Locks Immediate: 209,882 752.27 29,201,590

==============================================Sorting===============================================

Rows Sorted: 90,014 322.63 12,448,923
Sort Range: 32,914 117.97 4,372,453
Sort Scan: 6,279 22.51 924,212
Sort Merge Passes: 0 0.00 0
Full Range Joins: 0 0.00 0

Input files can contain many snapshots of SHOW STATUS within the same file, and you can also pass in any number of files in chronological order (or a mixture of the two). StatPack also understands status files with both “batch” and “non-batch” outputs. That is to say it currently can parse files of both of the following formats:

[markleith@medusa:~/Development] $ mysql -u root -e "SHOW STATUS"
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 16 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
...

[markleith@medusa:~/Development] $ mysql -u root -B -e "SHOW STATUS"
Variable_name Value
Aborted_clients 16
Aborted_connects 0
Binlog_cache_disk_use 0
...

Currently it does not support the “\G” output.

There will be one report for each period reported within the “Number of Snapshots” section (less one, as initially we need 2 reports to start aggregation). I.e if it reports 4 snapshot periods, you will get 3 reports based on the differences between each snapshot.

Now, as I’ve learned a little more a Python I’ve realized I’ve done some things that could…. be done better… 🙂 I’m working ‘Version 2’, which is changing a fair bit of the way things are done, and also adds in connecting to a running server to generate a report from as well. Currently this is what it supports:

[markleith@medusa:~/Development] $ ./statpack-interactive.py

Error: No arguments supplied
usage: ./statpack-interactive.py [list of arguments]

Non-interactive mode (aggregate txt files containing SHOW STATUS snapshots):

-f --files List of statistics files to aggregate (--files=stat1.txt[, stat2.txt..])
Argument must be first within list of arguments, on it's own

Interactive mode (connect to running MySQL server for SHOW STATUS snapshots):

-h --host Host for MySQL server to connect to in interactive mode
-P --port Port the MySQL server is running on
-S --socket UNIX domain socket to use (instead of -h and -P)
-u --user User to connect to the MySQL server with
-p --password Password for the user
-d --defaults-file Defaults file to read options from (reads [client] group by default)
-i --interval Length of each collection interval
-c --report-count Count of intervals to collect and aggregate
-r --report-file File to output report data to
-s --status-file File to output raw SHOW STATUS data to

I’m still ironing a few things, but wanted to get this last version out for any feedback that anybody would like to provide before I release the next version! You can download the ‘current’ version from http://markleith.co.uk/dl/statpack-v1.tar.gz.

Please do provide feedback!

Monitoring OS statistics with INFORMATION_SCHEMA plugins

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created based on the SIGAR library ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

There are two INFORMATION_SCHEMA tables within the plugin library – INFORMATION_SCHEMA.OS_STATUS and INFORMATION_SCHEMA.OS_VARIABLES – much like MySQL’s current (well, within 5.1) INFORMATION_SCHEMA.GLOBAL|SESSION_STATUS and INFORMATION_SCHEMA.GLOBAL|SESSION_VARIABLES tables.

Here’s some sample output:

mysql> install plugin os_status soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin os_variables soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from os_variables;
+---------------------------+-------------------+----------------------------+---------------+
| VARIABLE_NAME             | VARIABLE_INSTANCE | VARIABLE_VALUE             | VARIABLE_TYPE |
+---------------------------+-------------------+----------------------------+---------------+
| Cpu_vendor                | cpu0              | Intel                      | VARCHAR       |
| Cpu_model                 | cpu0              | Xeon                       | VARCHAR       |
| Cpu_mhz                   | cpu0              | 3401                       | INTEGER       |
| Cpu_cache_size            | cpu0              | 1024                       | INTEGER       |
| Fs_name                   | /                 | /dev/md0                   | VARCHAR       |
| Fs_location               | /                 | local                      | VARCHAR       |
| Fs_type                   | /                 | ext3                       | VARCHAR       |
| Fs_name                   | /boot             | /dev/cciss/c0d0p1          | VARCHAR       |
| Fs_location               | /boot             | local                      | VARCHAR       |
| Fs_type                   | /boot             | ext3                       | VARCHAR       |
| Fs_name                   | /data0            | /dev/md10                  | VARCHAR       |
| Fs_location               | /data0            | local                      | VARCHAR       |
| Fs_type                   | /data0            | ext3                       | VARCHAR       |
| Fs_name                   | /users            | production:/usersnfs       | VARCHAR       |
| Fs_location               | /users            | remote                     | VARCHAR       |
| Fs_type                   | /users            | nfs                        | VARCHAR       |
| Fs_name                   | /nfstmp1          | nfssrva:/nfstmp1           | VARCHAR       |
| Fs_location               | /nfstmp1          | remote                     | VARCHAR       |
| Fs_type                   | /nfstmp1          | nfs                        | VARCHAR       |
| Rlimit_cpu_cur            |                   | 4294967295                 | INTEGER       |
| Rlimit_cpu_max            |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_cur      |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_max      |                   | 4294967295                 | INTEGER       |
| Rlimit_pipe_size_cur      |                   | 8                          | INTEGER       |
| Rlimit_pipe_size_max      |                   | 8                          | INTEGER       |
| Rlimit_data_cur           |                   | 4294967295                 | INTEGER       |
| Rlimit_data_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_stack_cur          |                   | 10240                      | INTEGER       |
| Rlimit_stack_max          |                   | 4294967295                 | INTEGER       |
| Rlimit_core_cur           |                   | 0                          | INTEGER       |
| Rlimit_core_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_cur         |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_max         |                   | 4294967295                 | INTEGER       |
| Rlimit_processes_cur      |                   | 16383                      | INTEGER       |
| Rlimit_processes_max      |                   | 16383                      | INTEGER       |
| Rlimit_open_files_cur     |                   | 1024                       | INTEGER       |
| Rlimit_open_files_max     |                   | 1024                       | INTEGER       |
| Rlimit_virtual_memory_cur |                   | 4294967295                 | INTEGER       |
| Rlimit_virtual_memory_max |                   | 4294967295                 | INTEGER       |
| Os_name                   |                   | Linux                      | VARCHAR       |
| Os_version                |                   | 2.6.9-22.ELsmp             | VARCHAR       |
| Os_architecture           |                   | i686                       | VARCHAR       |
| Os_machine                |                   | i686                       | VARCHAR       |
| Os_description            |                   | Red Hat Enterprise Linux 4 | VARCHAR       |
| Os_patch_level            |                   | unknown                    | VARCHAR       |
| Os_vendor                 |                   | Red Hat                    | VARCHAR       |
| Os_vendor_version         |                   | Enterprise Linux 4         | VARCHAR       |
| Os_vendor_name            |                   | Linux                      | VARCHAR       |
| Os_vendor_code_name       |                   | Nahant Update 2            | VARCHAR       |
+---------------------------+-------------------+----------------------------+---------------+
49 rows in set (0.01 sec)

mysql> select * from os_status;
+-------------------------+-------------------+----------------------+---------------+
| VARIABLE_NAME           | VARIABLE_INSTANCE | VARIABLE_VALUE       | VARIABLE_TYPE |
+-------------------------+-------------------+----------------------+---------------+
| Memory_total            |                   | 1059471360           | INTEGER       |
| Memory_used             |                   | 682172416            | INTEGER       |
| Memory_free             |                   | 377298944            | INTEGER       |
| Swap_total              |                   | 1002676224           | INTEGER       |
| Swap_used               |                   | 121491456            | INTEGER       |
| Swap_free               |                   | 881184768            | INTEGER       |
| Cpu_user_total          |                   | 695644430            | INTEGER       |
| Cpu_sys_total           |                   | 273173250            | INTEGER       |
| Cpu_nice_total          |                   | 14360                | INTEGER       |
| Cpu_idle_total          |                   | 46569287330          | INTEGER       |
| Cpu_wait_total          |                   | 809380330            | INTEGER       |
| Cpu_total_total         |                   | 48347499700          | INTEGER       |
| Cpu_user                | cpu0              | 695644430            | INTEGER       |
| Cpu_sys                 | cpu0              | 273173250            | INTEGER       |
| Cpu_nice                | cpu0              | 14350                | INTEGER       |
| Cpu_idle                | cpu0              | 46569287330          | INTEGER       |
| Cpu_wait                | cpu0              | 809380330            | INTEGER       |
| Cpu_total               | cpu0              | 48347499690          | INTEGER       |
| Load_average            | 0                 | 0.05                 | DECIMAL       |
| Load_average            | 1                 | 0.04                 | DECIMAL       |
| Load_average            | 2                 | 0.01                 | DECIMAL       |
| Fs_size                 | /                 | 9618148              | INTEGER       |
| Fs_free                 | /                 | 5002764              | INTEGER       |
| Fs_used                 | /                 | 4615384              | INTEGER       |
| Fs_avail                | /                 | 4514188              | INTEGER       |
| Fs_files                | /                 | 1221600              | INTEGER       |
| Fs_disk_reads           | /                 | 20785383             | INTEGER       |
| Fs_disk_read_bytes      | /                 | 176143946752         | INTEGER       |
| Fs_disk_writes          | /                 | 625659222            | INTEGER       |
| Fs_disk_write_bytes     | /                 | 363676917760         | INTEGER       |
| Fs_disk_queue           | /                 | 0                    | INTEGER       |
| Fs_size                 | /boot             | 98747                | INTEGER       |
| Fs_free                 | /boot             | 87464                | INTEGER       |
| Fs_used                 | /boot             | 11283                | INTEGER       |
| Fs_avail                | /boot             | 82365                | INTEGER       |
| Fs_files                | /boot             | 25584                | INTEGER       |
| Fs_disk_reads           | /boot             | 2053                 | INTEGER       |
| Fs_disk_read_bytes      | /boot             | 6922240              | INTEGER       |
| Fs_disk_writes          | /boot             | 350                  | INTEGER       |
| Fs_disk_write_bytes     | /boot             | 361472               | INTEGER       |
| Fs_disk_queue           | /boot             | 18446744073709551615 | INTEGER       |
| Fs_size                 | /data0            | 19228180             | INTEGER       |
| Fs_free                 | /data0            | 18804340             | INTEGER       |
| Fs_used                 | /data0            | 423840               | INTEGER       |
| Fs_avail                | /data0            | 17827596             | INTEGER       |
| Fs_files                | /data0            | 2443200              | INTEGER       |
| Fs_disk_reads           | /data0            | 13058321             | INTEGER       |
| Fs_disk_read_bytes      | /data0            | 485011117056         | INTEGER       |
| Fs_disk_writes          | /data0            | 68639632             | INTEGER       |
| Fs_disk_write_bytes     | /data0            | 281147932672         | INTEGER       |
| Fs_disk_queue           | /data0            | 0                    | INTEGER       |
| Fs_size                 | /users            | 373057344            | INTEGER       |
| Fs_free                 | /users            | 43121216             | INTEGER       |
| Fs_used                 | /users            | 329936128            | INTEGER       |
| Fs_avail                | /users            | 24170944             | INTEGER       |
| Fs_files                | /users            | 47382528             | INTEGER       |
| Fs_size                 | /nfstmp1          | 307583488            | INTEGER       |
| Fs_free                 | /nfstmp1          | 179642368            | INTEGER       |
| Fs_used                 | /nfstmp1          | 127941120            | INTEGER       |
| Fs_avail                | /nfstmp1          | 164017952            | INTEGER       |
| Fs_files                | /nfstmp1          | 39075840             | INTEGER       |
| Mysqld_threads          | mysqld            | 10                   | INTEGER       |
| Mysqld_mem_size         | mysqld            | 128045056            | INTEGER       |
| Mysqld_mem_resident     | mysqld            | 26636288             | INTEGER       |
| Mysqld_mem_share        | mysqld            | 5382144              | INTEGER       |
| Mysqld_mem_minor_faults | mysqld            | 8926                 | INTEGER       |
| Mysqld_mem_major_faults | mysqld            | 7                    | INTEGER       |
| Mysqld_mem_page_faults  | mysqld            | 8933                 | INTEGER       |
| Mysqld_cpu_user         | mysqld            | 440                  | INTEGER       |
| Mysqld_cpu_sys          | mysqld            | 180                  | INTEGER       |
| Mysqld_cpu_total        | mysqld            | 620                  | INTEGER       |
| Uptime                  |                   | 24173401.250000      | DECIMAL       |
+-------------------------+-------------------+----------------------+---------------+
72 rows in set (0.01 sec)

If you have multiple CPUs then you will have multiple returns for variables such as ‘Cpu_user’, ‘Cpu_nice’ – labelled cpu0, cpu1 etc. – just like there is with the ‘Fs*’ variables – specific to each CPU.

You can download a copy of the plugin code from here. Build and installation instructions are within the INSTALL file.

All feedback welcome!

MySQL 5.0 – INFORMATION_SCHEMA

Arjen Lentz (“Community Relations Manager” for MySQL AB) posted “The MySQL 5.0 Beta Challenge” on his blog today – so in the spirit of community I decided to give it a crack, by putting together some posts on the new features of MySQL 5.0 up here to my humble blog. Who knows – I may win a mug! Then I can have a list of ALL the people to bug over at MySQL AB!

I’m a DBA at heart, so whilst I’ll take a look at the new features that are available for developers, I’m going to start from the DBA tack, and take a look at INFORMATION_SCHEMA.

So what is the INFORMATION_SCHEMA? Those that have used other RDBMS’ in the past will recognize it as MySQL’s “Data Dictionary”. An RDBMS Data Dictionary is a collection of tables or views (that appear as tables), that utilize the database metadata to give easy access to information about the objects (tables, indexes, procedures, functions, views etc.) within the database, as well as information on things such security/privileges, performance statistics, auditing trails etc.

On installing MySQL 5.0, and issuing “SHOW DATABASES;” you will immediately see “information_schema” listed. The INFORMATION_SCHEMA is implemented to look like a database in it’s own right. However, it doesn’t conform with the conventional database privilege system. You can “USE” it, but you can’t run any type of DML against it. GRANT does seem to work against it, but attempting to run a DELETE results in an error.

mysql> use information_schema;
Database changed
mysql> show grants;
+------------------------------
| Grants for mark@localhost                                            |
+------------------------------
| GRANT USAGE ON *.* TO 'mark'@'localhost'                             |
| GRANT SELECT, DELETE ON `information_schema`.* TO 'mark'@'localhost' |
+------------------------------
2 rows in set (0.00 sec)

mysql> delete from information_schema.tables;
ERROR 1288 (HY000): The target table tables of the DELETE is not updatable

“What good are they to me?”

Well, one question I’ve seen a lot around IRC is “Can I use the output of a SHOW command in a statement, such as a subquery?”. This mostly comes down to something to do with SHOW TABLES – getting databases sizes, table sizes etc. SHOW TABLES still works in 5.0, as do all other SHOW commands, however, INFORMATION_SCHEMA.TABLES now also parses the exact same output. Think of it of as view (more on these later) against the SHOW TABLES command. The great thing about this is we can now use conventional SQL to use these values – we no longer have to write external scripts to do what SUM() can do, for example. So here’s one for those guys on IRC that want to find out how big their databases are from SQL:

SELECT s.schema_name,
       CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),'Mb') total_size,
       CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),'Mb') data_used,
       CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),'Mb') data_free,
       IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
       COUNT(table_name) total_tables
  FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
 WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name
ORDER BY pct_used DESCG

*****************1. row **************
 schema_name: test
  total_size: 0.06Mb
   data_used: 0.06Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 2
*****************2. row **************
 schema_name: flightstats
  total_size: 2950.14Mb
   data_used: 2949.71Mb
   data_free: 0.43Mb
    pct_used: 99.99
total_tables: 81
...

So now we can get the information we want, in the format we want. We can also JOIN the INFORMATION_SCHEMA tables to create better reports – such as above, if there are no actual tables in a database, they won’t be shown within INFORMATION_SCHEMA.TABLES, so to get a full report on all databases, we LEFT JOIN from INFORMATION_SCHEMA.SCHEMATA.

This also leads us in to getting helpful information on any object optimizations easily. Perhaps you have performed an archive of a lot of data, and want to reclaim freespace. You want to find all tables with > 40% of the datafile free, so that you can perform an OPTIMIZE TABLE against them:

SELECT table_name,
       ROUND(((t.data_length+t.index_length)-t.data_free)/(t.data_length+t.index_length)*100) pct_used
  FROM information_schema.tables t
 WHERE table_schema = 'flightstats'
HAVING pct_used < 60
ORDER BY table_name;</blockquote>

Let’s take this one step further – now we can actually use MySQL to generate the scripts that we’ve had to use an external scripting language to achieve. Let’s focus on another question often asked on IRC – “How do I drop all my tables, without dropping my database?” – previous to MySQL 5.0 the answer is “get table_name from the output of SHOW TABLES, and loop through this with your scripting language of choice, running a DROP TABLE command for each one”.

Now let’s use the INFORMATION_SCHEMA, and SQL to do the job for us:

SELECT CONCAT('DROP TABLE ',table_name,';')
  INTO OUTFILE '/sql/drop_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'test';

Now all we have to do is “SOURCE /sql/drop_tables.sql;” and all of the tables in the test database will be gone, and we didn’t have to leave the mysql command line interface! Don’t try this on your production schemas kids!

Or to go back to our OPTIMIZE TABLE:

SELECT CONCAT('OPTIMIZE TABLE ',table_name,';')
  INTO OUTFILE '/sql/optimize_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'flightstats'
AND ROUND(((data_length+index_length)-data_free)/(data_length+index_length)*100) < 60;<

This makes life a lot easier! There are a number of other INFORMATION_SCHEMA tables, but I won’t go in to all of those now. We can use these to get information on grants, build grant scripts, get information about indexes, columns, foreign keys etc. For instance – it’s probably possible now to perform “fuzzy” foreign key matches – searching for columns that are named the same within different tables, that may not necessarily be the InnoDB engine. As I put together more scripts in readiness for 5.0, I’ll post them here, and to the other site that I run – SQLDump.co.uk (which I haven’t really started filling yet!! But it’s wiki! ;))

It should be noted that all users have access to the INFORMATION_SCHEMA. They will however only be able to see the objects for which they have some privileges on.