Posted by: Administrator
on March 02, 2006 03:03 AM
On the 1st December, 2004, MySQL AB released a new version of MySQL Administrator (version 1.0.17). I had not previously used this tool, but with some time on my hands and the work crisis averted thanks to the installation of our new database server, I thought I would see how it shapes up. Since we use PHP as a scripting language, we have always used phpMyAdmin, but the web interface can be frustratingly slow. MySQL Administrator is installed on your client, and so it responds quickly. It can be installed on a Linux machine with a graphical desktop, or Windows (NT, 2000, XP, or 2003 only). This month I give a quick tour, see how you could use it, and highlight some of the places where you can find useful data to help optimize your databases.
Features
Once installed, open MySQL Administrator and it will ask for a connection. Enter your login details (the server can be remote or local - connecting remotely disables some of the features, though many will not be running GTK on their lean and mean database servers, so local installation may not be an option). Your server connection can be saved under Tools/Save current connection. Once connected (and you can connect to multiple servers at the same time), navigation is simple - there is a sidebar with various main sections, and options within these. These are the sidebar options.
* Server Information: High-level overview with information about the user, host, port, MySQL client and server versions, and general client and server information.
* Service Control: Only available when you are connecting to a database server on the local host, this allows you to start and stop the MySQL server, and configure the MySQL service (on Windows).
* Startup Variables: Also only available when connected to localhost, this allows you to set startup variables for the MySQL server.
* User Administration: An easier way to administer privileges, you can fully control user access to the MySQL server in this section.
* Server Connections: Useful when the server is taking strain, here you can list the threads, as well as see how many instances of each user is connected to the server. You can also kill individual threads or all connections from a user.
* Health: In-depth data about your connections, memory usage, status variables and system variables.
* Server Logs: If connected to localhost, you can view entries from the error, slow and general logs
* Replication Status: View information about replicated slaves and masters.
* Backup: Backup, or schedule backups
* Restore: Restore backed up databases.
* Catalogs: Information about databases, as well as the ability to create and edit tables and perform routine maintenance, such as optimizing and checking.
Server Health
Most administrators will find the health feature particularly useful, so let's start with that one. At first you'll be presented with the connection health, which consists of three graphs: connections (a graphic of the percentage of total available connections used, as well as figures for the current, minimum, maximum and average number of connections), traffic (a graphic of kB being sent, as well as current, minimum, maximum and average kB sent) and number of SQL queries (again a graphic of the number of queries, and figures for the current, minimum, maximum and average number of queries).
So what does this tell us? I am going to use a live server as an example, with the data taken from off-peak hours. Let's look at a screenshot.
Firstly, the number of connections is not a problem. It may be off-peaks, but the maximum is still way below the limit. The database server has recently been upgraded, and the previous maximum connection limit has been maintained. Because the server processes the queries much more quickly now, the connections have not been building up nearly as much.
Secondly, the bytes sent are running at about one sixth of the maximum, but slightly above average, as one would expect from an off-peak server (remembering that excessive highs or lows skew averages).
Finally, the number of queries running is quite low, again corresponding to the time of day. The main probable call to action from this screen would be if the connection usage were approaching 100%, indicating one should up the max_connections in the config file.
Learn about Linux
Download Linux
Get Linux help
ravindra mudumby
Posted by: Administrator on March 02, 2006 03:03 AMFeatures
Once installed, open MySQL Administrator and it will ask for a connection. Enter your login details (the server can be remote or local - connecting remotely disables some of the features, though many will not be running GTK on their lean and mean database servers, so local installation may not be an option). Your server connection can be saved under Tools/Save current connection. Once connected (and you can connect to multiple servers at the same time), navigation is simple - there is a sidebar with various main sections, and options within these. These are the sidebar options.
* Server Information: High-level overview with information about the user, host, port, MySQL client and server versions, and general client and server information.
* Service Control: Only available when you are connecting to a database server on the local host, this allows you to start and stop the MySQL server, and configure the MySQL service (on Windows).
* Startup Variables: Also only available when connected to localhost, this allows you to set startup variables for the MySQL server.
* User Administration: An easier way to administer privileges, you can fully control user access to the MySQL server in this section.
* Server Connections: Useful when the server is taking strain, here you can list the threads, as well as see how many instances of each user is connected to the server. You can also kill individual threads or all connections from a user.
* Health: In-depth data about your connections, memory usage, status variables and system variables.
* Server Logs: If connected to localhost, you can view entries from the error, slow and general logs
* Replication Status: View information about replicated slaves and masters.
* Backup: Backup, or schedule backups
* Restore: Restore backed up databases.
* Catalogs: Information about databases, as well as the ability to create and edit tables and perform routine maintenance, such as optimizing and checking.
Server Health
Most administrators will find the health feature particularly useful, so let's start with that one. At first you'll be presented with the connection health, which consists of three graphs: connections (a graphic of the percentage of total available connections used, as well as figures for the current, minimum, maximum and average number of connections), traffic (a graphic of kB being sent, as well as current, minimum, maximum and average kB sent) and number of SQL queries (again a graphic of the number of queries, and figures for the current, minimum, maximum and average number of queries).
So what does this tell us? I am going to use a live server as an example, with the data taken from off-peak hours. Let's look at a screenshot.
Firstly, the number of connections is not a problem. It may be off-peaks, but the maximum is still way below the limit. The database server has recently been upgraded, and the previous maximum connection limit has been maintained. Because the server processes the queries much more quickly now, the connections have not been building up nearly as much.
Secondly, the bytes sent are running at about one sixth of the maximum, but slightly above average, as one would expect from an off-peak server (remembering that excessive highs or lows skew averages).
Finally, the number of queries running is quite low, again corresponding to the time of day. The main probable call to action from this screen would be if the connection usage were approaching 100%, indicating one should up the max_connections in the config file.
#