MySQL Forums
Forum List  »  InnoDB

Re: How to reduce ibtmp1 file in mysql 5.7?
Posted by: Magi Home
Date: April 08, 2016 12:38AM

Thanks fo answer!

But, in /var/log/mysql/error.log

[ERROR] InnoDB: Tablespace innodb_temporary ran out of space. Please add another file or use 'autoextend' for the last file in setting innodb_temp_data_file_path

[ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_2' is full [ERROR] /usr/sbin/mysqld: The table '/tmp/mysql/#sql_c76e_3' is full


mkdir /tmp/mysql
chown mysql:mysql /tmp/mysql
service mysql restart


Rights are, but in /tmp/mysql/ no files.

In mysqld.cnf

InnoDB section

innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 5M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_log_buffer_size = 8M
innodb_max_undo_log_size = 50M
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:512M

If you remove the limitation to 512 M, the file fills all the available space in a few minutes.

innodb_data_file_path = ibdata1:10M:autoextend:max:512M

innodb_thread_concurrency = 8
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_pct = 75
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_io_capacity = 600
innodb_io_capacity_max = 1000
innodb_read_io_threads = 32
innodb_write_io_threads = 16

innodb_fast_shutdown = 0
innodb_max_dirty_pages_pct = 0

df -h


udev 5,0G 0 5,0G 0% /dev
tmpfs 1,0G 110M 915M 11% /run
/dev/sda2 35G 22G 12G 65% /
tmpfs 5,0G 0 5,0G 0% /dev/shm
tmpfs 5,0M 0 5,0M 0% /run/lock
tmpfs 5,0G 0 5,0G 0% /sys/fs/cgroup
/dev/sda1 511M 3,4M 508M 1% /boot/efi
tmpfs 1,0G 0 1,0G 0% /run/user/1000

How to fix this error?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to reduce ibtmp1 file in mysql 5.7?
6662
April 08, 2016 12:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.