Comments on: One Million Tables in MySQL 8.0

By: y-taka

y-taka — Tue, 19 Dec 2017 08:38:12 +0000

Hi,

Your post is very interesting, I’m glad to know about new data dictionary.

Well, I would like to know how long MySQL 5.7 take to create one million tables compared below time.

> That script works perfectly in MySQL 8.0.1-dmr and creates one million tables in 25 minutes and 28 seconds (1528 seconds).

Regard,

By: billkarwin

billkarwin — Tue, 03 Oct 2017 23:54:44 +0000

In reply to Dmitry Lenev. At my previous company, we had to manage over 160,000 tables per MySQL instance, because we separated customer data into one schema per customer (for security and some manageability). The workload was not strictly uniform, because different customers used the service at different rates. But their usage was unpredictable.

By: Jaime Crespo

Jaime Crespo — Tue, 03 Oct 2017 08:39:56 +0000

I would like to see more benchmarks like this on more practical queries about the new data dictionary. Things like https://www.percona.com/blog/2008/02/04/finding-out-largest-tables-on-mysql-server/ or typical metadata-querying information.

By: Federico

Federico — Mon, 02 Oct 2017 12:53:33 +0000

Thank Alex, very interesting. I was wondering if you are testing performance differences with information_schema.TABLES and similar system tables.

Federico

By: Dmitry Lenev

Dmitry Lenev — Mon, 02 Oct 2017 09:38:35 +0000

Hello!

Regarding “Opening tables” state.

Yes, after .frm files removal we no longer need to open them when table definition is loaded into table definition cache and TABLE_SHARE object is constructed. However we still need to construct TABLE_SHARE object from the information in the data-dictionary. Plus we still need to construct TABLE object and create “handler” object and “open” table in SE (even though this might not lead to opening of files in reality) for each table instance used by some statement (if it is not in open tables cache already).

As you have mentioned both table definition cache and open tables caches are limited to 524288 elements in your case. So test which tries to access 1mil of tables randomly will result in lots of cache misses (and effect will be even worse then one might expect due to open tables cache partitioning).

But do we really have workloads which access 1mil of tables uniformly randomly in practice?
Without some kind of temporal or connection locality at all?