SQL Optimization Project for Joomla CMS (Status Update)
GSoC 2014 - SQL Optimization Project for Joomla CMS was the project I am involving at the moment. After spending more time on getting familiar with the Joomla core at the moment I have implemented several queries and hope to carry out the project in order to meet the final outcome.
This post is regarding to the status update for my project on Joomla! in GSoC 2014. As mentioned in my first post I carried on my project work based on three main aspects as follows:
- Testing the performance and identifying the queries and their bottlenecks
- Reimplementing the queries if there is possibility to improve the performance
- Testing the queries and comparing the performance related to the later performance results
During the past five weeks, my first task was to get familiar with the Joomla! code base and to identify the query execution routing in the system at run time. I had to spend about a week in order to get familiar with the code base. After getting a considerable understanding of the query execution routing in the system my next task was to identify a suitable profiling tool for the query profiling. I had to go through so many resources and had to use several profiling tools in order to identify the most suitable profiling tool for the analyzing purposes. With hard working I found the profiling tool called Neor Profiler. The reasons for choosing this profiler tool is because it can store profiling data for multiple sessions and also it is an Open Source product.
One another important profiling tool is the Joomla!'s inbuilt debug tool. This can be enabled from the administration panel and after enabling the debug tool we can identify the query's and also their execution details in the system. I use both of these tools because without logging to the database system debug tool shows the query explanation and also it provides the file execution path also. With the lack of the multiple session handling ability I do not use this tool only. Instead I use the Neor Profiler with the debugging tool.
After the above mentioned pre-preparation, my next task was to overload the system with a large amount of data. This was the most difficult task I had to face. With the help of my Mentors Nicola Galgano and Gunjan Patel, I could overload the system with a considerable amount of data articles. But at the moment we believe the amount of data is not enough because we have about 13k of data articles and about 1600 categories with four levels. Still we are searching for a larger and real dataset and carrying out the project work with the current resources.
My next step was to start the query analyzing and identifying the bottlenecks and start the optimization work of the queries. I started working on the com_content and com_categories components at first and at the moment I am carrying out the optimization work on that. First I started at the administration component and went through the analyzing process to identify the bottlenecks. I could identify several queries which there is a need of optimization and documented them before starting the implementation. I had to went through tutorials and to understand the optimizing techniques of the queries. For this task I spent nearly a week and then I started the optimization work for the identified queries.
Following weeks spent for the implementation work and I optimized several queries and tested them locally. After some testing I committed several changes and the others need more testing. At the moment my mentors are doing further testing on those changes before pushing them to the Joomla repo.
My future target is to start the stress testing with concurrent users and I am hoping to use the apache's ab.exe tool for this purpose. There is more work to do in order to complete the project and I hope at the end of the day that I will be able to provide something valuable to the community.