Overview of tricks to improve performance of MySQL in ERP5.
Table of Contents
Check which requests are slow¶
Log in MySQL and use the command "show full processlist", this will display all requests which are executed at this time and you can see which one are slow with the column 'Time'. You can also use the log slow request in my.cnf.
Then test this request, here is a script that I used to test mysql request :
for ITER in `seq $1`; do
echo -ne "${ITER}\r"
mysql -h localhost -u root -t erp5 --disable-pager -t -e "SELECT..." >> /dev/null
done
echo
In a shell do : ./test_mysql.sh 3, this will fill the mysql cache then do for example : time ./test_mysql.sh 10, you will get the time it takes to execute the query 10 times.
Improve table structure¶
The table "catalog" contains a huge number of columns and some can remain completely unused by the application. One thing to do is to customize your catalog by removing all columns which you are sure won't be used by your application. This will speed up insertion and update of rows in the table.
Use index¶
Using explain on your slow request, you will see how mysql is using or not index defined on table. If you see in explain things like 'using filesort' or 'using a tmp table' is quite bad for performance, you should avoid this as much as possible.
If you think one index is missing, you can add it, this will speed up request by 100 times at least. But take care not to add too many index as insertion and update will then go slowly.
A case in which an index is often required is when sorting a list, for example in searchFolder, if not index contains the columns on wich the ORDER BY is done, mysql will use a filesort which is slow, so you must defined an index wich contains the columns on wich the sort and tell mysql to use it, it will not do it by itself, look at http://www.mysqlperformanceblog.com/2007/02/16/using-index-for-order-by-vs-restricting-number-of-rows/ to know why. You need to force this when doing test.
In ERP5, you can configure your catalog in order to make it force MySQL to use index when necessary. You just need to go on the property tab of your catalog and select in "sql_catalog_index_on_order_keys" the appropriate field. For example if you want SQLCatalog to force MySQL to use your index when sorting on column 'title' from 'catalog', select the field 'catalog.title'.
Remove JOIN¶
Try to avoid JOIN as much as possible, for example, if you use getInventory to only get quantity, you don't all other result from the request and thus can remove join. Another way is to use subquery instead of join if possible.
Related Articles¶