Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.

EDIT:

Server_01

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
1876765

but this also happened on server with much less tables:

Server_02

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
49514

I was using this query to list all databases but as it didn't work with tmpfs I've just replaced it with simpler one (SHOW DATABASES...).

I was watching disk space on /tmp mounted with tmpfs and there still was a plenty of space so I'm not sure how it could run out of space?

Basically I've got a problem with logical backups on the server with ~8000 DBs - it takes many hours (~24) to complete this task. I've created a simple BASH script (please see below) and instead of mysqldump I'm using mydumper as initial tests showed that it's much faster.

Backups running very fast initially and then slowing down dramatically:

Since ext3 has a default block size of 1K, while ext4 has 4K, perhaps the 4K blocksize may have allowed for more data. This, in itself, is not the real problem.

ASPECT #2: YOUR QUERY

If you have tens of thousands of tables, the query you are running looks quite demanding. In fact, the query says "Get me all databases that have 1 or more tables in them." If you have a large number of tables, you could be looking at a rather large temp table in order collect table_schema values before doing the DISTINCT.