You could say: what could be the reason for having really big number of tables? Just design the application properly! It’s not always that easy. And this post isn’t really about arguing whether having many tables is good or not, it’s about what happens in terms of memory usage if you already reached that point.
Btw what do I mean by *many*? From my experience it’s tens of thousends or even millions rather than hundreds.
The inspiration for me to write this post was strong desire to try out the latest declared improvements in that area announced to be done in MySQL 5.6. _1
What I did was a very simple test where I loaded sql dump of databases and tables definitions only into different versions of MySQL. During the load time I was monitoring memory usage of mysqld process (RSS value from ps aux) as a function of tables number. Additionaly I compared disk usage in case you choose single innodb table space versus file per table, also in relation to number of tables.
Settings I used for test were all default and I chose lowest possible innodb buffer pool:
innodb_buffer_pool_size = 8Mexcept when I started to test Percona versions I realized that the lowest allowed value there was 32MB, this warning appeared in error log after the service start:
“[Warning] option ‘innodb-buffer-pool-size’: signed value 8388608 adjusted to 33554432″
So both Percona versions I tested had 32MB and all others had 8MB but I think it wasn’t that important in this particular test.
Additionally for Percona versions I used the
innodb_dict_size_limitvariable introduced already in 2009 _2.
The sample table set that I used has around 50 different tables with various number of fields and indexes.
Here are the results of creating more than a million tables:
I wonder what is the difference between the patch that Percona did few years ago (and which was available starting from 5.0.77-b13 of Percona Server) and latest fix available in MySQL 5.6.x branch. Any way It’s really nice to see it among all other great improvements that are happening in 5.6.x development line.
OK, now let’s see how the disk usage is affected in this simple test by just creating empty tables in two scenarios: single InnoDB ibdata file and when innodb_file_per_table option is set. In this case I observed no differences between various MySQL versions, so you can see only one here.
Related links:
http://www.mysqlperformanceblog.com/2010/05/06/how-much-memory-innodb-dictionary-can-take/
http://fromdual.com/how-mysql-behaves-with-many-schemata-tables-and-partitions
http://www.percona.com/doc/percona-server/5.5/management/innodb_dict_size_limit.html?id=percona-server:features:innodb_dict_size_limit
http://dev.mysql.com/doc/refman/5.6/en/innodb-performance.html#innodb-performance-table-cache
http://blogs.innodb.com/wp/2011/12/improving-innodb-memory-usage-continued/
http://ma.tt/2006/03/wordpress-and-lyceum/
References: