Guide 12 - Optimize access to MySQL |
4/15/2021 6:52:30 PM |
| PHPRunner Tips and Tricks | |
fhumanes author
This article is, like everything that is on my website, an example of what tools we have to observe, measure and if you can, correct the yields that our PHPRunner applications have. In PHPRunner, many Querys are produced and executed that we do not define, in many cases we do not know, and that in a few occasions, but very important, they have returns that are not expected. PHPRunner does not give you many tools, once the "disaster" of observing and measuring what is happening is happening. In the file "Include / AppSetting.php" in the first sentence this code is available: $Ddebug = false; If we put "TRUE" it facilitates traces in the browser, the Querys that does but when mixing trace data with application data, this usually gives many problems and helps little, although sometimes it may be enough. Objective: The objective of this article is to know all the consultations that are made to the database and once those that can give us problems identify us, identify access plans of the database manager and some references of what we can do to improve the access times. The full article is on my portal |
|
|
|
fhumanes author 4/15/2021 |
|
Explanations: The first thing we are going to do in activating traces in the MySQL database manager, to inform us of all the Querys that resolves. Very important, these traces should not be activated in systems in production because it is going to make the server slower. The tool we are going to use to observe and measure is MySQL Workbench. Once installed and configured, we access to consult our database and access the status query of the manager and its instance.
It is very normal not to meet the active log files. The state is seen in the figure once it is configured. To activate them, in this case, we will modify the file "My.ini" that is in "Base Directory" of the installation of MySQL. Very important - Before starting the changes, make a copy of the file in case we have any error / problem and we have to return to the previous configuration
You can vary any of the variables, as it will depend on the version of MySQL / MariaDB that you are using, but are marked with brightness. If no PATH is indicated, the files will be created in the "Data Directory". They are TXT files, which you can observe its content with any publisher. How can you observe, you can configure a file to collect the accesses that take longer of the time (in seconds) to be fixed. This can be used to detect problems that we do not know at what point of the application occurs or by means of "Slow Query Log" queries. To analyze a query I have used a very simple application and in the general query box I have put a value and the system has resolved me outcome.
In the "Fhumanes.log" file we see all the consultations that have been made and observed those that Phprunner built to resolve what we have indicated.
We can see what it does (even being a very simple example) 2 Querys nothing simple. Each query that we want to measure will copy it and we will execute it at MySQL Workbench.
I explain the annotations I have made in the Figure:(1) .- This button allows us to put on "nice" the sentence so that it is more readable. (2) .- In this "magnifying glass" button, it allows us the strategy information that will use the database manager to resolve the query. (3) .- This button allows us to see the plan information in graph or table format. (4) .- The information of the plan, where it explains if it uses indexes, part of these or makes a sequential reading of all the data. Once we have observed and measured, we have to correct. Here we are going to give you a rule, because there is only one, if not many things that must be taken into account, for example:
Knowing the problems you can define what the solutions are. I hope you have been illustrative and for any questions, contact me at fernandhumanes@gmail.com P.S. |
|
|
Tandy 4/17/2021 |
|
I tried a few times emailing you fhumanes at: fernandhumanes@gmail.com And I get back from Google: |
|
|
|
fhumanes author 4/17/2021 |
|
Hello: I find it very strange what you tell me, because every day I receive several emails and, until now, nobody has told me that you have problems sending me something. The account is: fernandohumanes@gmail.com my email |
|
|
|
Tandy 4/17/2021 |
|
Tried again this time clicking on the link.. Thank you.. |
|