PDA

View Full Version : verify mysql usage ?

locos
4-1-03, 11:30 AM
is there a way in the ops for us to check our mysql usage ? it only seems fair that if we are faced with these limitations, that we be able to view how close we are to the cap.
Xinil
4-1-03, 05:22 PM
fair? Since when was life fair? :)
Fred Drouven
4-1-03, 06:20 PM
Maybe you can build a script which runs every hour and ask out the status using

$status = explode(' ', mysql_stat($db));
print_r($status);

Where $db is your mysql_connect(database,username,password)

This function is available since php 4.3.0
omatic
4-1-03, 06:58 PM
Fred, I tried what you suggested, and I did get back results. However, they appear to be server-wide (not database-specific). My database is currently unused, yet reports 6.7 million questions, 78.486 queries per second average, etc.

I, too, would love to have some statistics about how much MySQL I'm using. Unfortunately this doesn't appear to be how to get 'em... :(

Regards,
omatic
Fred Drouven
4-1-03, 07:02 PM
You could be right. When I run it I get the following results:

Array ( [0] => Uptime: 119748 [1] => Threads: 1 [2] => Questions: 621307 [3] => Slow queries: 3 [4] => Opens: 23379 [5] => Flush tables: 34 [6] => Open tables: 512 [7] => Queries per second avg: 5.188 )

We'll have to sort it out
omatic
4-1-03, 08:09 PM
Fred, did you get your stats off of a Powweb mySQL server, or a local one?

By the way, it's not pretty (didn't work on formatting it at all) but here's my results. I was connecting to my database on mysql01.powweb.com to get this:

Array ( [0] => Uptime: [1] => 90578 [2] => [3] => Threads: [4] => 2 [5] => [6] => Questions: [7] => 7122440 [8] => [9] => Slow [10] => queries: [11] => 15 [12] => [13] => Opens: [14] => 202500 [15] => [16] => Flush [17] => tables: [18] => 26 [19] => [20] => Open [21] => tables: [22] => 1024 [23] => [24] => Queries [25] => per [26] => second [27] => avg: [28] => 78.633 )

Yucko. Sorry for the formatting mess. I'm gonna try to tweak it real quick-like (but the girlfriend is on her way over... and I'm no PHP whiz... so no promises ;-)

omatic
omatic
4-1-03, 08:33 PM
Here... this is a bit more readable:
----------------
Uptime: 92253
Threads: 4
Questions: 7220636
Slow queries: 15
Opens: 206357
Flush tables: 27
Open tables: 1024
Queries per second avg: 78.270
----------------And here's the script for anybody who wants to try it out:
<HTML><TITLE>MySQL Stats:</TITLE><BODY>----------------<BR>
<?PHP
$db = mysql_connect("mysql_nn.powweb.com", "yourmysqlusernamehere","yourpasswordhere");
mysql_select_db("yourdbnamehere",$db);
$status = explode(' ',mysql_stat($db));
foreach($status as $stat) {
&nbsp;&nbsp;printf("%s ", $stat);
&nbsp;&nbsp;if ($stat=='') printf("<BR>\n");
}
?>
<BR>----------------</BODY></HTML>

Girlfriend's just gotten here, catch you later....!
omatic