0

my data structure is around 0.2 million Innodb tables. each table is not longer than 300 rows. And the number grows by ~3000 tables per day. I'm afraid that this may bottleneck my I/O operations while looking for the required tables. Each table is completely independent of the others. and I only use SELECT and WHERE in my queries, nothing else. How can I optimize my query speed? for example, is keeping the Innodb_file_per_table=1 a good measure? Also, the server takes almost an hour to start, is there a way to speed this up?

roma salah
  • 3
  • 2
  • 2
    To have millions of independent tables tell me you should change something. Switch to NoSQL, data warehouse or something else. IMHO MySQL is not the right solution in your case. – Romeo Ninov Jul 09 '22 at 10:55
  • 1
    I flagged this question for movement to DB, since it would be the best place imho as there will be the specialized ppl for such kind of requests – djdomi Jul 09 '22 at 17:30

1 Answers1

0

Immediate question

For tiny tables, file_per_table is probably the wrong way to go. It would be better to have them in the main tablespace (ibdata).

The OK does not like having millions of tables, especially when they are in a single directory -- which is the case of all these TABLEs are in the same DATABASE.

"almost an hour to start" -- that is the cost for a huge number of file_per_table tables.

Possibly the fastest and simplest way to switch is to dump (via mysqldump or similar tool), start a fresh MySQL (possibly upgrading while you are at it), then

SET GLOBAL innodb_file_per_table = OFF;

and reload the data.

Deeper question

Let's discuss why so many tables. You say "completely independent", but do you mean "same schema" but "data is never fetched together"? In general, tables with the "same schema" (that is, same CREATE TABLE except for table name) should be put into the same table. This new structure would need an extra column to distinguish what had been separate tables. Also, the indexes need revamping. Please provide SHOW CREATE TABLE for one of the zillion similar tables; I'll advise further.

If the tables mostly have different schemas; show us a few samples. Hopefully see can see a pattern that can lead to drastically shrinking the number of tables.

Rick James
  • 2,463
  • 1
  • 6
  • 13