Jump to content


Photo

Alert When Database Is Full ?


  • Please log in to reply
6 replies to this topic

#1 Bambid

Bambid

    Member

  • Members
  • PipPip
  • 14 posts

Posted 14 January 2009 - 04:11 PM

Is there any possibility to get alerted, when database is full and/or is 98 % full ?


Thanks



David
Regards



David

#2 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 14 January 2009 - 07:16 PM

When is a database full? The available disk space can be full!

We had similar requests and questions lately. Currently we do not support this. The 'Disk Info' option in MONyog is a interactive feature only (the information will display when and only when some connected browser requests it), but there is no background thread running retiriving the 'Disk Info' data and no 'MONobjects' implemented that can be used in scripting.

But it seems that users want this option in order to define an alert for size of data (for the whole server, a database or a table) to exceed a certain level. This will require one more thread running and will have 'cost' but after all we have computers to use them - not to spare them! So please let us discuss this. I think we will have to schedule the request in our future plans! I think there is no fundamental difficulty in implementing it!
Computers make your grey hair come off ....

Peter Laursen
Webyog

#3 Bambid

Bambid

    Member

  • Members
  • PipPip
  • 14 posts

Posted 14 January 2009 - 11:36 PM

When is a database full? The available disk space can be full!


This can happen, when you use InnoDB and set for example
ibdata:1G:autoextend:max:3G

Regards



David

#4 Boyd Hemphill

Boyd Hemphill

    Member

  • Members
  • PipPip
  • 26 posts
  • Gender:Male
  • Location:Austin, Texas (USA)

Posted 19 February 2009 - 01:14 PM

This can happen, when you use InnoDB and set for example

ibdata:1G:autoextend:max:3G


This is a good idea on its face and I would like to see it implemented at some point, but it is not at all straight forward.

Even when using innodb "full" is relative, here is why:
In the example above I believe the autoextend increment it 5M by default (unless you have it set elsewhere in your file). So the idea seems to be straightforward. So, let's assume that the db has allocated 2.9G to the tablespace at this point which triggers a "disk full" alert. Right after a very large delete takes place. Innodb does not recover space (no "coalese" for you Oracle folks). So even though there is a bunch of empty space within the tablespace, there would appear to be a problem.

So rather than looking at it as a "database full" issue I suggest the following:
1. Innodb: tablespace within n% of maximum allowed size (as defined by the max directive in the example above)
The problem here is that there can be mulitple tablespaces and, even worse, a tablespace per table if that option is selected. Anyone with a clever idea, please speak up!

2. The size of the mysql data directory

3. The size of the mysql logs directory (binary,general,slow,relay, but not innodb)

4. The size of the mysql instance on disk as compared to the free space on the devices.
This too gets a bit hairy b/c in my situation my logs are one one RAID array while my data is on another. I think the combination of a linux "df" with the location of the data and log files could break this down correctly, but the Monyog guys would have to let us know there.

5. The free space on each device.
While this seems identical to (4) above, it really is different because mysql could be sharing the host with any number of other processes that consume space. In particular a web server that allows the upload of large files.

Right now we have Zabbix watching our disk usage, but since we dedicate a box to mysql it would be great to get this info in Monyog.

#5 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 20 February 2009 - 12:26 PM

Thanks @Boyd for commenting on this.

I can add that with engines supporting TABLESPACE parameter in CREATE statement (NDB and FALCON) there are even more problems! You can have 20 databases with each 500 tables distributed randomly in 7 tablespaces (at least as I understand it)!

So 'disk is full' would mean that "physical files are close in size to defined value" AND "there is no free space in at least one tablespace". Agreed?

And actually I am not sure if MyISAM files (.MYD and .MYI) will shrink or not if you delete data or drop an index. I think (but not sure) that each of of these files behave identically to the InnoDB tablespace (free space is not reallocated to disk). Same will probably apply to ARCHIVE, FALCON, PBXT etc. database files.
Computers make your grey hair come off ....

Peter Laursen
Webyog

#6 dl4ner

dl4ner

    Newbie

  • Members
  • Pip
  • 1 posts

Posted 27 April 2009 - 12:53 PM

there is another possibility where a table can get full.
It is the case when you are using HEAP tables.

my.cnf:
[mysqld]
max_heap_table_size = xxxM

Possibility to check:
[codebox]foreach (db) {
foreach (table) {
show table status like "tablename"
compare Data_lengt/Max_data_length
}
}[/codebox]

btw: if there is a crashed table, then max_data_length will be 0 (zero), so
and you'll find more info in the "Comment" field of the show status request.

but be careful: such a table-scan can take a lot of time, if you have many DBs and many tables.
(just implemented this in another script (besides monyog)...

regards.

Werner Maier

#7 peterlaursen

peterlaursen

    Advanced Member

  • Admin
  • PipPipPip
  • 7,869 posts
  • Gender:Male
  • Location:Skagen, Denmark
  • Interests:well ... jazz/folk music, photography, chess, nature, ecology, history, bicycling, Highland Malts ... well, Lowland Malts and Cognac too actually :-) just wonder how I get the time to touch a computer! SQLyog and MONyog? no that's not interest, that's BASIC NEEDS simply!

Posted 28 May 2010 - 07:05 PM

foreach (db) {
  foreach (table) {
    show table status like "tablename"
    compare Data_lengt/Max_data_length
  }
}




What language is this (I think C?).  MONyog connects with - like any client - the MySQL API (SQL) and not does not have shell (or extended shell access .. ie. Perl-based etc)  to MySQL. 

Please explain this in SQL -terms/MySQL API terms.
Computers make your grey hair come off ....

Peter Laursen
Webyog




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users