Folks,
I work at a Web Hosting company and we were frustrated by our
inability to account for user activity on a busy MySQL server (sound
familiar?).
We could see gross spikes in activity by SHOW STATUS, but couldn't tell
where they were coming from in an easy manner. We modified MySQL
so our DBAs could account for activity by user, db, and connecting
host (including historical info). It made our lives a lot easier. We love it!
We decided to better document our changes and developed an extensive
set of patches for MySQL and recently released them as Open Source (we
hope it will get picked up in the main MySQL distribution soon!) as ExtSQL.
You can find the source at ExtSQL.Com
As a couple of examples below show -- it became easy for us to track
high usage patterns. We were surprised to see that a lot of activity
was coming from web sites we had never expected to be 'high users'.
Show number of select, updates, and total queries from all
users@client host machines where more than 10,000 queries were issued:
Show number of select, updates, and total queries for DB bandala for
the past three minutes:
Hope you like it!
John Murtari
MOD NOTE: Signatures must be added into a designated space in user control panel and not manually added to posts.
I work at a Web Hosting company and we were frustrated by our
inability to account for user activity on a busy MySQL server (sound
familiar?).
We could see gross spikes in activity by SHOW STATUS, but couldn't tell
where they were coming from in an easy manner. We modified MySQL
so our DBAs could account for activity by user, db, and connecting
host (including historical info). It made our lives a lot easier. We love it!
We decided to better document our changes and developed an extensive
set of patches for MySQL and recently released them as Open Source (we
hope it will get picked up in the main MySQL distribution soon!) as ExtSQL.
You can find the source at ExtSQL.Com
As a couple of examples below show -- it became easy for us to track
high usage patterns. We were surprised to see that a lot of activity
was coming from web sites we had never expected to be 'high users'.
Show number of select, updates, and total queries from all
users@client host machines where more than 10,000 queries were issued:
Code:
sql> SHOW STATISTICS Com_select, Questions, Com_update FROM conuser
WHERE Questions > 10000;
+----------------------------+-------------+-----------+------------+
| conuser (user@host) | Com_select | Questions | Com_update |
+--------- ------------------+-------------+-----------+------------+
| [email]bandala@db2.adomain.com[/email] | 8306726 | 23163320 | 3439850933 |
| [email]sandymao@db2.adomain.com[/email] | 1704040 | 3831803 | 3365501841 |
| [email]ponnetli@lathe.adomain.com[/email] | 24920 | 95662 | 156529077 |
|Show number of select, updates, and total queries for DB bandala for
the past three minutes:
Code:
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 |John Murtari
MOD NOTE: Signatures must be added into a designated space in user control panel and not manually added to posts.
