Caution : Hard Disk Full

Dear all,

Today by chance I am checking the space in mysql data directories.

/dev/sdd5 274G 258G 2.6G 100% /hdd4-1

In the next 2 days i know my space becomes empty due to increase in data
of myisam tables.

[root@sd-1 hdd4-1]# du -sh *
33G innodb_data1
33G innodb_data2
33G innodb_data3
33G innodb_data4
33G innodb_data5
33G innodb_data6
8.1G innodb_data7
16K lost+found
57G myisam_data
4.0K temp
[root@sd-1 hdd4-1]#


When it becomes full, I am sure my server down. Can anyone Please let me
know the steps I need to follow in this condition. Following are my
directories :
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 29G 12G 15G 45% /
/dev/sda1 99M 11M 84M 11% /boot
/dev/sda5 69G 35G 32G 52% /hdd1-1
/dev/sdb1 274G 225G 36G 87% /hdd2-1
/dev/sdc5 274G 225G 36G 87% /hdd3-1
/dev/sdd5 274G 258G 2.6G 100% /hdd4-1
/dev/sde1 266G 184G 70G 73% /hdd5-1


Thanks




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Caution : Hard Disk Full

----- Original Message -----
> From: "Adarsh Sharma"
>
> Today by chance I am checking the space in mysql data directories.

By chance? That should be automated, as should a million other standard checks. Install Nagios.

> When it becomes full, I am sure my server down. Can anyone Please let
> me know the steps I need to follow in this condition.

Pretty obvious: add space or remove data.

Given that you say it's MyISAM data taking up the space, you simply need to optimize the tables to reclaim free space inside the datafiles - but make sure there's enough free space for a full copy of the remaining data, so start optimizing the smallest tables first. For InnoDB it's quite a bit more trouble.

If there's no data you can delete, you could have a look at wether there's tables that don't need to be written to anymore - or set up archiving tables for exactly that purpose; you can convert those to compressed MyISAM, that should save quite some space, too.

Adding disks, well... if you set up the server with LVM or MD that shouldn't be too hard, but it looks like you didn't. Welcome to screwville. You'll need to swap out the disk for a larger one (yes, downtime) and copy all the data. That, or *add* a disk, set that one up with LVM, copy the data there and then add the old disk as a second physical volume and expand the logical volume.


And this, dear pupils, is why we tell the marketeers to go screw themselves until they can provide a three-year volume estimate.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Caution : Hard Disk Full

On Fri, August 26, 2011 00:44, Johan De Meersman wrote:
> ----- Original Message -----
>> From: "Adarsh Sharma"
>>
>> Today by chance I am checking the space in mysql data directories.
>
> By chance? That should be automated, as should a million other standard
> checks. Install Nagios.
>
>> When it becomes full, I am sure my server down. Can anyone Please let
>> me know the steps I need to follow in this condition.
>
> Pretty obvious: add space or remove data.
>
> Given that you say it's MyISAM data taking up the space, you simply need
> to optimize the tables to reclaim free space inside the datafiles - but
> make sure there's enough free space for a full copy of the remaining data,
> so start optimizing the smallest tables first. For InnoDB it's quite a bit
> more trouble.
>
> If there's no data you can delete, you could have a look at wether there's
> tables that don't need to be written to anymore - or set up archiving
> tables for exactly that purpose; you can convert those to compressed
> MyISAM, that should save quite some space, too.
>
> Adding disks, well... if you set up the server with LVM or MD that
> shouldn't be too hard, but it looks like you didn't. Welcome to
> screwville. You'll need to swap out the disk for a larger one (yes,
> downtime) and copy all the data. That, or *add* a disk, set that one up
> with LVM, copy the data there and then add the old disk as a second
> physical volume and expand the logical volume.
>
>
> And this, dear pupils, is why we tell the marketeers to go screw
> themselves until they can provide a three-year volume estimate.
>
I would only add, while you are in there, add two disks and use MD to
create raid 1 and the put LVM on top of that. With MD you can replace one
of the underlying drives with a larger one move that in and then replace
the other bring that in and increase the available space. BTW: on modern
processors, I've found MD to be faster than Hardware Raid unless you go
real high end. Plus you can put the disks on separate controllers. Not
a trivial exercise, but you are not locked it.
------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Caution : Hard Disk Full

--90e6ba6e827075c0b204aba9d405
Content-Type: text/plain; charset=ISO-8859-1

Hi,
I think I am too late.. if not for time being you can, move *
'57G myisam_data'* dir to drive '*/dev/sde1 266G 184G 70G 73% /hdd5-1
*' and make the soft link with proper permission. Note activity must be done
once database is shutdown.

and secondly, normally innodb do not release the space once data get
deleted. so its advise , to take backup and re-import the databses.
steps :
1) Stop writes to the server
2) Dump the data
3) drop all databases (except mysql)
4) stop mysqld
5) remove the innodb data and log files
6) start mysqld (the innodb tablespace should be automatically created)
7) Check the error logs for anything strange
8) import the data

Thanks,
Prabhat

On Thu, Aug 25, 2011 at 9:48 PM, Adarsh Sharma wrote:

> Dear all,
>
> Today by chance I am checking the space in mysql data directories.
>
> /dev/sdd5 274G 258G 2.6G 100% /hdd4-1
>
> In the next 2 days i know my space becomes empty due to increase in data of
> myisam tables.
>
> [root@sd-1 hdd4-1]# du -sh * 33G innodb_data1
> 33G innodb_data2
> 33G innodb_data3
> 33G innodb_data4
> 33G innodb_data5
> 33G innodb_data6
> 8.1G innodb_data7
> 16K lost+found
> 57G myisam_data
> 4.0K temp
> [root@sd-1 hdd4-1]#
>
>
> When it becomes full, I am sure my server down. Can anyone Please let me
> know the steps I need to follow in this condition. Following are my
> directories :
> Filesystem Size Used Avail Use% Mounted on
> /dev/sda2 29G 12G 15G 45% /
> /dev/sda1 99M 11M 84M 11% /boot
> /dev/sda5 69G 35G 32G 52% /hdd1-1
> /dev/sdb1 274G 225G 36G 87% /hdd2-1
> /dev/sdc5 274G 225G 36G 87% /hdd3-1
> /dev/sdd5 274G 258G 2.6G 100% /hdd4-1
> /dev/sde1 266G 184G 70G 73% /hdd5-1
>
>
> Thanks
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?**
> unsub=aim.prabhat@gmail.com
>
>


--
Best Regards,

Prabhat Kumar
MySQL DBA

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--90e6ba6e827075c0b204aba9d405--