Quote:
| Originally Posted by Dan_T Does anybody know if there is a way (or tool) which allows me to generate stats on the usage of a database, tables etc per requesting client? (not the usual stuff like show status, show innodb status etc etc) but real stat details per table
|
This may be a bit late! But there is an open source add
on to MySQL which provides pretty comprehensive usage reporting
by user, db, and connecting host -- including historical info
and configurable by the DBA. It's called ExtSQL.
I work at a Web Hosting company and we were frustrated by
our inability to account for user activity on a busy database.
We could see gross spikes in activity by SHOW STATUS, but couldn't
tell where they were coming from in an easy manner. We finally
developed an extensive set of patches of MySQL and released them
as Open Source (we hope it will get picked up in the main MySQL
soon!). I have some brief examples below, you can read more and
download the source at ExtSQL.Com
Code:
sql> SHOW STATISTICS Com_select, Com_insert, Questions FROM user;
+----------+-------------+------------+------------+
| user | Com_select | Com_insert | Questions |
+----------+-------------+------------+------------+
| bandala | 8302675 | 95973 | 23153940 |
| sandymao | 1702812 | 6205 | 3829023 |
| ponnetli | 24909 | 4784 | 95646 |
Show number of select, updates, and total queries issued by all client
hosts which have connected to this server:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM host;
+-------------------+--------------+----------+--------------+
| host | Com_select | Questions | Com_update |
+-------------------+--------------+----------+--------------+
| db2.adomain.com | 17715223 | 44224076 | 4143634981 |
| lathe.adomain.com | 2738061 | 9743215 | 3913397495 |
| telkomadsl.co.za | 195 | 5390 | 539604 |
Show number of select, updates, and total queries from all
users@client host machines where more than 10,000 queries were issued:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM conuser WHERE Questions > 10000;
+----------------------------+-------------+-----------+------------+
| conuser (user@host) | Com_select | Questions | Com_update |
+--------- ------------------+-------------+-----------+------------+
| bandala#db2.adomain.com | 8306726 | 23163320 | 3439850933 |
| sandymao#db2.adomain.com | 1704040 | 3831803 | 3365501841 |
| ponnetli#lathe.adomain.com | 24920 | 95662 | 156529077 |
Show number of select, updates, and total queries for DB bandala for
the past three minutes:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM db LIKE 'bandala' HISTORY LIMIT 3;
+---------+-------------+------------+-----------+------------+
| db | minutes | Com_select | Questions | Com_update |
+---------+-------------+------------+-----------+------------+
| bandala | 11/20 13:56 | 216 | 382 | 318343 |
| bandala | 11/20 13:55 | 642 | 1618 | 1386347 |
| bandala | 11/20 13:54 | 280 | 699 | 646855 |
Dev Articles Community Forums Sponsor:
July 17th, 2005, 04:10 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
