Re: Performance bottlenecks with thousands of small databases
It is not Wordpress.
Tried doing ALTER TABLE tablename ENGINE=InnoDB; for all tables in all databases, but eventually I saw an error like NO DATABASE SELECTED, or LOST CONNECTION, after it used up all 8 GB of memory. I even tried upgrading the server to 16GB of memory and running it, same thing, ran for 10 minutes or so then errored.
Is this operation in memory? Should I see the number of directories and files in the MySQL data directory reduce as it runs? I was reading a bit further and perhaps adding:
RESET QUERY CACHE;
After every Alter table might help. What do you think? I would have assumed I would see the number of directories and files reduce as it ran the ALTER TABLE statements. Also, the ALTER table query seems to be single threaded (blocking), as it was running, it was only maxing out a single CPU core.
Thank you so much for the insight and feedback.