范德萨发而为
全部博文（392）
分类： Mysql/postgreSQL
2012-11-22 09:51:35
http://blog.csdn.net/kangquan2008/article/details/7547334
今天插入了大量的数据到数据库，结果查到一半不动了。比较奇怪，查了磁盘，结果显示满了：
[testuser@rendering mysql]$ df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/sda1 ext3 24G 23G 0 100% /
/dev/sda3 ext3 420G 46G 353G 12% /export
tmpfs tmpfs 7.9G 0 7.9G 0% /dev/shm
tmpfs tmpfs 963M 3.0M 960M 1% /var/lib/ganglia/rrds
glusterfs#rendering:/test413
fuse 16G 4.6G 9.9G 32% /mnt/test413
glusterfs#rendering:/shareweb
fuse 32G 26G 3.9G 88% /mnt/shareweb
glusterfs#rendering:/iozonetest
fuse 1.3T 65G 1.2T 6% /mnt/iozonetest
glusterfs#rendering:/iozonetestreplicated
fuse 432G 9.8G 400G 3% /mnt/iozonetestreplicated
查看了mysql 的日记的地点：
mysql> show variables like '%log%';
+-----------------------------------------+------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------------------------+
| back_log | 50 |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| binlog_stmt_cache_size | 32768 |
| expire_logs_days | 0 |
| general_log | OFF |
| general_log_file | /var/lib/mysql/rendering.log |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | /var/lib/mysql/rendering.scut.edu.cn.err |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_relay_log_size | 0 |
| relay_log | mysql-relay-bin |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/rendering-slow.log |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sync_binlog | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
+-----------------------------------------+------------------------------------------+
41 rows in set (0.00 sec)
查看错误日记：（果然是磁盘慢了的原因，磁盘满了，mysql默认会隔一定时间去重试看是有空间写入）
vi /var/lib/mysql/rendering.scut.edu.cn.err
120508 15:40:03 [ERROR] /usr/sbin/mysqld: Disk is full writing './mysql-bin.000008' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
120508 15:40:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 15:50:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:00:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:10:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:20:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:30:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:40:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 16:50:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:00:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:10:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:20:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:30:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:40:03 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 17:45:03 [ERROR] Error writing file 'mysql-bin' (errno: 28)
120508 17:47:04 [Warning] IP address '121.12.125.104' could not be resolved: Name or service not known
120508 17:55:59 [ERROR] /usr/sbin/mysqld: Disk is full writing './mysql-bin.000008' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)
120508 17:55:59 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
120508 18:05:59 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted in 600 secs
查看mysql 数据的位置：
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /var/lib/mysql/ |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| lc_messages_dir | /usr/share/mysql/ |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+
11 rows in set (0.00 sec)
到mysql的数据目录去查看哪些文件占用了最多的空间：
[root@rendering mysql]# ls -alh
total 9.8G
drwxr-xr-x 9 mysql mysql 4.0K May 8 14:30 .
drwxr-xr-x 37 root root 4.0K May 3 13:13 ..
-rw-rw---- 1 mysql mysql 1.8G May 8 17:45 ibdata1
-rw-rw---- 1 mysql mysql 5.0M May 8 17:45 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M May 8 17:45 ib_logfile1
drwx------ 2 mysql mysql 4.0K Apr 20 23:55 mysql
-rw-rw---- 1 mysql mysql 1.1G Apr 29 19:20 mysql-bin.000001
-rw-rw---- 1 mysql mysql 1.1G Apr 29 20:28 mysql-bin.000002
-rw-rw---- 1 mysql mysql 1.1G Apr 29 21:37 mysql-bin.000003
-rw-rw---- 1 mysql mysql 1.1G May 5 14:10 mysql-bin.000004
-rw-rw---- 1 mysql mysql 1.1G May 5 15:18 mysql-bin.000005
-rw-rw---- 1 mysql mysql 1.1G May 5 16:26 mysql-bin.000006
-rw-rw---- 1 mysql mysql 1.1G May 8 14:30 mysql-bin.000007
-rw-rw---- 1 mysql mysql 1002M May 8 17:45 mysql-bin.000008
-rw-rw---- 1 mysql mysql 152 May 8 14:30 mysql-bin.index
srwxrwxrwx 1 mysql mysql 0 Apr 21 23:41 mysql.sock
-rw-r--r-- 1 root root 6 Apr 20 23:55 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Apr 20 23:55 performance_schema
-rw-rw---- 1 mysql root 32K May 8 17:47 rendering.scut.edu.cn.err
-rw-rw---- 1 mysql mysql 5 Apr 21 23:41 rendering.scut.edu.cn.pid
-rw-r--r-- 1 mysql mysql 698 Apr 20 16:55 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql 349 Apr 20 16:55 RPM_UPGRADE_MARKER-LAST
drwx------ 2 mysql mysql 4.0K May 8 14:27 scutdb
drwx------ 2 mysql mysql 4.0K Apr 4 00:08 silverlight
drwx------ 2 mysql mysql 4.0K Apr 30 13:06 taa
drwx------ 2 mysql mysql 4.0K Mar 30 10:30 test
drwx------ 2 mysql mysql 4.0K Apr 21 00:07 test_partition
可见是mysql-bin占了大头，mysql-bin是主从复制时产生的bin日记（在从服务器上还有relay-bin日记）。先把这些日记删掉：
reset master；
其后log-bin被删除。
数据库的数据是在MyISAM引擎是在 .MYD 文件中，在InnoDB 则由指定：| innodb_data_file_path | ibdata1:10M:autoextend | (表结构都是 .FRM)
mysql> show variables like '%InnoDB%'
-> ;
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
