MySQL Forums
Forum List  »  Performance

Locked queries
Posted by: Juan Nino
Date: September 28, 2016 06:31PM

I have a server which is getting constant updates queries from radius into one single table called radacct.
Using showprocesslist I can see the multiple updates taking up to one minute to update, during this, when I try to get a select running on a different database on the same server, the query appears as locked. therefore it takes a few minutes to get it showing results.

what I have done
initially radacct was a myisam table, I changed it to innodb.
radacct had a trigger on update, I have dropped the trigger.
I have increased the max_connections to 800
I have checked for peaks on cpu or ram but there is none.
I have enabled the query_cache_size and set it to 10M but off course, this only helps to get faster results when running the same query, the new queries are still getting locked.

I've ran mysqltuner to fine tune more settings and out of its suggestions I have set the following
innodb_buffer_pool_size = 1500000000
max_connections = 800
query_cache_size = 10000000
join_buffer_size = 130000
thread_cache_size = 4
table_open_cache = 100
key_buffer_size = 22000000

I am still having the same issues. only when I restart the mysql service I can get it working fast again until all the radacct updates come and slow down everything.
I currently have another server managing more connections and updates and I dont have the same behaviour. also, I had this database working a few days ago with no problem and now suddenly I have this issues.
if you can let me know what to try I would greatly appreciate it.

Options: ReplyQuote

Written By
Locked queries
September 28, 2016 06:31PM
September 29, 2016 09:39AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.