More fun visualizing MySQL Database Size

[Latest News Headlines]

Hi again!

As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like this:http://chart.apis.google.com/chart?c...000,1,-1,11The graph looks like this: [IMG]http://chart.apis.google.com/chart?cht=bhs&chbh=19,2,2&chs=653x459&chtt=sakila%20Size%20(MB)&chco=4D89F9,C6D9FD&chd=t:0.0156,0.0156,0.0156,0.0156,0.0156,0.0469,0. 0625,0.0625,0.0781,0.0781,0.1875,0.1875,0.1875,0.1 163,0.1719,1.5156,1.5156|0.0000,0.0000,0.0000,0.01 56,0.0313,0.0156,0.0156,0.0313,0.0156,0.0469,0.078 1,0.0781,0.0781,0.2002,0.1875,0.6094,1.2031&chds=0,2.7188&chxt=y,x&chxl=0:|rental%20(InnoDB)|payment%20(InnoDB)|inven tory%20(InnoDB)|film_text%20(MyISAM)|films%20(Inno DB)|film_actor%20(InnoDB)|film%20(InnoDB)|customer %20(InnoDB)|staff%20(InnoDB)|address%20(InnoDB)|fi lm_category%20(InnoDB)|city%20(InnoDB)|store%20(In noDB)|actor%20(InnoDB)|language%20(InnoDB)|country %20(InnoDB)|category%20(InnoDB)|1:|0|2.72MB&chm=N*f2*,000000,0,-1,11|N*f2*,000000,1,-1,11[/IMG]Here's the script I used:SET @maxpixels:=300000;
SET @barwidth:=19;
SET @spacebetweenbars:=2;
SET @spacebetweengroups:=2;
SET @totalbarwidth:=@barwidth+(2*@spacebetweenbars)+(2 *@spacebetweengroups);
SET @megabytes:=1024*1024;
SET @decimals:=2;

SELECT CONCAT(
'http://chart.apis.google.com/chart'
, '?cht=bhs'
, '&chbh=',@barwidth,',',@spacebetweenbars,',',@spaceb etweengroups
, '&chs=', @maxpixels div (COUNT(*) * @totalbarwidth),'x', COUNT(*) * @totalbarwidth
, '&chtt=', table_schema, ' Size (MB)'
, '&chco=4D89F9,C6D9FD'
, '&chd=t:', GROUP_CONCAT(data_length / @megabytes ORDER BY (data_length+index_length))
, '|', GROUP_CONCAT(index_length / @megabytes ORDER BY (data_length+index_length))
, '&chds=' ,0, ',', MAX(data_length+index_length)/@megabytes
, '&chxt=y,x'
, '&chxl=0:|', GROUP_CONCAT(table_name, ' (', engine,')' ORDER BY (data_length + index_length) DESC separator '|')
, '|1:|', 0, '|', ROUND(MAX(data_length+index_length) / @megabytes, @decimals), 'MB'
, '&chm=N*f',@decimals,'*,000000,0,-1,11|N*f',@decimals,'*,000000,1,-1,11'
)
FROM information_schema.tables
WHERE table_schema = SCHEMA()
AND table_type = 'BASE TABLE'
GROUP BY table_schemaI'm not really satisfied yet...I keep hitting limitations w/re to google charts. I built a little bit of logic that will ensure the resulting picture is within the upper limit of 300000 pixels. I just found out there is another limitation that says the chart height can't exceed 1000 pixels. I'm going to stop looking at this for a while, and maybe later on I will come up with some works-most-of-the-time kind of logic to control this.

Feel free to drop me a line if you have some ideas regarding this.


More from Roland Bouman's Blog ...