We are facing a strange problem. We have mysql 5.7 and have
innodb_stats_on_metadata set as false by default but as a precaution we have also have disabled it with following command.
set global innodb_stats_on_metadata=0;
But each request is adding 3 seconds in comparison to if we shift same code to cake2. Cake3 queries generate following slow queries on information_schema.
SELECT * FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = 'stagingdb' AND kcu.TABLE_NAME = 'api_tokens' AND rc.TABLE_NAME = 'api_tokens'
Is there a problem writing to the cache directory in your Cake3? I would expect any schema reflection tasks to be done once and cached.
It looks like this might be making the data that is stored in
tmp/cache/models. But possibly I’m misunderstanding the role and source of this query.
@dreamingmind - I checked the difference between our dev and staging/live env,
tmp/cache/models directory did had 777 permission and I gave it to that directory but nothing is being created there.
Resolving that is above my pay grade. But if there are no cache files in there, it sounds like a candidate for the source of your problem. It should be chock-full of table schema data; stuff that makes the ORM/Entity/Form magic happen.
I would suggest updating your database just to eliminate that as a potential issue. Older versions of MySQL are a bit unstable in my experience.
We were able to get it fixed by restarting the db but we were still not sure what was happening.
Finally we figured that there was a query in our db which was generating an error and creating a small file on disk (thousands and thousands of such files were there), which was slowing down cakephp 3 (cakephp 2 was working fine). Shutting down that query and cleaning the space also worked.
Still don’t know how it was effecting cake3 but not cake2.