Re: MySQL / SNMP

[Stewart Smith]

On Tue, 2006-06-20 at 12:10 +0200, Michel Stam wrote:
> >was this MySQL Cluster (as in NDB) or a bunch of replication slaves?
> 
> Actually its MySQL cluster 4.1. Old, but unfortunately I'm not able to 
> upgrade the production servers on a regular basis.

great! (he says as he's on the cluster team)

> >there's utilities such as mytop, nagios etc. too.
> 
> I know, but products such as HP OpenView talk SNMP; as its a 
> standardised protocol I figured SNMP would be a good choice (the 
> protocol was specifically meant for this).

yeap - sounds like you've gone beyond what these products currently
offer.

> >I'd be interested to see your version - as no doubt would others. Is it
> >a patch to the mysql server or a separate process?
> 
> I'm currently cleaning it up and making it a little more idiot proof.
> (such as when you kill the mysqld process, or all of the management 
> nodes in a cluster).

sure, understandable.

> The daemon is a seperate process, I thought this would be better. 

yes, I think so too. 

Do you deal with redundancy? i.e. can you have more than one of your
processes running and still get sane SNMP stuff out?

> Especially since I use the standard mysqlclient libs, and the ndbclient lib.

great!

> This way it should be possible to compile it on a version anywhere from 
> 4.1 to 5.1beta (I develop on 4.1, but I'll try to check this before I 
> submit the code).

within some limits.... there's been some API changes, but possibly
nothing that would affect a tool such as this. Should just get compile
failures IIRC.

Feel free to ask about anything you're unsure of.

> What it currently does is export a large number of the show global and 
> show status commands, list the cluster state (if configured) in the same 
> way ndb_mgm does, show the process list, the table status for all tables 
> in the system, and as an extra, I've coded the ndb_size.pl script into C 
> which gives a fairly accurate resource usage for tables if they were 
> stored on a cluster database (shown per table, unfortunately not 
> databases). Including BLOBS and ENUMS.

coding ndb_size.pl into C - brave man :)

I've made some updates to ndb_size.pl recently... perhaps it's better to
call out to the perl and have the perl script produce easier to parse
and mutilate output? I'd welcome your thoughts.

I've been thinking that XML is perhaps an option as then it's easy to go
to XHTML and people can use it in their MS Office/OpenOffice docs for
calculations and the like.

> LOBS are currently giving me a bit of an issue though, as parsing the 
> column sizes drastically degrades performance (for example by selecting 
> the average sizes using a query such as SELECT AVG( LENGTH( col ) ) FROM 
> database.table like the perl script does). MYSQL_FIELD structures only 
> provide me the maximum size of the BLOB column, which would report 
> memory usage way off from the actual situation. If anyone has any 
> thoughts on this, please let me know.

We should be able to (for cluster tables) talk to the BLOB table to get
some stats we maintain internally.

> As the underlaying SNMP engine I use Net-SNMP (see 
> http://www.net-snmp.org/). I hope that is not a problem.

I had a look at this a little while ago and it seemed like a sane choice
(seems to be the standard).

> I'll keep you posted.

great!

I'm looking forward to seeing it. It sounds like something we'd like to
incorporate into the distribution, it'd be cool if you/you're employer
are open to that (we can discuss details later)
-- 
Stewart Smith, Software Engineer
MySQL AB, www.mysql.com
Office: [phone] Ext: 6616
VoIP: 6616@stripped
Mobile: [phone]

Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html

Attachment: [application/pgp-signature] This is a digitally signed message part signature.asc

Attachment: [application/pgp-signature]