0

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 ....

  • MySQL 5.4???... – Rick James Mar 12 at 2:14
  • Might be your open-file-limit isn't sufficient to support 2K table_open_cache. – danblack Mar 21 at 2:46
1

10.3.3 was a beta release. As the is a new upgrade, use the latest GA 10.3 release. Lots of these sort of bugs pop up in earlier versions.

ref: https://mariadb.com/kb/en/library/release-notes-mariadb-103-series/

  • 1
    Kinda important details here. Ask a new question with this contained. Show observations, what is the mysql error that php is getting? caches flush themselves out when not in use. Is something maintaining persistent connections? – danblack Mar 7 at 21:58
0

Yes - looks like the system was the culprit - I changed the open_table_cache to 400 and my php application is no longer having any issues preparing statements, even after the nightly backups of the databases. Looking at older mysql documentation, mysql 5.6.7 had a table_open_cache setting of 400, so when I upgraded to mariadb 10.3.13 that default setting was changed to 2000 which is when I started having problems.

Not quite sure what the following is telling me, but might be of interest ....

su - mysql

-bash-4.1$ ulimit -Hn 100 -bash-4.1$ ulimit -Sn 100 -bash-4.1$ exit logout [~]# ulimit -Hn 4096 [~]# ulimit -Sn 4096

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.