info on open tables

--_000_DB7P192MB02687502B7E45EAA8EF75DC0CFEC0DB7P192MB0268EURP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi all


    I am hoping this mail finds all well.


    I have a question about mysql open tables and open_table_cache which I =
do not seem to be finding the answer to on the internet.


     We are busy investigating an issue on a server where we have erattic b=
ehaviour.


      During the times when it experiences issues, I found that there is al=
ways about 4-5 processes on mysql that gets stuck on "closing table" state =
and once the software (opensips) is restarted due to a sigfault, then these=
 queries are killed as well.


      Based on information found we added grafana graphs and did notice tha=
t the table_open_cache is fully used during the time of the sigfaults and t=
he queries getting stuck so we tested by increasing the table_open_cache.


     This did however not resolve the issue as we simply saw table_open_cac=
he using the full value of 3000 and once again the same behaviour.



     When running "Show open tables" though, we find that the amount of ope=
n tables is only 283 and a count of all tables returned that there is only =
381 tables on the server.


    However when running "Show global status like 'open_tables' we get a va=
lue of 2900.


    So my questions are as follows :


     1. Why is the table_open_cache used up if there are not even that many=
 tables.

     2. Why does these values differ so much? or do they provide different =
info.

     3. What other reasons can there be for a query to get stuck on "closin=
g table" state.


   From what I could see , the moment the queries get stuck in this state, =
the load on the server increases to more than the amount of cores available=
 and that is then when the opensips software errors out with a sigfault com=
plaining about mysql being unavailable.



   Any suggestions would be greatly appreciated.



Regards

--_000_DB7P192MB02687502B7E45EAA8EF75DC0CFEC0DB7P192MB0268EURP_--