Lots of connections in "opening table" "closing table"

[Bassam Tabbara]

Hello,

 

Last night we upgraded our Percona Server (5.1.47-rel11.1) running on EC2 from m2.2xlarge to m2.4xlarge. This effectively adds 4 more (virtual) processors for a total of 8, and upgrades memory to 68GB. We also increased the InnoDB buffer pool to 60 GB (up from 27 GB).  Most of our data now fits in memory and I/O is reduced significantly.

 

We are however seeing a problem where lots of connections get stuck in “opening table” and “closing table” state. It almost feels like the whole server is hung. Closing the connections seems to resolve the problem. I’m aware of the following open bugs:

 

http://bugs.mysql.com/bug.php?id=51325

http://forum.percona.com/index.php/t/1189/

 

but we don’t really have any temporary innodb tables, nor do we do any DROP tables.

 

This does seem like a concurrency issue similar to that reported here:

 

http://venublog.com/tag/thr_lock_open/

 

although I’m not able to tell for sure.

 

Increasing the table_open_cache limit to 1000 seems to extend the period before the hang happens.

 

Can you please point me in the right direction to diagnose this?

 

Thanks a million,

Bassam

 

[Bassam]

I found the problem. Our queries where doing lots of UNION ALL queries
with long chains. Each of referenced table in the chain used up an
open table handle.

[Peter Zaitsev]

Baasam,

Thanks for update. Good to hear it is not Percona Server issue.

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

Percona Training Workshops
http://www.percona.com/training/