Re: Performance bottlenecks with thousands of small databases
Posted by:
Rick James
Date: March 20, 2014 06:45PM
> In fact, going to a single ibdata file may degrade performance further instead of improve it?
Not in any way I know of. All InnoDB I/O is in blocks of 16KB. A block lives in a "tablespace" (ibdata or .ibd) at some offset. Random I/O (which is almost always the case) is mostly oblivious (performance-wise) to whether one file descriptor is involved, or many. I/O is buffered and multi-threaded.
The main reason for recommending file_per_table=ON is for maintenance -- ALTER, DROP, etc can return disk space to the OS.
Your situation (lots of files) is more rare, and =OFF addresses a more pressing problem (too many file descriptors).
> over 2,500 databases and 30,000 tables
Numbers like those probably indicate a flaw in the schema design. I have almost never seen more than 100 dbs and 1000 tables. (I have studied hundreds of systems.)