I need a program to monitor MySQL server

Hi:

I need to know is there is any program that could help me with a problem.

I have a server for apache and another server with direct link connection with the apache server for MySQL, sin 3 days i have seen on my munin graphs that eth1 traffic (connection between apache server and MySQL server is too hight, more than 2 or 3 times normal traffic).

What i would like to know if there is a software that let me know wich user is using more connections,executing more queries, etc.

I need something to detect who's site is making server going slow than normal.

Thanks.

 

Are you using a reseller?

 

No
They are dedicated servers.
I have full access.

 

I have not tested this, but this should do what you need. It will email you the date and time that mysql connect attempts fail. Set it to run in a cron job to run every 10 minutes.

This is the optimal way to check MySQL status. You can also add logging to a text file.

Your script, let me know if it doesn't work, I did not test it but should be ok:

<?php

	$sendEmailFrom = 'info@mysite.com';
	$sendEmailTo = 'you@yoursite.com';
	$dbname = 'xxx';
	$dbuser = 'xxx';
	$dbpass = 'xxx';
	$dbhost = 'localhost';
	$subject = 'Cannot connect to mysql!';
	$message = 'Cannot connect to mysql at ' . date('F j, Y, g:i a T') . '.';


	/*
		Stop Editing!
	*/
	$conn = mysql_connect($dbhost, $dbuser, $dbpass) or exit(mysql_error());
	mysql_select_db($dbname, $conn) or exit(mysql_error());
	if (!$conn) {
		$headers = "From: ".$sendEmailFrom."\r\n" . 
		        'X-Mailer: PHP/' . phpversion() . "\r\n" . 
		        "MIME-Version: 1.0\r\n" . 
		        "Content-Type: text/html; charset=utf-8\r\n" . 
		        "Content-Transfer-Encoding: 8bit\r\n\r\n"; 
		
		// Send 
		mail($sendEmailTo, $subject, $message, $headers); 

	}
?>

PHP:

 

Sorry but this is not what i'm searching for somthing to test which user is using more resources, with more select, update queries, etc not to test if reached the connections limit.

 

My bad... sorry haven't had my coffee yet

I need to know:

1. What operating system are you using?
2. Does the server have a control panel like cPanel, Plesk, Ensim, etc?

I would recommend hosting on a cPanel enabled server. The WHM panel offers outstanding tools for monitoring what you need without having to type commands.

 

CentOS, and yes, i use Cpanel.

 

Outstanding, that is good!

In your WHM these links will help you.

"Process Manager" - Shows you what is going on with RAM/CPU instantly

"Daily Process Log" - Shows which users are using what resources daily

"View Bandwidth Usage" - Shows how much bandwidth each user is using.

Make sure to run your Apache with SUExec and PHP with SUPHP. Then you can track each user's exact server usage for RAM and CPU.

If all fails, you can login to their cPanel using your root password and check their stats there. In awstats you can see which files they have being downloaded the most, like streaming video.

For web hosting I recommend at least a 100Mb/s pipe too.

 

I have awstats disabled and problem is not in streaming files, because eth1 is a direct connection between apache server and MySQL server, internet connection is eth0 and didn't experienced any more traffic than normal.

 

You have 2 separate dedicated servers one with apache and one with MySQL?

Sounds like you have persistant links that are not closing or your my.cnf config file needs adjusted.

You should enable awstats, a lot more accurate than webalizer. Webalizer is lame.

 

Yes, its explained on first post.

 

Yeah I can read. I am simply verifying because it is an odd setup for someone that does not know how to administer Linux.

 

you can check the process using TOP and you can try and optimize mysql and the my.conf file by using the mysqltuner.pl from mysqltuner.org

 

I have done that too much times, what i need is to monitor users that use much resources of mysql, not optimize more mysql.

 

There are several paid and open source monitoring applications available. e.g. MySQL Monitor, Webyog, mtop, MySQL Performance Monitor etc.

Queries like 'SELECT * ...' use to consume a lot of network resources, see if you have not recently added such queries. Similarly see if you are not using mysql_num_rows() to count number of rows on huge result set (use SELECT COUNT(*)... instead).

 

I will test one of that programs, and i'm going to see what you say about select queries and mysql_num_rows

Thanks

 

You are welcome.

 

Enable MySQL general query log for 20-30 minutes (don't enable it for longer time, it has a performance hit) and parse that log which mysql users are using much of MySQL resources.

 

I solved this years ago thanks