Statistics Gathering Script

I posted some code to the MySQL Forge at:

http://forge.mysql.com/snippets/view.php?id=52

Basically I gather some stats on my webservers in a very crude way. It runs a bunch of commands on the commandline (I run it every 5 minutes from a cron script). Please note that it adds to the load of the database server by running these commands, and it connects a few times to the database, so it actually increases things like “total # of connections” by the sheer fact that it runs.

This should run on most Unix machines, running most versions of MySQL. Please comment to this entry if you find something wrong or an incompatibility.

An explanation of the code follows:

This script will produce a string that looks like this:
(‘value’,’value’,’value’,123,’value’),
and all you have to do is add a line at the top: “INSERT INTO table (field1,field2,field3,field4,field5) VALUES”

and change the last comma to a semicolon. Then you will have something suitable for entering into a database, for later processing.

So here is an explanation of the lines of code, in order — the first few set variables:
#!/bin/sh
DB=db_here
USER=user_here
PASS=pass_here
HOST=`/bin/hostname | /bin/cut -f1 -d\.`

/bin/echo \(\'$HOST\',\'\

“echo” sets up the fact that you are printing all this out (later you >> to a file). \ is the escape character, and the line starts with “(“. Then we put the hostname in quotes, print a comma, and then an open quote. The final \ means “continue on the next line”, and it shows up on every line hereafter, so we can build a long string and then save it to a file.

`/bin/date +%Y-%m-%d\ %T`\',\

Putting “ around a command ensures that it gets run first, and then echo uses the output. Otherwise echo would simply print out “/bin/date +%Y…..”

This gets the date and time in MySQL’s date format (‘YYYY-MM-DD HH:MM:SS’), and puts in the ‘ to end the quoted field and a comma.

`/usr/bin/mysql -u $USER -p$PASS -e "\s"| /bin/awk '/Threads/ {print $2","$7","$20}'`,\

If you type \s at the database prompt, you will get some information that looks like this:


mysql> \s
--------------
mysql Ver 12.22 Distrib 4.0.20, for sun-solaris2.8 (sparc)
Connection id: 296486
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.20-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 57 days 8 hours 6 min 25 sec
Threads: 1 Questions: 9331360 Slow queries: 32 Opens: 19372 Flush tables: 1 Open tables: 64 Queries per second avg: 1.884
--------------

This gets fields 2, 7 and 20 (fields are delimited by spaces) from the line marked “Threads” — field 2 is “1” relating to # of threads, field 7 is “32” which refers to the number of slow queries, and field 20 is “1.884” which referes to “Queries per second avg”. So this line gets 3 values. Since they are numbers, they do not need to be quoted*.

`cat /proc/loadavg | /bin/awk '{print $1}'`,\

/proc has some great information in it, but you have to use cat to view them. This gets the current load average, that appears as the first argument to “uptime”. Again, this is a number and does not need to be quoted.*

`cat /proc/meminfo | /bin/awk '/MemTotal|MemFree|SwapTotal|SwapFree/ {print $2","}'` \

This prints the 2nd field (and a comma separation) from /proc/meminfo from the lines that deal with total memory, free memory, total swap and free swap space. Again, these are numbers and do not require quoting.*

`/usr/bin/mysql -u $USER -p$PASS $DB -e "show table status\G" | /bin/awk '/InnoDB free:/ {print $4}' | /usr/bin/head -1`,\

This is a very hacky way to find how much space is left in the InnoDB tablespace. I use one set of InnoDB data files for all the InnoDB tables I have, so I can just take the free space from the first table, and that is OK. If you are using 1 file per table, you will want to get each value.

`/usr/bin/mysql -u $USER -p$PASS $DB -e "show status" | /bin/awk '/Bytes_received\W|Bytes_sent\W|Com_delete\W|Com_insert\W|Com_replace\W|Com_select\W|Com_update\W|Connections\W|Questions\W|Uptime\W/ {printf $2 ","}'`\

This gets lots of information from the SHOW STATUS command of MySQL. The Com_* commands are the counts, so Com_insert is the count of inserts (since the database instance started, so you can do Com_insert/Uptime to get inserts/second if you want).

Connections is the total number of connections (not to be confused with Threads, which is the current # of threads connected). Questions is total number of queries.

I wrote this in fits and starts, and above I parsed some output of “\s”. However, those numbers can be gotten by the “SHOW STATUS” variables of Slow_queries, Threads_created and Questions/Uptime. Because I am lazy, I have not changed my scripts, although you could decrease the amount of data MySQL sends and lessen the impact of this script with one less connection if you added it to this line.

There are TONS of variables in “SHOW STATUS” that are interesting and you might want to track. Decide what you like…and you can always add them in later!

`df -k /var | /bin/awk '/dev/ {print $2 "," $4}'`,\

This just looks at the space of /var, which is where my company keeps our mysql data files.

`df -k /var/log | /bin/awk '/dev/ {print $2 "," $4}'`,\

This just looks at the space of /var/log — where my company keeps our mysql logfiles.

`/usr/bin/mysql -u $USER -p$PASS $DB -e "SELECT COUNT(DISTINCT uid) FROM Sessions\G" | /bin/awk '/COUNT/ {print $3}' `,\

This gets information from a particular table in our system — it shows how many people are online. This is useful for us to gather because now we can see how the statistics we gather are related to how many people are logged in.

`/usr/bin/iostat -dk | /bin/awk '/sda/ {print $2","$5","$6}'`\),\

This requires the “sysstat” package, and this line retrieves the I/O (disk) transactions per second, the KB read and the KB written to the disk.

>> /home/care/`echo $HOST`perf.csv

And this is the last line — finally, at last, we save it to a file.

PLEASE feel free to modify this and make it cleaner……obviously you will want to apply it to your own situation and what you want to gather.

I run this as a local script on each database machine, because it gets information using the commandline. I save everything to a file and do a weekly import. Theoretically I could have it save directly to a reporting database, but I do not want any dependence on network, just in case. You may want to have the process more automatic.

Weekly I have a cron job to “rotate” the .csv file: copy the file locally, copy it to a remote server, and then delete all but the first line (the first line being “INSERT INTO tbl VALUES(…..”).

(for those interested, this is how I delete all but the first line. Oh so hacky….)
HOST=`/bin/hostname | /bin/cut -f1 -d\.`
head -1 /home/care/`echo $HOST`perf.csv > /tmp/temp; mv /tmp/temp /home/care/`echo $HOST`perf.csv;

And for those who care, the following is the first line of my file:

REPLACE INTO dbstats (serverName,statsTime,procs,slowQueries,queriespersec,serverLoad,MemTotal
,MemFree,SwapTotal,SwapFree,ibFree,bytesRec,bytesSent,deletes,inserts,replaces,selects,updates
,connections,queries,mysqlUptime,sizeKBvar,availKBvar,sizeKBvarlog,availKBvarlog,Sessions,tps,
kbRead,kbWrtn) VALUES

* while numbers do not need to be quoted, if there’s ever a chance that the number may end up blank instead of 0, it should be quoted, as MySQL does not handle “VALUES (‘foo’,,’bar’)”
and throws an error.

I posted some code to the MySQL Forge at:

http://forge.mysql.com/snippets/view.php?id=52

Basically I gather some stats on my webservers in a very crude way. It runs a bunch of commands on the commandline (I run it every 5 minutes from a cron script). Please note that it adds to the load of the database server by running these commands, and it connects a few times to the database, so it actually increases things like “total # of connections” by the sheer fact that it runs.

This should run on most Unix machines, running most versions of MySQL. Please comment to this entry if you find something wrong or an incompatibility.

An explanation of the code follows:

This script will produce a string that looks like this:
(‘value’,’value’,’value’,123,’value’),
and all you have to do is add a line at the top: “INSERT INTO table (field1,field2,field3,field4,field5) VALUES”

and change the last comma to a semicolon. Then you will have something suitable for entering into a database, for later processing.

So here is an explanation of the lines of code, in order — the first few set variables:
#!/bin/sh
DB=db_here
USER=user_here
PASS=pass_here
HOST=`/bin/hostname | /bin/cut -f1 -d\.`

/bin/echo \(\'$HOST\',\'\

“echo” sets up the fact that you are printing all this out (later you >> to a file). \ is the escape character, and the line starts with “(“. Then we put the hostname in quotes, print a comma, and then an open quote. The final \ means “continue on the next line”, and it shows up on every line hereafter, so we can build a long string and then save it to a file.

`/bin/date +%Y-%m-%d\ %T`\',\

Putting “ around a command ensures that it gets run first, and then echo uses the output. Otherwise echo would simply print out “/bin/date +%Y…..”

This gets the date and time in MySQL’s date format (‘YYYY-MM-DD HH:MM:SS’), and puts in the ‘ to end the quoted field and a comma.

`/usr/bin/mysql -u $USER -p$PASS -e "\s"| /bin/awk '/Threads/ {print $2","$7","$20}'`,\

If you type \s at the database prompt, you will get some information that looks like this:


mysql> \s
--------------
mysql Ver 12.22 Distrib 4.0.20, for sun-solaris2.8 (sparc)
Connection id: 296486
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Server version: 4.0.20-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 57 days 8 hours 6 min 25 sec
Threads: 1 Questions: 9331360 Slow queries: 32 Opens: 19372 Flush tables: 1 Open tables: 64 Queries per second avg: 1.884
--------------

This gets fields 2, 7 and 20 (fields are delimited by spaces) from the line marked “Threads” — field 2 is “1” relating to # of threads, field 7 is “32” which refers to the number of slow queries, and field 20 is “1.884” which referes to “Queries per second avg”. So this line gets 3 values. Since they are numbers, they do not need to be quoted*.

`cat /proc/loadavg | /bin/awk '{print $1}'`,\

/proc has some great information in it, but you have to use cat to view them. This gets the current load average, that appears as the first argument to “uptime”. Again, this is a number and does not need to be quoted.*

`cat /proc/meminfo | /bin/awk '/MemTotal|MemFree|SwapTotal|SwapFree/ {print $2","}'` \

This prints the 2nd field (and a comma separation) from /proc/meminfo from the lines that deal with total memory, free memory, total swap and free swap space. Again, these are numbers and do not require quoting.*

`/usr/bin/mysql -u $USER -p$PASS $DB -e "show table status\G" | /bin/awk '/InnoDB free:/ {print $4}' | /usr/bin/head -1`,\

This is a very hacky way to find how much space is left in the InnoDB tablespace. I use one set of InnoDB data files for all the InnoDB tables I have, so I can just take the free space from the first table, and that is OK. If you are using 1 file per table, you will want to get each value.

`/usr/bin/mysql -u $USER -p$PASS $DB -e "show status" | /bin/awk '/Bytes_received\W|Bytes_sent\W|Com_delete\W|Com_insert\W|Com_replace\W|Com_select\W|Com_update\W|Connections\W|Questions\W|Uptime\W/ {printf $2 ","}'`\

This gets lots of information from the SHOW STATUS command of MySQL. The Com_* commands are the counts, so Com_insert is the count of inserts (since the database instance started, so you can do Com_insert/Uptime to get inserts/second if you want).

Connections is the total number of connections (not to be confused with Threads, which is the current # of threads connected). Questions is total number of queries.

I wrote this in fits and starts, and above I parsed some output of “\s”. However, those numbers can be gotten by the “SHOW STATUS” variables of Slow_queries, Threads_created and Questions/Uptime. Because I am lazy, I have not changed my scripts, although you could decrease the amount of data MySQL sends and lessen the impact of this script with one less connection if you added it to this line.

There are TONS of variables in “SHOW STATUS” that are interesting and you might want to track. Decide what you like…and you can always add them in later!

`df -k /var | /bin/awk '/dev/ {print $2 "," $4}'`,\

This just looks at the space of /var, which is where my company keeps our mysql data files.

`df -k /var/log | /bin/awk '/dev/ {print $2 "," $4}'`,\

This just looks at the space of /var/log — where my company keeps our mysql logfiles.

`/usr/bin/mysql -u $USER -p$PASS $DB -e "SELECT COUNT(DISTINCT uid) FROM Sessions\G" | /bin/awk '/COUNT/ {print $3}' `,\

This gets information from a particular table in our system — it shows how many people are online. This is useful for us to gather because now we can see how the statistics we gather are related to how many people are logged in.

`/usr/bin/iostat -dk | /bin/awk '/sda/ {print $2","$5","$6}'`\),\

This requires the “sysstat” package, and this line retrieves the I/O (disk) transactions per second, the KB read and the KB written to the disk.

>> /home/care/`echo $HOST`perf.csv

And this is the last line — finally, at last, we save it to a file.

PLEASE feel free to modify this and make it cleaner……obviously you will want to apply it to your own situation and what you want to gather.

I run this as a local script on each database machine, because it gets information using the commandline. I save everything to a file and do a weekly import. Theoretically I could have it save directly to a reporting database, but I do not want any dependence on network, just in case. You may want to have the process more automatic.

Weekly I have a cron job to “rotate” the .csv file: copy the file locally, copy it to a remote server, and then delete all but the first line (the first line being “INSERT INTO tbl VALUES(…..”).

(for those interested, this is how I delete all but the first line. Oh so hacky….)
HOST=`/bin/hostname | /bin/cut -f1 -d\.`
head -1 /home/care/`echo $HOST`perf.csv > /tmp/temp; mv /tmp/temp /home/care/`echo $HOST`perf.csv;

And for those who care, the following is the first line of my file:

REPLACE INTO dbstats (serverName,statsTime,procs,slowQueries,queriespersec,serverLoad,MemTotal
,MemFree,SwapTotal,SwapFree,ibFree,bytesRec,bytesSent,deletes,inserts,replaces,selects,updates
,connections,queries,mysqlUptime,sizeKBvar,availKBvar,sizeKBvarlog,availKBvarlog,Sessions,tps,
kbRead,kbWrtn) VALUES

* while numbers do not need to be quoted, if there’s ever a chance that the number may end up blank instead of 0, it should be quoted, as MySQL does not handle “VALUES (‘foo’,,’bar’)”
and throws an error.

Comments are closed.