Get Paid to Participate - up to $1 per post!     Twitter     Facebook     Google+
Hosting Discussion
 

Hosting Discussion > Web Hosting Forums > Essential Software & Control Panels > Monitoring MySQL server usage by user/db.
forgot password?



Reply


Old
  Post #1 (permalink)   10-20-2008, 08:35 AM
HD Newbie
 
Join Date: Oct 2008
Location: Lyons, NY
Posts: 1

Status: jmurtari is offline
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:

Code:
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:

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     |
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.
 
 
 


Old
  Post #2 (permalink)   12-15-2008, 02:29 PM
HD Addict
 
Join Date: Dec 2008
Location: Florida,Tampa
Posts: 101
Send a message via MSN to HivelocityLB

Status: HivelocityLB is offline
Pretty neat. Makes managing your mySQL service a lot easier.
I like the fact that you are storing the SELECT and UPDATE queries separately - allowing you to determine who you top mySQL users are.

Thumbs up
__________________
Dedicated Servers - sales@hivelocity.net - 1-888-869-HOST(4678)
Viva Hivelocity "THE SERVER STUD" - Award Winning Hosting
Managed Dedicated Servers. Reseller Discounts. 24/7 Impressive Tech Support.
 
 
 


Old
  Post #3 (permalink)   12-16-2008, 12:54 AM
HD Amateur
 
Join Date: Oct 2008
Posts: 66

Status: hzSari is offline
Nice one , pretty simple too.
__________________
hzSari | HostingZoom.com
ModVPS.com | ResellerZoom.com
Power Speed Reliability
 
 
 
Reply

Thread Tools

New Post New Post   Old Post Old Post
Posting Rules:
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On
Sponsored By: