MySQL Bugs: #47558: InnoDB with many concurrent update threads eventually causes "table full" errors

Bug #47558	InnoDB with many concurrent update threads eventually causes "table full" errors
Submitted:	23 Sep 18:02	Modified:	21 Nov 17:22
Reporter:	Evan Jones
Status:	Need Feedback
Category:	Server: InnoDB	Severity:	S3 (Non-critical)
Version:	5.4.3-beta	OS:	Linux (Ubuntu 9.04 (Jaunty); Kernel 2.6.29)
Assigned to:		Target Version:

Description:
Summary: Running many concurrent UPDATEs eventually leads to a spurious "table full"
error. This is a spurious error because simply retrying the operation will typically
cause it to succeed. Setting innodb_max_purge_lag seems to fix the problem.

This may seem to be a configuration error and not a bug, but  MySQL's default
configuration causes a "table full" error when the table isn't really full. I spent a
very frustrating day trying to figure this out. I can only reproduce this when running
with InnoDB tables in memory. While it seems unlikely that "real" users will run into
this issue in the near future, it seems possible that with solid state disks and
multi-core machines, others might.

How to repeat:
Configuration details (my.cnf below; if you want my scripts that create the db and run
the test, I can provide them):

* MySQL 5.4.2-beta compiled from source, using built-in InnoDB (reproducible with MySQL
5.1.39)
* data files are stored in tmpfs (/dev/shm) because I'm trying to do some benchmarks
without worrying about slow disks (I'm a database researcher at MIT; I'm not trying to
advocate this as a production config; I'm using InnoDB because I want transactional
semantics which the MEMORY engine does not provide)
* trying to turn off as many disk writes as possible (data is on a ram disk after all)
* Running on an 8 core Intel Nehalem (Xeon 5540), which has 16 "virtual CPUs" thanks to
hyperthreading

Database contains 64 tables created with:

CREATE TABLE table00 (id INTEGER PRIMARY KEY, counter INTEGER);
INSERT INTO table00 VALUES (0, 0);

I then spawn a varying number of client threads, each of which executes the following as
a prepared statement, operating on its own table (table00 through table63):

UPDATE table16 SET counter = counter + 1 WHERE id = 0;

When running with approximately ~20 threads, I typically get a "table full" error pretty
quickly.

Important my.cnf configuration details:

innodb_buffer_pool_size=256M
innodb_log_file_size=64M
innodb_data_file_path=ibdata1:256M
innodb_thread_concurrency = 0
# It is harder to reproduce with =1 or =2, but still seems to happen
innodb_flush_log_at_trx_commit=0
# This seems to fix the issue:
# innodb_max_purge_lag=1000000

Suggested fix:
easy fix: change the default for innodb_max_purge_lag to be some value > 0

hard fix: change InnoDB's internals so that it knows the "out of space" error is being
caused by a lagging purge thread, and it can wait to avoid the error.

Thank you for the bug report.

"if you want my scripts that create the db and run the
test, I can provide them". Yes Please provide them. Thanks in advance.

Scripts, configuration, and client program used to reproduce this bug

Attachment: dbscale.tar.bz2 (application/x-bzip2, text), 9.48 KiB.

There are a bunch of moving parts, so I've attached a .tar.bz2. Or you can browse them on
the web:

http://people.csail.mit.edu/evanj/dbscale/

Description of files:
my.cnf - My mysql configuration
createdb.py - Creates a new mysql database
mysql_build.sh - Configuration used to build the MySQL server
mysqlclient.cc - Runs the client in many threads
scale.py - Creates databases and runs the client with many threads, recording information
as it goes

Example reproduction, using my local mysql install in /home/evan/mysql

# Comment out the innodb_max_purge_lag setting in my.conf to get the error
# Create an empty database in /dev/shm, using my local mysql installation
./createdb.py --basedir=/home/evan/mysql /dev/shm/mysqldata
# Start MySQL in one terminal
/home/evan/mysql/libexec/mysqld --defaults-extra-file=/dev/shm/mysqldata/my.cnf
--basedir=/home/evan/mysql --datadir=/dev/shm/mysqldata/data
# Build and run test in another terminal
# This should crash, reporting the table full error
PATH=/home/evan/mysql/bin:$PATH make
./mysqlclient 20 /dev/shm/mysqldata/data/mysql.sock

Thank you for the feedback.

I have syntax errors when trying to run created.py script you provided:

$./createdb.py 
Traceback (most recent call last):
  File "./createdb.py", line 5, in ?
    import dbscale
  File "/Users/apple/Documents/web_project/MySQL/bugs/bug47558/dbscale/dbscale.py", line
37
    value_string = ", ".join(str(v) for v in values)
                                      ^
SyntaxError: invalid syntax

$python --version
Python 2.5.2

...

$/usr/local/bin/python3.1 ./createdb.py --basedir=/Users/apple/Applications/mysql-azalea/
`pwd`/testdb
Traceback (most recent call last):
  File "./createdb.py", line 5, in <module>
    import dbscale
  File "/Users/apple/Documents/web_project/MySQL/bugs/bug47558/dbscale/dbscale.py", line
9, in <module>
    import mysql
  File "/Users/apple/Documents/web_project/MySQL/bugs/bug47558/dbscale/mysql.py", line
44
    print proc.stdout.read()
             ^
SyntaxError: invalid syntax

Which version of Python do you use?

new version: Scripts, configuration, and client program used to reproduce this bug

Attachment: dbscale.tar.bz2 (application/x-bzip2, text), 9.47 KiB.

Weird. I'm using Python 2.6.2, but I've also tested it with Python 2.5.2. I just made some
changes and it now works with Python 2.4.5 as well on my Linux system, so hopefully the
updated version will work for you? If not, maybe /usr/bin/python is different than the
"python" on your path, and running "python2.5 createdb.py" might work?

I just double checked: the directions I posted above reproduces the issue on two Linux
systems: 8 cores and 16 cores. However, I can't reproduce it on my single core
workstation, so you probably need a system with a number of CPUs. And using a RAM disk is
critical. It probably won't be reproducible on Mac OS X, unless there is some RAM disk
functionality I don't know about.

Additional steps to reproduce:

1. Edit dbscale/my.cnf and uncomment the "innodb_data_file_path" line (bug happens faster
on fixed size innodb table, otherwise the table just grows and grows until /dev/shm is
full, which takes longer)
2. Edit dbscale/my.cnf and comment the "innodb_max_purge_lag" line (prevents the bug)
3. Follow steps above, but use "mysqlclient 30 socketpath" to run more threads
(reproduces faster with more threads)

Thank you for the feedback.

I can create test case, but can not repeat described behavior. Please provide configure
options you used when compiled server.

Compiled with the following (mostly taken from Debian/Ubuntu's set of flags):

CXX=gcc CFLAGS="-O3 -DBIG_JOINS=1 -fno-strict-aliasing -march=native" ./configure
--prefix=/home/evan/mysql --enable-shared --enable-static --enable-thread-safe-client
--enable-local-infile --with-pstack --with-fast-mutexes --with-big-tables --without-docs
--with-plugins=max-no-ndb

Please, try to repeat with a newer version, 5.4.3, and inform about the results.

Tried with 5.4.3-beta. Table full error still occurs for many concurrent updates.

I noted this:

innodb_data_file_path=ibdata1:256M

so, single data file can not grow. Can you, please, try to make it 10 times bigger or
allow it to grow, and then check if this will have any influence on testing results?