0

I've a table with 10,000 records precommit_tags_change_lists, a select query on this takes forever.

I tried to add an index as below which also hangs...

ALTER TABLE `precommit_tags_change_lists` ADD INDEX `change_list_id` (`change_list_id`)

Following is the structure of the table, any guidance on how to debug this and what could be causing this issue?

enter image description here

One observation is Quite a few process are stuck with state "Waiting for table metadata lock" on the table precommit_tags_changelists

enter image description here

Because of the above the database connection keeps failing intermittently with error Can't connect to MySQL server on '10.xx.xxx.xxx' ((1040, u'Too many connections'))")

user3508811
  • 847
  • 4
  • 19
  • 43
  • What do some of those queries look like? Depending on what exactly you're selecting the index you added may or may not be useful. – VoteyDisciple Aug 25 '17 at 15:41

2 Answers2

1

A table of 10k records is not very large. An ALTER TABLE should complete in at most a couple of seconds. I think it's likely that your ALTER TABLE is waiting for a lock on the table. All those other SELECT queries are also waiting, because they're queued behind the ALTER TABLE.

An ALTER TABLE requires exclusive access to the table. No other query can be running while ALTER TABLE does its work (well, certain types of changes can be done "online" in MySQL 5.6 or later, but in general no). This exclusive access is implemented using the metadata lock. Many SELECT queries can share a metadata lock, but ALTER TABLE cannot share.

So I think your real problem is that you have some long-running query hindering the ALTER TABLE. You haven't shown this long-running query.

It's possible to make a long-running query even on a small table. It has to do with the logic of the query. You should look in your processlist for a query referencing precommit_tags_change_lists but is not waiting for metadata lock. It will be in some other state (like "sending data" or "writing to temp table", etc.), and has been running for longer than any other query.

When you find that query, kill it. If it has been running for hours, it's not likely anyone is still waiting for its result. Once you kill that query, the logjam will be broken, and the ALTER TABLE and all the other queries will be able to complete.

This is my guess, based on experience. But I have to make some assumptions about your situation because you haven't provided all the relevant information.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

This error can occurs when connection reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections To check the current value of this variable, login as root user and run the following command: show global variables like max_connections;

Login to MySQL using root user and increase the max_connections variable to higher value. SET GLOBAL max_connections = 100;

In order to make the max_connection value persistent, modify the value in the configuration file.

To persist the Change Permanently 

Stop the MySQL server:
Service mysql stop
Edit the configuration file my.cnf.
vi /etc/my.cnf
Find the variable max_connections under mysqld section.
[mysql]
max_connections = 100
Set into higher value and save the file.
Start the server.
Service mysqld start
nitin.sharma0180
  • 471
  • 3
  • 6
  • @nitin.sharma0810 - is there is a command to check how many current connections are established? – user3508811 Aug 29 '17 at 21:26
  • SELECT @@threads_connected may help. SELECT @@max_used_connections will show High Water Mark since startup. Because most queries are completed quickly, web connections may be rarely active with the first SELECT. – Wilson Hauck Sep 02 '17 at 21:35