Hi,
I don't know how to contribute to your project (I am used to github), anyway we have huge database with many thousand projects and we are facing performance issues mainly due to the number of sql queries executed. I am auditing the application and I saw indexes that could be improved. For example on the parameter table you don't take the full advantage of MySQL indexes on multiple columns. I suggest you to merge the parameterProject and parameterUser in a unique index with this patch:ALTER TABLE parameter DROP INDEX parameterProject;ALTER TABLE parameter DROP INDEX parameterUser;ALTER TABLE parameter ADD INDEX parameterUserProject (idUser, idProject);The advantage is that the queries such as select * from parameter where parameter.idUser is null and parameter.idProject is null would use a non unique key lookup instead of merging indexes at each execution. In our testing enviroment, the queries are now executed below 1ms instead of 15ms and we are saving 100ms on saveDataToSession.php script with this fix only.
Hi,
Good idea.
Morover, the idProject is not used on this table, so possibly the column (and index could be removed).
But if we keep the columns, merging indexes will be good.
Ticket #5136 recorded
I found out that projreqtor can trace all SQL queries by setting trace level to 3 and add $debugQuery=true; into the paramaters file.
I developed this PHP script https://gist.github.com/bbalet/52ed04ec0ff6ef1a0b6b6fbd74393b22
It cleans the log, reshapes the queries and compute some statistics in a CSV file in order to priorize the audit on indexes.
By the way I'm not sure about what you want this code in model/persistence/Sql.php to achieve
debugTraceLog(round((microtime(true) - $startMicroTime)*1000000)/1000000 . ";" . $sqlRequest);
I had to multiple the time from the log by 1000 in order to get values that make sense.
Hi,
Thanks for sharing.
But why not just use the "slow_query_log " directive in MySql ?
It will automatically trace queries longer that the "long_query_time" and write it in the "slow_query_log_file"
I had to multiple the time from the log by 1000 in order to get values that make sense.
Sure, values in the log are in Milliseconds, as most queries long just few milliseconds
Hi,
I want to achieve a different objective:
1 - Get the frequency of a type of query
2 - Get the mean execution time
3 - Optionally detect outliers and remove them for more accurate statistics
slow_query_log variable in MySql simply log queries above a threshold.
The problem is that they can be false positive due for example to a lock or temporary overload of the db server.
Another point is that you need to prioritize the queries that you want to improve.
A slow query that is not very often used doesn't necessarily need to be optimized.
I forward you an example of output and later on I will open new threads for code improvement.
Hi,
Thanks.
Yes your approach is good.
Just one question about your result file (I did not examine code generating this file yet) : are the value in seconds or in milliseconds ?
in seconds
in seconds
Are you sure ?
This would mean it take about 200ms for a single "select * from tiichet where id=1"
Really this is 1000 times slower that what I get
In the log file, I read 0,2ms (0,0002s) for that query.
Execution times vary from one system to another:
- There is 41550 tickets in my sample database.
- Maybe that the database server in your case is in running on the same computer than the webserver without virtualization/container.
- My test system is running on two VMs (DB/WS) hosted on Hyper-V (2GB and 2 VCPU each). They communicate through by the physical network.
The file was just a sample, we intend to collect actual data on the production next week.