Als je regelmatig in aanraking komt met MySQL omgevingen dan zal je wel weten dat er heel veel informatie terug te vinden is in de database engine. Dat kan gaan van informatie over de data zelf, het resource verbruik van je MySQL instance als over de structuur van je data. Op deze pagina kan je enkele oneliners en scripts terugvinden waar ik zelf regelmatig op terugval indien ik bepaalde informatie wil onttrekken uit een MySQL instance.

Grootte van alle databases

SELECT table_schema "Data Base Name",  sum( data_length + index_length ) / 1024 /  1024 "Data Base Size in MB" 
FROM information_schema.TABLES
GROUP BY table_schema;

Grootte van alle tables in een database

Vervang in onderstaande code schema_name door de naam van de database waarvan je de grootte van de tables wil weergeven.

SELECT TABLE_NAME, table_rows, data_length, index_length, 
round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM information_schema.TABLES WHERE table_schema = "schema_name"
ORDER BY (data_length + index_length) ASC;

Aantal statements per type

Let op, deze waardes worden terug op 0 gezet na een herstart van MySQL of het uitvoeren van een FLUSH STATUS.

SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert'
OR Variable_name = 'Com_update'
OR Variable_name = 'Com_select'
OR Variable_name = 'Com_delete';

Indexes weergeven per table in een database

Vervang in deze code de term database door de concrete naam van de database waarover je de informatie wil weten.

for i in `mysql -sNe 'use database; show tables;'`; do mysql -Ne "show index from database.${i};"; done

Tables zonder primary key

Is onderstaande informatie te uitgebreid dan kan je de range verkleinen door een database te specifiëren. Haal hiervoor de regel die begint met “–” uit comment en vervang de term database door de naam van de database waarover je de informatie wil weten.

select tab.table_schema as database_name,
       tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
      and tab.table_schema not in('mysql', 'information_schema', 
                                  'performance_schema', 'sys')
      and tab.table_type = 'BASE TABLE'
--    and tab.table_schema = 'database'
order by tab.table_schema,
         tab.table_name;

Optimale waarde voor InnoDB buffer pool size berekenen

Het definiëren van de correcte waarde voor de InnoDB buffer pool size kan een grote impact hebben op de performantie van je MySQL omgeving. Via onderstaande query kan je de optimale waarde voor deze parameter berekenen. Let wel op dat het hier gaat om een louter theoretische waarde.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Advies

Wat je hierboven terugvindt is slechts een kleine set aan commando’s en queries die je tijdens je dagelijkse werking met MySQL kan hanteren. We kunnen hier uiteraard op aanvraag veel verder en dieper op ingaan.
Heb je nood aan wat advies over MySQL of zit je met een probleem waar je hulp voor kan gebruiken dan moet je niet twijfelen en zeker eens contact opnemen.