Re: MySQL / SNMP

[Stewart Smith]

On Thu, 2006-06-22 at 21:50 +0200, Michel Stam wrote:
> Ok I am trying to use my_sys.h as much as I can. Not sure if I can use 
> NDB portlib if people do not compile in ndb support?

not sure if we export it in the ndbapi lib... probably not :) 

> I am probably going to need a few functions in the range of memset, 
> strstr and strtoll/strtoull.

best judgement - that's what we end up doing sometimes internally. 

>  >>Also, I assume that the MySQL SNMP OID I mentioned in an earlier mail
>  >>is
>  >>currently not used by anyone in MySQL? (this to prevent any nasty
>  >>conflicts later on). If not, I may need to register a new one (which
>  >>takes approximately 4 weeks).
>  >>
>  > I don't know. We can chase that up a bit in the future.
> 
> Ok, I'm using it right now, but it can be changed in under 2 minutes 
> (its a constant in a header file).

great!

> >In the future we'll be exporting more things in the
> >INFORMATION_SCHEMA.FILES table. currently we have free extents for disk
> >data. more, per table information is planned for sometime in the future.
> >
> >having all of this from SQL IMHO is a good idea.
> 
> Ok, in that case I'll not do anything right now and assume the
> maximums. This can be added later on, when the information_schema
> table is available

My current thoughts for what to add to the I_S.FILES table are below.
Note that this has not gone through *any* review yet (apart from a quick
"looks good" from some other devs) :) 

also, this wouldn't be added to anything before 5.1.

Add reporting of IndexMemory and DataMemory usage (per table) to
INFORMATION_SCHEMA.FILES.

Varsized attributes are allocated in a separate set of pages in 5.1. so there
are two places to get usage from.

In NDB, IndexMemory and DataMemory usage can be different on each node. This is
less of a problem in 5.1 as we don't support initial NR as a form of repacking
anymore.

See WL#1359 for details of columns.

I propose we have the following "files" with the following behaviour:

foreach node:
  FILE_NAME=NULL
  FILE_TYPE="INDEX"
  TABLESPACE_NAME="IndexMemory"
  FREE_EXTENTS= number of free IndexMemory pages for node X
  TOTAL_EXTENTS= total number of IndexMemory pages
  EXTENT_SIZE=size of IndexMemory page
  EXTRA= "NODE=X" (where X is node id) - format as for DATAFILES and UNDOFILES.
  - other columns are NULL

  same for DataMemory

these rows exist so that you can get reports when there are no tables in NDB

for each NDB table:
  a row for IndexMemory and DataMemory as above, but DataMemory is split into
two parts (i.e. THREE rows per table)
    - FILE_NAME="Fixed Sized Columns", TABLESPACE_NAME="DataMemory"
    - FILE_NAME="Variable Sized Columns", TABLESPACE_NAME="DataMemory"
  - TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME for the table
  - ROW_FORMAT (fixed for fixed column part, variable for varsized part)
  - TABLE_ROWS is row count for the table
  - AVG_ROW_LENGTH (constant for fixed, possibly estimate for varsize)
  -  FREE_EXTENTS if we know we have free extents, number. else NULL
  -  TOTAL_EXTENTS= total number of pages allocated to this table
  -  EXTENT_SIZE=size of page
  - DATA_LENGTH actual space used, in bytes. NULL if we cannot easily find this out.
  - MAX_DATA_LENGTH size we can be if we use all pages allocated to us. 
  - INDEX_LENGTH, always NULL - even for indexmemory.
  - DATA_FREE (in bytes) the free space inside the pages allocated to us.
possibly an estimate.
  - EXTRA: node id as per DATAFILE. also OVERHEAD - the overhead per page.

In the case of BLOB tables and Index tables:
- another row for index/data memory but with FILE_NAME being the name of the
index/blob table

> >great - this was the purpose of ndb_size.pl too.
> 
> I added it to SNMP as I thought it would be a nice feature to have. 
> Especially since some programs, like Zabbix, can plot very nice graphs 
> of SNMP values, thus showing you how fast your database runs full.

If it's possible (and easy) having it easy to plug in extra queries to
be exported via SNMP could be useful.

> Ok, I'll await the information; Right now it isn't really important 
> whether its fully integrated or not. Getting a version public is.
> Other than a few mysql/ndb/mgmapi/ include files which are missing by 
> default after a 'make install' on the base mysql source tree 
> (mysql/ndb/ndb_global.h and mysql/ndb/ndb_types.h if I remember 
> correctly), the snmp module quite happily compiles outside of the MySQL 
> source tree right now.
> I will look into that a little later on and submit a patch, if its not 
> already fixed.

great. we should fix this if it's a current problem.

> In my case, of course,  the module code doesn't touch any of the server 
> code (monitoring applications should affect the systems they monitor 
> very little, or preferably, not at all).

great. although we should expand support for monitoring applications
where appropriate.

> I'd welcome any review of the code, of course.

look forward to reviewing it.

thanks,
stewart
-- 
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]