ActiveRecord vs DAO - Test memory consumption and query execution speed
In the process of growth and development of the project “ Avtoofis - Payment Acceptance and Full Automation of Sales ”, we faced the fact that when there are more than 100,000+ contacts in our databases, one of the main functions responsible for creating and sending mass email mailings On the basis of customer data online stores began to shamelessly slow down.
Having rummaged in the code, it was revealed that when data samples totaling 100,000+ records from MySQL tables using the ActiveRecord class, the consumption of RAM and script processing time increase greatly. It got to the point that one variable storing the result of a sample of 100,000 records consumed 644,750,972 bytes (614 MB) of RAM, and the script execution time exceeded 50 seconds.
Since 100,000 records, to put it mildly, is not the limit of dreams, and the number of AvtoOffice clients has been in the thousands for a long time, we could not ignore this problem and not take measures to eliminate it. As a result, the function described above was completely rewritten into samples using the well-known DAO, which significantly reduced the consumption of resources and increased the speed of the script.
')
In the course of the transition, our programmers were not too lazy and did several tests that vividly show the comparative characteristics of using ActiveRecord and DAO when trying to sample from MySQL databases 1, 5, 10, 50, 100, 500, 1 000, 5 000, 10 000, 50 000 and 100,000 entries. With the results of these tests and I suggest you familiarize yourself right now.
Immediately I would like to say that when carrying out similar tests on your project, the numbers may differ slightly, because I do not know what computer capacities you have, what data you choose from the database, and what means for measuring indicators you will use.
In my case, standard PHP functions were used to measure RAM consumption and calculate the time spent on script development:
The purpose of this article is to show the dynamics of changes in the amount of resources consumed and the time spent on script execution using ActiveRecord and DAO.
And so for starters, a pivot table showing the increase in resource consumption and runtime of scripts using MySQL to access the database:
It is important to understand that in this table the execution time is 0 seconds. means that it took less than 1 second to get the data and save the result to a variable.
Now let's look at the dynamics of growth in the consumption of RAM, depending on the increase in the number of rows returned with data from MySQL databases:
As can be seen from the graph, significant changes when using both approaches begin only if the result of the sample includes more than 1,000 lines. However, the indicators of memory consumption when using DAO is much less than when using ActiveRecord. And the peak performance of DAO consumes 5.91 times less resources than ActiveRecord.
Of course, some “specialists” can say that given the continuous decline in the cost of iron (physical and RAM), as well as the continuous growth of the maximum available server capacity, this problem can be solved by simply purchasing more iron. But, looking at the growth dynamics of resource consumption, it can be noted that, starting from 1,000 records, the growth of resource consumption becomes almost linear, i.e. if 100,000 records consume 614 MB. RAM, then 1,000,000 entries, according to the most conservative estimates, will require 6,140 MB. (6 GB.)
Now let's consider the dependence of the time spent on working out the scripts on the number of returned rows satisfying the database query:
Here everything is much more interesting!
The number of returned values when using DAO has almost no effect on the execution time of the script. At all stages of testing, this indicator did not exceed 1 second.
But ActiveRecord behaved almost as well as with the consumption of resources. It took him 52 seconds to get the result of 100,000 records from the MySQL database, which is completely unacceptable for a serious Internet project. No sane user of your online resource will wait for so long to load the page with the information he needs (of course, provided that you are not a monopolist in your niche).
As a result, I want to say the following:
I really like working with ActiveRecord. It is convenient and very well suited for solving a whole group of tasks that do not require receiving large amounts of data. But, if you work with large data arrays, if your queries should return more than 1,000 records, then it’s better to use something else to build queries and get sample results, for example, the favorite of many DAOs or even cooler solutions.
If you have any questions to me or you want to share your experience in implementing large samples from the MySQL database, then write in the comments, I would be extremely grateful for the valuable content.
Having rummaged in the code, it was revealed that when data samples totaling 100,000+ records from MySQL tables using the ActiveRecord class, the consumption of RAM and script processing time increase greatly. It got to the point that one variable storing the result of a sample of 100,000 records consumed 644,750,972 bytes (614 MB) of RAM, and the script execution time exceeded 50 seconds.
Since 100,000 records, to put it mildly, is not the limit of dreams, and the number of AvtoOffice clients has been in the thousands for a long time, we could not ignore this problem and not take measures to eliminate it. As a result, the function described above was completely rewritten into samples using the well-known DAO, which significantly reduced the consumption of resources and increased the speed of the script.
')
In the course of the transition, our programmers were not too lazy and did several tests that vividly show the comparative characteristics of using ActiveRecord and DAO when trying to sample from MySQL databases 1, 5, 10, 50, 100, 500, 1 000, 5 000, 10 000, 50 000 and 100,000 entries. With the results of these tests and I suggest you familiarize yourself right now.
Immediately I would like to say that when carrying out similar tests on your project, the numbers may differ slightly, because I do not know what computer capacities you have, what data you choose from the database, and what means for measuring indicators you will use.
In my case, standard PHP functions were used to measure RAM consumption and calculate the time spent on script development:
- time () - Returns the number of seconds elapsed since the beginning of the Unix Epoch (The Unix Epoch, January 1, 1970, 00:00:00 GMT) to the current time.
- memory_get_usage () - Returns the amount of memory in bytes that has been allocated to the PHP script at the moment
The purpose of this article is to show the dynamics of changes in the amount of resources consumed and the time spent on script execution using ActiveRecord and DAO.
And so for starters, a pivot table showing the increase in resource consumption and runtime of scripts using MySQL to access the database:
It is important to understand that in this table the execution time is 0 seconds. means that it took less than 1 second to get the data and save the result to a variable.
Now let's look at the dynamics of growth in the consumption of RAM, depending on the increase in the number of rows returned with data from MySQL databases:
As can be seen from the graph, significant changes when using both approaches begin only if the result of the sample includes more than 1,000 lines. However, the indicators of memory consumption when using DAO is much less than when using ActiveRecord. And the peak performance of DAO consumes 5.91 times less resources than ActiveRecord.
Of course, some “specialists” can say that given the continuous decline in the cost of iron (physical and RAM), as well as the continuous growth of the maximum available server capacity, this problem can be solved by simply purchasing more iron. But, looking at the growth dynamics of resource consumption, it can be noted that, starting from 1,000 records, the growth of resource consumption becomes almost linear, i.e. if 100,000 records consume 614 MB. RAM, then 1,000,000 entries, according to the most conservative estimates, will require 6,140 MB. (6 GB.)
Now let's consider the dependence of the time spent on working out the scripts on the number of returned rows satisfying the database query:
Here everything is much more interesting!
The number of returned values when using DAO has almost no effect on the execution time of the script. At all stages of testing, this indicator did not exceed 1 second.
But ActiveRecord behaved almost as well as with the consumption of resources. It took him 52 seconds to get the result of 100,000 records from the MySQL database, which is completely unacceptable for a serious Internet project. No sane user of your online resource will wait for so long to load the page with the information he needs (of course, provided that you are not a monopolist in your niche).
As a result, I want to say the following:
I really like working with ActiveRecord. It is convenient and very well suited for solving a whole group of tasks that do not require receiving large amounts of data. But, if you work with large data arrays, if your queries should return more than 1,000 records, then it’s better to use something else to build queries and get sample results, for example, the favorite of many DAOs or even cooler solutions.
If you have any questions to me or you want to share your experience in implementing large samples from the MySQL database, then write in the comments, I would be extremely grateful for the valuable content.
Source: https://habr.com/ru/post/209568/
All Articles