The MySQL Graph Collection

[ottob]

Hi all,

i've made a small collection of mysql-graphs like those in the picture.
The QueryCache Graph works only with Mysql 4.* all other graphs will work also with earlier versions...

The scripts are an enhangement of a script i have found elsewhere in this board - so thanks to the author!

Installation:
Put the files in the scripts-dir of the zip-file into the cacti script directory. Then import the xml-files into cacti using the web-interface.

To create mysql-graphs you must have the "process"-right on the mysql-server you want to graph. Use an sql-statement like this to set this rights:

Code: Select all

GRANT PROCESS ON * TO cactiuser@localhost IDENTIFIED by 'mypassword';

Using the Templates:
Select a Host an create a new graph by using one of the Mysql-templates. Fill in the User an Password fields (in the example above: cactiuser, mypassword)...

Have fun,
Otto

[SCaRaBaeuS]

looks very nice

only in the single statistics
i have to fill in that Status variable, but what is it? thanks!

[ottob]

Hi!

The "status variable" is the name of one of the following mysql-variables. You can use the Template "Single statistics" to graph one of these values...

Code: Select all

mysql> show status;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 12         |
| Aborted_connects         | 8          |
| Bytes_received           | 97112304   |
| Bytes_sent               | 1029178198 |
| Com_admin_commands       | 0          |
| Com_alter_table          | 0          |
| Com_analyze              | 0          |
| Com_backup_table         | 0          |
| Com_begin                | 31         |
| Com_change_db            | 462763     |
| Com_change_master        | 0          |
| Com_check                | 0          |
| Com_commit               | 31         |
| Com_create_db            | 0          |
| Com_create_function      | 0          |
| Com_create_index         | 0          |
| Com_create_table         | 0          |
| Com_delete               | 86         |
| Com_delete_multi         | 0          |
| Com_drop_db              | 0          |
| Com_drop_function        | 0          |
| Com_drop_index           | 0          |
| Com_drop_table           | 0          |
| Com_flush                | 0          |
| Com_grant                | 0          |
| Com_ha_close             | 0          |
| Com_ha_open              | 0          |
| Com_ha_read              | 0          |
| Com_insert               | 26         |
| Com_insert_select        | 0          |
| Com_kill                 | 0          |
| Com_load                 | 4          |
| Com_load_master_data     | 0          |
| Com_load_master_table    | 0          |
| Com_lock_tables          | 0          |
| Com_optimize             | 0          |
| Com_purge                | 0          |
| Com_rename_table         | 0          |
| Com_repair               | 0          |
| Com_replace              | 56         |
| Com_replace_select       | 15         |
| Com_reset                | 0          |
| Com_restore_table        | 0          |
| Com_revoke               | 0          |
| Com_rollback             | 0          |
| Com_savepoint            | 0          |
| Com_select               | 199667     |
| Com_set_option           | 1380       |
| Com_show_binlog_events   | 0          |
| Com_show_binlogs         | 0          |
| Com_show_create          | 1389       |
| Com_show_databases       | 0          |
| Com_show_fields          | 1453       |
| Com_show_grants          | 0          |
| Com_show_keys            | 54         |
| Com_show_logs            | 0          |
| Com_show_master_status   | 0          |
| Com_show_new_master      | 0          |
| Com_show_open_tables     | 0          |
| Com_show_processlist     | 0          |
| Com_show_slave_hosts     | 0          |
| Com_show_slave_status    | 0          |
| Com_show_status          | 25969      |
| Com_show_innodb_status   | 0          |
| Com_show_tables          | 1769       |
| Com_show_variables       | 14830      |
| Com_slave_start          | 0          |
| Com_slave_stop           | 0          |
| Com_truncate             | 0          |
| Com_unlock_tables        | 0          |
| Com_update               | 66         |
| Connections              | 488691     |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 47         |
| Created_tmp_files        | 0          |
| Delayed_insert_threads   | 0          |
| Delayed_writes           | 0          |
| Delayed_errors           | 0          |
| Flush_commands           | 1          |
| Handler_commit           | 0          |
| Handler_delete           | 127409     |
| Handler_read_first       | 2511       |
| Handler_read_key         | 167106     |
| Handler_read_next        | 409328345  |
| Handler_read_prev        | 261        |
| Handler_read_rnd         | 71668635   |
| Handler_read_rnd_next    | 1007629376 |
| Handler_rollback         | 0          |
| Handler_update           | 159        |
| Handler_write            | 159410     |
| Key_blocks_used          | 7793       |
| Key_read_requests        | 34433841   |
| Key_reads                | 40194      |
| Key_write_requests       | 3775448    |
| Key_writes               | 31441      |
| Max_used_connections     | 9          |
| Not_flushed_key_blocks   | 0          |
| Not_flushed_delayed_rows | 0          |
| Open_tables              | 64         |
| Open_files               | 128        |
| Open_streams             | 0          |
| Opened_tables            | 1364       |
| Questions                | 1585126    |
| Qcache_queries_in_cache  | 23586      |
| Qcache_inserts           | 197855     |
| Qcache_hits              | 386859     |
| Qcache_lowmem_prunes     | 35251      |
| Qcache_not_cached        | 32149      |
| Qcache_free_memory       | 1022800    |
| Qcache_free_blocks       | 11         |
| Qcache_total_blocks      | 47349      |
| Rpl_status               | NULL       |
| Select_full_join         | 0          |
| Select_full_range_join   | 0          |
| Select_range             | 98698      |
| Select_range_check       | 0          |
| Select_scan              | 33351      |
| Slave_open_temp_tables   | 0          |
| Slave_running            | OFF        |
| Slow_launch_threads      | 0          |
| Slow_queries             | 0          |
| Sort_merge_passes        | 0          |
| Sort_range               | 67900      |
| Sort_rows                | 71668548   |
| Sort_scan                | 48         |
| Table_locks_immediate    | 201448     |
| Table_locks_waited       | 8          |
| Threads_cached           | 0          |
| Threads_created          | 488690     |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 1254820    |
+--------------------------+------------+
132 rows in set (0.00 sec)

Otto

[SCaRaBaeuS]

aha okay thanks

well i deleted it for now

btw:
http://stats.only4clans.com/graph_view. ... on=preview

some of them wont work or tho nothing there.. did i do something wrong? or just that nothing is in it? thanks

[ottob]

Maybe the mysql-rights are not correct?

i saw i had a an error in my mysql-grant statement above. It must be like:

Code: Select all

GRANT PROCESS ON * TO cactiuser@localhost IDENTIFIED by 'mypassword';

where cactiuser is the mysql-user typed into the username-field during the graph creation...

Otto

[SCaRaBaeuS]

Maybe the mysql-rights are not correct?

i saw i had a an error in my mysql-grant statement above. It must be like:

Code: Select all

GRANT PROCESS ON * TO cactiuser@localhost IDENTIFIED by 'mypassword';

where cactiuser is the mysql-user typed into the username-field during the graph creation...

Otto

maybe can you contact me on msn: paul[at]vanderholst.com

and yes i did that should work though

[ottob]

sorry, i dont have an msn account

in cacticonsole->configuration->utilities->"view poller cache" you see elsewhere the commands like them of the mysql-graph-scripts. For example:

Code: Select all

php -q /home/httpd/vhosts/xxx/httpdocs/cacti/scripts/mysql_com.php xxx cactiuser cactipassword

try to execute the command on the shell and see what happens...

[SCaRaBaeuS]

okay i found the problem.

normally a mysql connection has a priveliges system for localhost i just set that (so nobody from the outside can connect)

But my cacti has a hostname www.only4clans.com so it connects to that, but that doens't work..
so you should change your stuff to localhost instead to |hostname|

(also alot of the time php -q doesnt work)
example i have to use /usr/local/bin/php instead of only php

[Steve]

I'm having difficulty getting this up and running.. The "connections" and "questions" graphs work fine, but anything referencing the scripts does not seem to.

I've installed everything, and when I run any one of the commands manually under the webserver's user, the command responds fine. For example:

/usr/local/bin/php -q /remote/web0/www.domain.com/public_html/cacti-0.8.5a ... ql_com.php 192.168.10.2 root password

RETURNS

change_db:20063303 delete:3218219 insert:2741713 select:14300059 update:3669556

So that seems fine.. correct me otherwise.

Viewing the poller cache shows the following:

Data Source: Server: www - MySQL - Command statistics
RRD: /remote/web0/www.domain.com/public_html/cacti-0.8.5a ... ct_313.rrd
Action: 2, Script: /usr/local/bin/php -q /remote/web0/www.domain.com/public_html/cacti-0.8.5a ... ql_com.php 192.168.10.2 root password

So it seems to run without problems, but after several hours, there is no information on the graph (which I've attached below).

Any help would be appreciated.

-- Steve

[SCaRaBaeuS]

i got the same problem but then with query cache :S hmm lemme check have no time right now

[Steve]

i got the same problem but then with query cache :S hmm lemme check have no time right now

Ok, after I hardcoded the proper path to php into Cacti, and cleared the poller cache, all the graphs began to generate except "commands". Same thing as before, the command runs fine from the command line, the poller cache verifies the proper command it being run...

Any ideas would be useful.

-- Steve.

[ottob]

The "connections" and "questions" graphs work fine, but anything referencing the scripts does not seem to.

-> mysterious if the connection and questions graphs are working.
Sorry i have no ideas at this point... I rember i got the same problems ones - but i aktually dont know how i solved them...
Maybe anything worse at the path to the php-binary ("data input methods")?

Otto

[Guest]

otto ,

nice scripts !! And thx for explaining them a bit

I have a short question, since i have to work 'into' mysql a bit.

I've installed your scripts + template cacti_graph_template_mysql_connections.xml as a first test.

I first have got errors ob console and 'empty' graphs.
Solution:
- Had to change Data Templates --> MySQL - Connections --> Custom Data --> Database Host --> INSERT into the field: localhost.
This seems to come from my configuration of mysql ...

Afterwards i got no errors on console + graphs

But what i have seen is: The number of 'Connections' is always increasing:

command: php -q /home/htdocs/web1/html/cacti/scripts/mysql_stat.php localhost cactiuser xxxx Connections, output: 1434 <------

Each time the cmd polls it this counter increases, not going down ...
And this, even so i have no real usage of the database yet

Is this a 'normal' behaviour ? of course, the cacti db i am using, but no other users or usage.

THX Stef

[ottob]

Hi Stef,

maybe its normal... The number of connections is a real value and its gathered as a "counter"-value (as seen in the Data-Template). Seriously some time i will go down

It seems that a few users have the problem with the Database Host. Normally the Database-Hostname comes from the current device (Management->Devices). Maybe its depending on the cacti-version (i use 0.8.5a)?

[ottob]

Sorry, the connections-value is not a "real" value its a "counting"-value. Be shure the setting "Datasource type" in the data-template is "counter"...