Just upgraded mysql 5.6 to mariadb 10.3.13 – now when the server hits open_tables = 2000, my php queries stop working – if I do a flush tables it starts working correctly again. This never happened when I was using mysql – now I can't go a day without having to login and do a flush tables to get things working again

Use WHM / Cpanel to administer my VPS and on the last WHM release it started warning me that the version of MySql (really can't remember what version it was – it was what was loaded when I got my VPS) that I was running was soon coming to an end and I would need to upgrade to SQL 5.7 or MariaDB xxx. Had been wanting to move to MariaDB for awhile anyway, so that is what I did – WHM recommended the 10.3.13 version.

After some more watching and looking it appears that what makes my open_tables hits the 2000 max was the automatic CPANEL backup routines – which also backup all of my databases at one get go. Doesn't crash anything just causes problems with my PHP application connections – I don't thing the connections get rejected – they just don't return any data …. Turned all of the automatice WHM/CPANEL backups off and things have settled down a little.

table_definition_cache 400
table_open_cache 2000

I still do a mysqldump via cron to do my database backups – only two live databases and they still make the tables_open grow to that 2000 max – just not as fast.

I now run a script that runs every hour to show me some of the variables and here is what I am seeing

after doing a flush tables command both open_tables and open_table_definitions start increasing until open_table_definitions hits 400 it stops increasing while open_tables keeps increasing thru the day.

then when the mysqldumps happen in the early morning hours tables_open hits 2000 (the max setting) and my php queries are not executed

I do not get a PHP error.

I ran the following command so that I could see what was happening on the db side.

SET GLOBAL general_log = 'ON'

Looking at the log, when everything is running OK I see my application connecting, preparing the statement, executing the statement and then disconnecting ….

I did the same thing when it started acting up (i.e. my php application starts to not get a result again)

Looking at the log I see my application connecting, then preparing the statement and then instead of seeing it execute the statement, it prepares the same statement 2 more times and then disconnects …

I logged into mysql and did a flush tables command and everything goes back to normal – application connects, prepares a statement, executes it, disconnects …

But this never happened before I moved to MariaDB – I never messed with the MySQL server stuff at all – the only time MySQL was restarted was when I did a CENTOS 6 system update and had to reboot the server – would go months without doing a thing on the server ….