Description:
I restore a 7G table from mysqldump and return the error as below.
"Error writing file '/u01/mydata/ogepsintt1/tmp/MY1fnqpm' (Errcode: 28)"
Checked and found the tmp directory, which has 3G space, full during restore.
I tried to make a table without index and insert about 7MB data to the table by a simple insert statement. I found the tmp directory usage raise about 7MB during insert and I am sure that there is no other action is perform on DB.
According to below link, insert statement will not create any tmp table. I am not sure if there any misconfig or another reason cause the use of tmp table
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html#internal-temporary-...
below listed the my.cnf.
[mysqld]
#basedir = /u01/app/mysql/usr
basedir = /u01/app/testm1intt1/usr
datadir = /u01/mydata/testm1intt1
#plugin_dir = /u01/app/mysql/usr/lib64/mysql/plugin
plugin_dir = /u01/app/testm1intt1/usr/lib64/mysql/plugin
bind-address = 192.169.0.01
port = 9306
user = testm1int
socket = /u01/mydata/testm1intt1/mysql.sock
pid-file = /u01/mydata/testm1intt1/mysqld.pid
log-error = /u01/mydata/testm1intt1/mysqld.err
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /u01/mydata/testm1intt1/mysqld-slow.log
general_log_file = /u01/mydata/testm1intt1/mysqld.log
tmpdir = /u01/mydata/mytmp
#Test InnoDB tablespace encryption
early-plugin-load = keyring_file.so
keyring_file_data = /u01/mydata/testm1t1/mysql-keyring/keyring
#plugin-load =rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;auth_socket=auth_socket.so
plugin-load =rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so;auth_socket=auth_socket.so;audit=libaudit_plugin.so;
autocommit = 0
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 64M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
sql_mode=no_engine_substitution
character-set-server = utf8mb4
character-set-client-handshake = false
#general-log = 1
ignore-db-dir = undotbs
ignore-db-dir = lost+found
table_open_cache_instances = 2
explicit_defaults_for_timestamp = true
lower_case_table_names = 1
transaction-isolation = read-committed
server-id = 1
binlog-do-db = ttest001
binlog-do-db = healthcheckdb
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-do-table = ttest002.dummy
replicate-ignore-table = healthcheckdb.write_test
relay_log_space_limit = 1073741824
auto_increment_increment = 2
auto_increment_offset = 1
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 1000
rpl_semi_sync_slave_enabled = 1
log-bin = /u01/myarch/testm1intt1/mysql-bin
binlog_format = mixed
max_binlog_size = 104857600
expire_logs_days = 3
default-storage-engine = innodb
innodb_data_home_dir = /u01/mydata/testm1intt1
innodb_data_file_path = systemts1:1G:autoextend:max:4G
innodb_log_group_home_dir = /u01/mydata/testm1intt1
#innodb_log_file_size = 50M
innodb_log_file_size = 100M
#innodb_log_files_in_group = 5
innodb_log_files_in_group = 10
innodb_file_per_table = 1
innodb_undo_directory = /u01/mydata/testm1intt1/undotbs
innodb_undo_logs = 128
innodb_undo_tablespaces = 1
innodb_file_format = barracuda
innodb_large_prefix = true
slow_query_log = 1
slow_query_log_file = /u01/mydata/testm1intt1/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
validate_password_length = 8
validate_password_mixed_case_count = 2
validate_password_number_count = 1
validate_password_policy = MEDIUM
validate_password_special_char_count = 1
optimizer_switch = derived_merge=off
# Added on 5.7.12
relay_log = mysqld-relay-bin
secure_file_priv = /u01/mydata/mytmp
innodb_open_files = 450
innodb_sort_buffer_size = 52428800
max_user_connections = 200
query_cache_type = 0
innodb_status_output_locks = on
slave_net_timeout = 3600
log_timestamps = SYSTEM
# Revised on 5.7.12
query_cache_size = 0
max_connections = 600
innodb_buffer_pool_size = 2G
#audit_offsets = 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964
innodb_temp_data_file_path = tmp/ibtmp1:512M:autoextend:max:3G
skip_slave_start = true
max_heap_table_size = 67108864
tmp_table_size = 67108864
audit_offsets = 7800, 7848, 3624, 4776, 456, 360, 0, 32, 64, 160, 536, 7964, 4352, 3648, 3656, 3660, 6048, 2072, 8, 7032, 7072, 7056
internal_tmp_disk_storage_engine = MYISAM
[client]
port = 19306
socket = /u01/mydata/testm1intt1/mysql.sock
default-character-set = utf8mb4
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
How to repeat:
I made a dummy table without any key on it.
mysql> create table healthcheckdb.test3 (col1 int(11), col2 varchar(30));
generate a patch of insert statement by below shell script.
#!/bin/bash
for i in {1..999999}
do
echo "insert into healthcheckdb.test3 values ($i, 'This is testing!!!!!');">>insert.sql
done
Then insert the data to the table, meanwhile using df to monitor the usage of the tmp directory, /tmp.
#> /u01/app/testm1intt1/usr/bin/mysql --defaults-file=/u01/mydata/testm1intt1/my.cnf -u user <insert.sql
you can see the tmp directory usage raise during insert.
