×

Welcome to TagMyCode

Please login or create account to add a snippet.
0
0
 
0
Language: SQL
Posted by: Roman Ignatov
Added: Nov 30, 2016 11:11 AM
Views: 26
Tags: mysql sysadmin
  1. -- During the course of my daily work I occasionally search for mysql queries which are cool and helpful. I once found the following query on http://forge.mysql.com/:
  2.  
  3. SELECT TABLE_NAME article_attachment,
  4. engine,
  5. ROUND(data_length/1024/1024,2) total_size_mb,
  6. ROUND(index_length/1024/1024,2) total_index_size_mb,
  7. table_rows
  8. FROM information_schema.tables
  9. WHERE table_schema = 'MYDBNAME'
  10. ORDER BY 3 DESC LIMIT 20;
  11.  
  12. -- Finding largest tables on MySQL instance is no brainier in MySQL 5.0+
  13. -- thanks to Information Schema but I still wanted to post little query
  14. -- I use for the purpose so I can easily find it later,
  15. -- plus it is quite handy in a way it presents information
  16. SELECT
  17.   CONCAT(table_schema, '.', TABLE_NAME),
  18.   CONCAT(ROUND(table_rows / 1000000, 2), 'M') ROWS,
  19.   CONCAT(
  20.     ROUND(data_length / (1024 * 1024 * 1024), 2),
  21.     'G'
  22.   ) DATA,
  23.   CONCAT(
  24.     ROUND(index_length / (1024 * 1024 * 1024), 2),
  25.     'G'
  26.   ) idx,
  27.   CONCAT(
  28.     ROUND(
  29.       (data_length + index_length) / (1024 * 1024 * 1024),
  30.       2
  31.     ),
  32.     'G'
  33.   ) total_size,
  34.   ROUND(index_length / data_length, 2) idxfrac
  35. FROM
  36.   information_schema.TABLES
  37. ORDER BY data_length + index_length DESC
  38. LIMIT 10 ;
  39.  
  40.  
  41. -- Find total number of tables, rows, total data in index size for given MySQL Instance
  42. SELECT COUNT(*) TABLES,
  43.         CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') ROWS,
  44.         CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
  45.     CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') idx,
  46.     CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  47.     ROUND(SUM(index_length)/SUM(data_length),2) idxfrac
  48.     FROM information_schema.TABLES
  49.     WHERE TABLE_NAME LIKE "%performance_log%";
  50.  
  51.  
  52. -- Find the same data using some filter
  53. -- I often use similar queries to find space used by particular table "type" in sharded
  54. -- environment when multiple tables with same structure and similar name exists:
  55. SELECT COUNT(*) TABLES,
  56.         concat(round(SUM(table_rows)/1000000,2),'M') ROWS,
  57.         concat(round(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
  58.     concat(round(SUM(index_length)/(1024*1024*1024),2),'G') idx,
  59.     concat(round(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  60.     round(SUM(index_length)/SUM(data_length),2) idxfrac
  61.     FROM information_schema.TABLES;
  62.  
  63. -- OR also
  64. SELECT COUNT(*) TABLES,
  65.         concat(round(SUM(table_rows)/1000000,2),'M') ROWS,
  66.         concat(round(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
  67.     concat(round(SUM(index_length)/(1024*1024*1024),2),'G') idx,
  68.     concat(round(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  69.     round(SUM(index_length)/SUM(data_length),2) idxfrac
  70.     FROM information_schema.TABLES
  71.     WHERE TABLE_NAME LIKE "%performance_log%";
  72.  
  73.  
  74. -- Find biggest databases
  75. SELECT
  76.         COUNT(*) TABLES,
  77.         table_schema,concat(round(SUM(table_rows)/1000000,2),'M') ROWS,
  78.         concat(round(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
  79.     concat(round(SUM(index_length)/(1024*1024*1024),2),'G') idx,
  80.     concat(round(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  81.     round(SUM(index_length)/SUM(data_length),2) idxfrac
  82.     FROM information_schema.TABLES
  83.     GROUP BY table_schema
  84.     ORDER BY SUM(data_length+index_length) DESC LIMIT 10;
  85.  
  86.  
  87.  
  88. -- Data Distribution by Storage Engines
  89. SELECT engine,
  90.         COUNT(*) TABLES,
  91.         concat(round(SUM(table_rows)/1000000,2),'M') ROWS,
  92.         concat(round(SUM(data_length)/(1024*1024*1024),2),'G') DATA,
  93.     concat(round(SUM(index_length)/(1024*1024*1024),2),'G') idx,
  94.     concat(round(SUM(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
  95.     round(SUM(index_length)/SUM(data_length),2) idxfrac
  96.     FROM information_schema.TABLES
  97.     GROUP BY engine
  98.     ORDER BY SUM(data_length+index_length) DESC LIMIT 10;
  99.  
  100.  
  101.  
  102.  
  103.