Mysql – increasing disk space for thesql data volume

disk-spaceMySQL

I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the data directory is mounted on to logical volume.

--- Logical volume ---
LV Path                /dev/mysql/data
LV Name                data
VG Name                mysql
LV UUID                UNse6Y-QgCX-Ap6D-UXvr-VXs5-r8dc-g91Vhn
LV Write Access        read/write
LV Creation host, time localhost, 2012-09-19 15:22:49 +0100
LV Status              available
# open                 2
LV Size                200.00 GiB
Current LE             51200
Segments               1
Allocation             inherit
Read ahead sectors     auto
- currently set to     256
Block device           253:3

And this is mounted under /data directory.

/dev/mapper/mysql-data  /data                   reiserfs        defaults,noatime,nodiratime,notail,data=writeback 0 1

Unfortunately, the binary logs are located inside the the /data directory.

# grep -i log-bin /etc/my.cnf 
log-bin                 = /data/binlog/mysql-bin.log

I am looking for the best option to increase the disk space and or moving the binary logs to
a separate disk location without having to restart mysql or with minimal downtime?

The database version is:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

Is there any good solution? Please help.

Best Answer

You say you have bin logging enabled. This leads me to believe you have replication running.

To minimize down time you could promote your slave to master. The finer details on how to do this really depends on your setup. Maybe it will involve updating application configurations, maybe it will involve changing the DNS to your "master server".

Prior to the promotion ensure you slave is configured they way you would like, e.g. not writing binlogs to the data directory. Make sure all the grants present on the master are on the slave. Make sure the slave is not running in read only mode when you are ready to bounce.

Prior to promotion you'll want to ensure both the master and slave are in read only mode. Run show master status; on the slave to see what log file and position you'll use for change master to after you reconfigure and restart the old master to run as a slave.

Once the old slave is promoted to the new master you can take your time to move the old binlogs to a new file system and reconfigure to keep new logs out of the data dir.

Related Solutions

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before

MySQL database drop insanely slow

I hate the checking permissions issue.

You may have to disable key checks before the DROP DATABASE

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

UPDATE 2013-04-15 18:04 EDT

I just noticed you have innodb_file_per_table OFF. What gives ?

You currently have all the InnoDB data and the corresponding index sitting in a single file.
Any CREATE TABLE statement must make data dictionary updates and look for space (small but annoying in this instance)
Internal Fragmentation of ibdata1
Dropping a table means scanning the table and its indexes for availability to lock. With data and index pages possibly fragmented, this takes spindles, seek time, and latency.
See Pictorial Representation of ibdata1 to see everything that goes into ibdata1

Recommendation : Remove all Data and Index Pages from ibdata1

This will give ibdata1 a breather to handle just data dictionary and MVCC management. In addition, ibdata1 will stay rather lean and mean and can be read more quickly.

You will need to perform the InnoDB Infrastructure Cleanup. I wrote out all the steps back on October 29, 2010 in StackOverflow.

UPDATE 2013-04-22 08:10 EDT

Three suggestions

SUGGESTION 1 : I just noticed something else. You are using an ancient version of MySQL (5.0.45). You should think about upgrading to MySQL 5.6.11 as it performs significantly faster that MySQL 5.5 and way faster than MySQL 5.0.

SUGGESTION 2 : You should also go ahead and implement the InnoDB Infrastructure Cleanup.

SUGGESTION 3 : You should also check the disk itself. If the data is sitting on a RAID10 set, one of the disks may have an issues. Check the disk controller's battery as well because it can slow down disk caching and affect read performance.