MYSQL Server Monitor

[Ravinos]

Has anyone had any experience with MYSQL monitors? I know there are a few out there but I'm looking for first hand experiences to see what everyone preferes. Are there any that plug right into WAMP? I'd like to be able to see data transfer rates, queries per minute/second, and cpu usage.

I would like to see how much stress is being put on the database. I'm running what I call a "Master Script" that makes sure there is no funny business going on and it does all of the heavy calculations as far as totaling, summing, and counting items that need constant updates. I have another large script that runs that keeps all of my refresh timings for dynamic content going.

The clock script runs all the time and the "Master Script" runs whenever a page is refreshed by the clock.

I personally haven't noticed any issues with these running but I'm the only one using it right now until it hits alpha. The only thing I've been able to see which I doubt is accurate is that I've opened the task manager in windows and spammed f5 on the browser to see if MYSQL spiked. Idle CPU usage is 00 and spamming brings it up to 02. By spamming I mean pressing f5 every 1/4 of a second using a macro.

[hallsofvallhalla]

hmm i never tried one but its a good idea.

[Jackolantern]

I could be wrong, but I thought there were diagnostic tools already built into the MySQL console that you could view?

[Ravinos]

Yeah it has one built in but I'd like to have a real time monitor. The one in phpmyadmin only refreshes when the page does.

Just a quick post on my games test page sitting idle for 2 minutes.

Traffic 1 ø per hour
Received 820 KiB 24 MiB
Sent 19 MiB 571 MiB
Total 20 MiB 594 MiB

14,118 queries

that seems like a lot of data for two minutes at idle. Anyone else check theirs recently? lol

I may have to tone down the Scripts a bit lol. As a test tomorrow while I'm developing the game I'll post my stats after 8 hours of having it run.

[Jackolantern]

Say what? 14k queries for 2 minutes idle, as in...with no players logged in? That is crazy

[Ravinos]

that was with me logged in sitting idle at the games mainpage without me doing anything but letting everything that auto refreshes to do their thing.

I spent last night streamlining the queries and my timing script. I cut the idle page from 14k queries to 500 over two minutes. 500 is with me actually "playing" the game normally. I'm sure I can get it lower and still maintain the streaming visual updates.

[Jackolantern]

500 is still a tad high for not having anything going on. What do you need so many scripts running for? At the very least, you should probably be planning out your game now to easily take advantage of a separate server running your MySQL database so you have room to grow. If you suddenly got 1000 players on your game overnight, you would already be nearly 5 queries a second in the hole just to support your world, and that would be harsh once players are adding hundreds more.

EDIT: Really everyone would benefit from writing their database routines to easily move the MySQL db to another server. If you have a player explosion, you only have one chance to get it right and to grow to support them or they will go elsewhere.

[Ravinos]

Exactly. That is the really the reason why I started working on optimizing the database and my scripts. I modified my control script a bit to make it less stress full and now it just runs once on login/logout and then once every hour. It's designed to make sure everything stays in sync for now. I'll be dumbing it down a bit more soon.


Stats after letting the server run an hour with 1 idle player logged in. Duration 2 hours 30 minutes

143,378 queries

Data Traffic 1 ø per hour
Received 6,250 KiB 2,442 KiB
Sent 171 MiB 67 MiB
Total 177 MiB 69 MiB

it is a huge improvement and i still have a ways to go on streamlining.

[hallsofvallhalla]

143k queries? something is wrong....You need more than streamlining. That's 40 queries a second.

[Ravinos]

It's now down to 8 queries a second at idle due to the autorefreshes.

I had a page running the original version of my Update script that had about 70 queries in it. It was running every 3 seconds.....oops lol