Hi,

While searching for myaql performance report base tool for php. I have found wonderful tool for mysql performance checking and report generation.

It is deemon called mysqlard (MySQL Activity Report) it presents useful graphs by processing the data to enforce a certain data density. The main goal of the package is to help the database system administrator in tuning the system for maximum performance.

This package contains example graphing, cron and PHP script.

Before installing this package you need to have "rrdtool" install. I have described the step how to install and monitor the use this tool:

First you need to install "rrdtool"



download the rrdtool:
rrdtool's home page is : http://people.ee.ethz.ch/~oetiker/webtools/rrdtool/



Now, you need to install (MySQL Activity Report) mysqlard



You need to compile I mean configure this using below metiond steps:

You could use only confiure option if have all is well set



You can also specify the rrd and mysql path if you are facing any error while compiling



Basic, installation is completed. For configuration:

You will find the file named mysqlard.cnf at /etc folder. if this file not created by default then you have copy from the below mentioned folder:



Edit the mysqlard.cnf file and add search for below mentioend string


provide the exact path of your rrdtool

save and exit



You need to set the correct permission for apache users and set the alias in your httpd conf file for var/lib/mysqlard folder.

restart the httpd services

Open your browser and browse the URL:

http://yourserverIP//mysqlar.php

This how you could install mysqlar Enjoy

Thanks!

I compiled using your instructions and had to specify the rrdtool path to configure.

Everything compiled ok and installed, but when I try to start the program I get this:

Try to locate the path of librrd.so.2 and create symbolic link to /usr/lib/ as mentioned below:

# locate librrd.so.2
# ln -s /usr/local/rrdtool-1.2.12/lib/librrd.so.2.0.6 /usr/lib/librrd.so.2



Pleas check it

Thanks. That worked and eliminated the error.

Now, I am getting a 500 error when I try to access mysqlar.php. I opened the file and noticed that there is a place to specify the user and password for the mysql database. I am assuming that you need to put in the root user and password. Is that right?

Here's what error_log has to say about it:

[Wed Oct 11 09:33:29 2006] [error] (8)Exec format error: exec of /var/lib/mysqlard/mysqlar.php failed
[Wed Oct 11 09:33:29 2006] [error] [client 24.63.69.230] Premature end of script headers: /var/lib/mysqlard/mysqlar.php

Permissions on the file are 755 and it is owned by root.

First check for :

The safest way to configure the daemon is to create a mysql user who has only USAGE permissions for MySQL, no permissions on any databases and no password. Configured like this, you don't have to put a plain text password in any of the config files or php files.

Run the following command in the MySQL shell to add a monitoring user without a password who can only connect from localhost :

GRANT USAGE ON *.* TO mysqlar@localhost;


Check your alias setting in your httpd.conf file.

Alias /mysqlstat/ "/var/lib/mysqlard/"

also set the ownership of that folder to your apache user and then check

Thanks,

OK, I created the user but the daemon wouldn't start, so I did a little investigating in my logs and found that it was unable to write its pid file.

So, I changed the path for the pid file to /var/run and the daemon starts ok now.

I also discovered that the archive directory must be created before the cron jobs will run.

I sitll am getting the same 500 error on the php script now, though.

Mark

I could easily setup and start the daemon and it is working fine.

Is .png file is generating in your mysqlard directory where your 'mysqlar.php' is located?

root#./mysqlard.server start
Loading defaultsettings
root#

I dont think that there is any error in configuration of MySQLard but you many need to check alias of mysqlard directory.

For more information regarding this you could check it homepage, FAQ and docs at

http://gert.sos.be/en/

Thanks,

Hi,

Last time I tried rrdtool v1.2.13 still didn't work correctly on RHEL 4, had to use older v1.0.49 instead. Did you install v1.2.14 on RHEL 4? And it worked?

As an additional note if anyone is having problems with rrdtool install - /tmp is noexec by default in RHEL4, so though builds can be stored there, u have to execute installs in e.g. /usr/local/src/

I am using RHEL3 on this machine. I visited the site you suggested and found that I had to run a cronjob in order to create the png files. So, I did that but still having the 500 error when running the php script.

I will contact the author and see if he can provided some assistance.

Thanks for all the help.

I have tried with stable version: rrdtool-1.2.12 of rrdtool

I can't get the graphics to show - got cacti installed so i know rrdtool is working properly.

Added the crontab also as shown in the manual. Still nothing.

I'm confused why there are only mysqlar.daily, mysqlar.weekly, mysqlar.monthly files and no mysqlar.hourly file though it expects hourly graphs.

EDIT: never mind got it to work.

TIP: it's a good idea to use the target path given in this example, otherwise all other paths given here and on official website will be also different.

You need to add a cron job to generate the graphs. For example :

*/5 * * * * hourly=1 daily=1 weekly=1 monthly=1 /usr/bin/mysqlar_graph > /dev/null

This will generate hourly, daily, weekly and monthly graphs in ${imgdir}, /var/lib/mysqlard/ in most cases

hey... everything seems to be working fine, except it doesn't show any graphs - only shows the graph backgrounds. Like when it would when I'm not getting any select, update, delete etc. queries, but I am.

Any ideas? Maybe I'll have to log in the script as MySQL root instead?

Use command:

# mysqlard -u root -p

and check

ok, ran that in shell, it executed, didn't give any info.

I also tried switching to root user in mysqlar.php, still nothing

It draws the graph backgrounds, but no graphs... like it's not getting information about the queries... yet the query amounts increase on the right side pane by around 100-200 select queries with every refresh.

Yes, that command will not give you any information it just start the daemon in your processlist.

I think you many need to create folder called archive in /var/lib/mysqlard/ directory.

and then execute the below mentioned command:

# graph_script=${graph_script:="/usr/bin/mysqlar_graph"}
# archivedate=$(date -dyesterday "+%F")
# start=${archivedate} ext="-${archivedate}" daily=1 ${graph_script} 1>/dev/null


Thanks,

Hi all,

did any of you tried installing this on SuSE?
I did, but my lack of Linux-experience stopped me.
On a SLES 10.1 machine with MySQL-5 I ended up in the dependency-hell.
For installing rrdtool I think I succeeded by installing the needed *-devel packages so the installation continued. But for mysqlard I had to contact Gert de Wit (the author) who told me I need to rebuild the package voor SuSE. The problem is that the rebuild asks for rrdtool-devel which I cannot find for SuSE.

any help would be great,

Ed

created the archive directory, tried the commands, nothing... I'm all out of ideas... the hour and the week backgrounds just show empty background grids... the daily graph has an extra vertical line on the left side... queries per minute are between 0.0 and 1.0.

I'm wondering if it could be caused due to installing it into /usr/local/share/mysqlard/ directory instead of /var/lib/mysqlard ....maybe some path is incorrect somewhere.

might have found the problem:

# PID file name
pidfile="/usr/local/var/mysqlard.pid"

the directory doesn't exist and can't find that file neither anywhere. suggestions?

Try setting the PID file to be in /var/run.

Mark

The file doesn't get written to (stays 0 bytes), the permissions are correct and it's owned by root.root like most other .pid files in that directory.

Second try on another (fresh, plain RHEL4 ES) server, tried with correct paths this time, using rrdtool-1.2.15.

> ./configure --prefix=/usr --sysconfdir=/etc --datadir=/var/lib --with-rrd=/usr/local/rrdtool-1.2.15

ran fine

> make

make all-recursive
make[1]: Entering directory `/usr/local/src/mysqlard-1.0.0'
Making all in src
make[2]: Entering directory `/usr/local/src/mysqlard-1.0.0/src'
if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/local/rrdtool-1.2.15/include -I/tmp/rrdbuild/lb/include -I/tmp/rrdbuild/lb/include/libart-2.0 -I/tmp/rrdbuild/lb/include/freetype2 -I/tmp/rrdbuild/lb/include/libpng -O3 -I/usr/include/mysql -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -MT main.o -MD -MP -MF ".deps/main.Tpo" -c -o main.o main.c; \
then mv -f ".deps/main.Tpo" ".deps/main.Po"; else rm -f ".deps/main.Tpo"; exit 1; fi
main.c:11:23: my_global.h: No such file or directory
main.c:12:20: my_sys.h: No such file or directory
main.c:13:22: m_string.h: No such file or directory
main.c:14:19: mysql.h: No such file or directory
main.c:15:20: errmsg.h: No such file or directory
main.c:50: error: syntax error before '*' token
main.c:51: error: syntax error before '*' token
main.c:52: error: syntax error before '*' token
main.c:53: error: syntax error before '*' token
main.c: In function `main':
main.c:67: error: `MYSQL' undeclared (first use in this function)
main.c:67: error: (Each undeclared identifier is reported only once
main.c:67: error: for each function it appears in.)
main.c:67: error: syntax error before "mysql"
main.c:284: error: `LOCAL_HOST' undeclared (first use in this function)
main.c:291: error: `mysql' undeclared (first use in this function)
main.c: At top level:
main.c:367: error: syntax error before '*' token
main.c: In function `ar_get_var':
main.c:368: error: `mysql' undeclared (first use in this function)
main.c:368: error: `varname' undeclared (first use in this function)
main.c: At top level:
main.c:371: error: syntax error before '*' token
main.c: In function `ar_get_param':
main.c:372: error: `mysql' undeclared (first use in this function)
main.c:372: error: `varname' undeclared (first use in this function)
main.c: At top level:
main.c:375: error: syntax error before '*' token
main.c: In function `ar_get':
main.c:377: error: `MYSQL_RES' undeclared (first use in this function)
main.c:377: error: `res' undeclared (first use in this function)
main.c:378: error: `MYSQL_ROW' undeclared (first use in this function)
main.c:378: error: syntax error before "row"
main.c:380: error: `var' undeclared (first use in this function)
main.c:381: error: `varname' undeclared (first use in this function)
main.c:385: error: `mysql' undeclared (first use in this function)
main.c:389: error: `row' undeclared (first use in this function)
main.c: At top level:
main.c:397: error: syntax error before '*' token
main.c: In function `ar_get_sl_param':
main.c:399: error: `MYSQL_RES' undeclared (first use in this function)
main.c:399: error: `res' undeclared (first use in this function)
main.c:400: error: `MYSQL_ROW' undeclared (first use in this function)
main.c:400: error: syntax error before "row"
main.c:404: error: `MYSQL_FIELD' undeclared (first use in this function)
main.c:404: error: `fields' undeclared (first use in this function)
main.c:406: error: `mysql' undeclared (first use in this function)
main.c:411: error: `row' undeclared (first use in this function)
main.c:415: error: `varname' undeclared (first use in this function)
make[2]: *** [main.o] Error 1
make[2]: Leaving directory `/usr/local/src/mysqlard-1.0.0/src'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/usr/local/src/mysqlard-1.0.0'
make: *** [all] Error 2
[root@www mysqlard-1.0.0]# make install
Making install in src
make[1]: Entering directory `/usr/local/src/mysqlard-1.0.0/src'
if gcc -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/local/rrdtool-1.2.15/include -I/tmp/rrdbuild/lb/include -I/tmp/rrdbuild/lb/include/libart-2.0 -I/tmp/rrdbuild/lb/include/freetype2 -I/tmp/rrdbuild/lb/include/libpng -O3 -I/usr/include/mysql -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -MT main.o -MD -MP -MF ".deps/main.Tpo" -c -o main.o main.c; \
then mv -f ".deps/main.Tpo" ".deps/main.Po"; else rm -f ".deps/main.Tpo"; exit 1; fi
main.c:11:23: my_global.h: No such file or directory
main.c:12:20: my_sys.h: No such file or directory
main.c:13:22: m_string.h: No such file or directory
main.c:14:19: mysql.h: No such file or directory
main.c:15:20: errmsg.h: No such file or directory
main.c:50: error: syntax error before '*' token
main.c:51: error: syntax error before '*' token
main.c:52: error: syntax error before '*' token
main.c:53: error: syntax error before '*' token
main.c: In function `main':
main.c:67: error: `MYSQL' undeclared (first use in this function)
main.c:67: error: (Each undeclared identifier is reported only once
main.c:67: error: for each function it appears in.)
main.c:67: error: syntax error before "mysql"
main.c:284: error: `LOCAL_HOST' undeclared (first use in this function)
main.c:291: error: `mysql' undeclared (first use in this function)
main.c: At top level:
main.c:367: error: syntax error before '*' token
main.c: In function `ar_get_var':
main.c:368: error: `mysql' undeclared (first use in this function)
main.c:368: error: `varname' undeclared (first use in this function)
main.c: At top level:
main.c:371: error: syntax error before '*' token
main.c: In function `ar_get_param':
main.c:372: error: `mysql' undeclared (first use in this function)
main.c:372: error: `varname' undeclared (first use in this function)
main.c: At top level:
main.c:375: error: syntax error before '*' token
main.c: In function `ar_get':
main.c:377: error: `MYSQL_RES' undeclared (first use in this function)
main.c:377: error: `res' undeclared (first use in this function)
main.c:378: error: `MYSQL_ROW' undeclared (first use in this function)
main.c:378: error: syntax error before "row"
main.c:380: error: `var' undeclared (first use in this function)
main.c:381: error: `varname' undeclared (first use in this function)
main.c:385: error: `mysql' undeclared (first use in this function)
main.c:389: error: `row' undeclared (first use in this function)
main.c: At top level:
main.c:397: error: syntax error before '*' token
main.c: In function `ar_get_sl_param':
main.c:399: error: `MYSQL_RES' undeclared (first use in this function)
main.c:399: error: `res' undeclared (first use in this function)
main.c:400: error: `MYSQL_ROW' undeclared (first use in this function)
main.c:400: error: syntax error before "row"
main.c:404: error: `MYSQL_FIELD' undeclared (first use in this function)
main.c:404: error: `fields' undeclared (first use in this function)
main.c:406: error: `mysql' undeclared (first use in this function)
main.c:411: error: `row' undeclared (first use in this function)
main.c:415: error: `varname' undeclared (first use in this function)
make[1]: *** [main.o] Error 1
make[1]: Leaving directory `/usr/local/src/mysqlard-1.0.0/src'
make: *** [install-recursive] Error 1

I've re-installed it on the old server too with the paths described here (except /usr/local/src instead of /tmp as /tmp is nosuid,noexec)... I'm now getting only a blank page when I visit the mysqlar.php file on the old server.

Has anyone gotten this working on a plain RHEL-4 ES?

ok, solved the problems:

1. The make file errors were due to "mysql-devel" not being installed
"up2date -i mysql-devel" to fix that

2. the blank page was due to open_basedir restriction as the /var/lib is not in the open_basedir.

Tip: one can remove the javascript:hide_all function from the body tag of mysqlar.php, so that all graphs would be visible when you load the page instead of having to start clicking show.

Any ideas on what's the best way to reset the graphs? Delete the png files? or the rrd files?