3
I have an application in Rails that uses a Mysql database with a table with millions of rows. Sometimes it happens that some part of my application does a very heavy query, locking all the rest of the application. How to prevent these heavy queries from blocking my database, or maybe limit so that the bank is locked for a maximum of 30 seconds, dropping the query if it takes too long?
3
All this depends a lot on how the environment is configured, the modeling of the database and the query performed and the way.
If database and its language run on the same machine a severe resource dispute condition may occur.
Mysql needs to read a lot of data from disk and transfer to memory and at the same time the Ruby process communicates with the Mysql process.
All this in the same CPU and on the same bus can cause a great performance degradation.
If this is the case, try separating database server and application server into two distinct hardware connected by a high-speed network.
It’s hard to talk about performance and optimization problems without talking about the model.
Inappropriate use of types (use VARCHAR as primary keys or to store numbers and dates), lack of suitable indexes, over normalization (needing to join many tables to retrieve information) are factors that contribute to a suffering performance.
With appropriate hardware and modeling, it is then necessary to check the query execution plan.
The most common cause of slowness in consultation is the so-called table scan, that is, when a query need to read all records from a table to return the results.
This happens whenever a condition is used WHERE which is not linked to an index. It may also occur in other situations, such as Subqueries, joins or even during the ordination.
For example, if you have a query like this:
select id, nome, idade, sexo, endereco
from cliente
where idade = 30
order by nome
The ideal in this case would be to create two indexes: one per idade and another by nome in ascending order.
This would make Mysql not have to read the table to filter the results. Looking at the index for idade it would determine which records to search for in the truth table and by looking at the index for nome, it would not need to compare all the names to determine the order, since the index is ordered.
See another example:
select id, nome, idade, sexo, endereco
from cliente
where idade = 30 and sexo = 'M'
order by nome
In the above case, Mysql could filter by idade and then look record the record to filter the sexo. This is better than nothing, but could be even more efficient with an index including idade and sexo.
Finally, the indexes allow the database to know beforehand which data exactly should be recovered for the user, without it needing to scan the entire table loading the values and performing comparisons.
And something to note is that even with good indexes, using conversion operations of transformation types or functions in the comparison clauses can cause them not to be used. There are many possibilities, so it is always good to test the query with a tool that shows the execution plan.
If the database server is locked, then the fault should not be Ruby’s.
From what I understand, the server is unavailable, so the processing bottleneck should be on it.
If the query was running quickly and then Ruby was reading the recovered information, probably the blocked process would be from Ruby itself.
But there is still something to consider...
Relational databases work with certain levels of isolation, which control how different sessions and transactions can see each other’s data.
The fact that you cannot see the data while the query is running may be caused by system overload or simply because the isolation level blocks the table when it is being read.
Something that can optimize concurrent reading is to allow "dirty reading". To do this in Mysql it is necessary to execute the command below:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
When performing this, you should be able to query the table while it is read or changed.
For more details about the command see documentation.
Browser other questions tagged mysql ruby-on-rails query performance active-record
You are not signed in. Login or sign up in order to post.
Are you sure the bottleneck is in the bank? Maybe it’s Rails. Which server do you use in production? Webrick is not good for production.
– user7261
Yeah, he locks the bank, I can’t even do queries right in the bank. With the SHOW PROCESSLIST command I see several connections in lock because of the heavy query
– Daniel Cukier
Have you ever tried to implement Eager loading with
includes? Behold here and here.– user7261
Checks if indexes are not missing in the tables.
– GuiGS