Php profile mysql queries

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

Simple mysql profiler class to check the performance of your page queries.

License

cornernote/php-mysql-profiler

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.txt

---------------------------------- PHP MySQL Profiler ---------------------------------- Copyright (c) 2012 Brett O'Donnell Source Code: https://github.com/cornernote/php-mysql-profiler Home Page: http://mrphp.com.au/blog/php-mysql-profiler This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. ---------------------------------- Description ---------------------------------- Simple mysql profiler class to check the performance of your page queries.

About

Simple mysql profiler class to check the performance of your page queries.

Источник

A tool to profile mysql queries in php env.

This tool helps you to quickly profile a mysql query in a PHP 7.4+ environnement. You can also compare 2 queries.

This image shows the results when comparing 2 ip search queries ((A) BETWEEN + index vs (B) INTERSECTS + SPACIAL index):

• left: query cost ( SHOW STATUS. )
• right: query plan ( SHOW PROFILE. )
• bottom: optimizer information ( EXPLAIN. )

This is a standalone one file php script. Not dependency (vanilla js, css and standard php modules only).

• find mysql configuration issues
• improve your indexes
• improve your queries
• spot mysql limitations

• Display the following reports:
  • query cost ( SHOW STATUS. )
  • query plan ( SHOW PROFILE. )
  • optimizer information ( EXPLAIN. )

  Use it only in DEV and control who can access it!

  1. Copy the file in a secure location (with .htaccess, etc.)
  2. Create a mysql user with profiling privileges.
  3. Configure the tool (user, password, ip allow list, etc.)

  The following will create a PHP 7.4 container with the mysql query profiler and also a mariadb 10.4 container

  Open http://localhost/mysql_query_profiler.php in your web browser

  To stop, run docker compose down

  You may want to profile the queries generated by your application by clicking on a link from your web pages.

  1. In your main configuration file, add a constant that will allow you to turn on/off the query displaying. For example:

  define('MQP_PROFILE_QUERIES', true);

  Источник

  Profiling MySQL Queries with phpMyAdmin

  I have used phpMyAdmin for over a decade. In my early years with the tool, I simply needed something that could show me table structure and quickly give me the data inside. As my needs have grown, so have the tools included with phpMyAdmin which keeps me coming back as my primary MySQL tool, even with optimization.

  Introduction and Scope: Using the Tools at Hand

  I have had the pleasure to work with several different databases. Each have their drawbacks, and each have their strengths. When given a choice, I tend to migrate back to MySQL, despite me being too cheap to purchase the MySQL Enterprise. Instead, I make due with phpMyAdmin as my main profiling tool. It works well for me, but I had to do quite a bit of research to understand what I am looking at while profiling my applications. I am hoping to pass this along in a way that can be understood by the beginner, up to the seasoned pro.

  Optimization takes time. Managers, clients, and peers for that matter, do not like to hear that a project is behind schedule because of optimization. Often times we rush the optimization in order to meet those benchmarks. In the end though, we aren’t doing anyone any favors. The prettiest web application in the world is going to get you repeat business if it takes 10 seconds to load every page. Likewise, if we wait to optimize until the end of our projects, chances are there will be much more work to do, than if we had been checking as the project goes along.

  A couple of notes before we get into the meat and potatoes. First, I am not going to get into MySQL Tuning, as it is a bit out of the scope for this tutorial. While tuning is optimization, it’s a topic all to itself in my opinion. I will briefly mention a couple of opportunities to optimized how to tune your server, but the mentions will be brief. In addition, I will be mainly looking at MyISAM tables and not InnoDB tables. The rule of thumb is if you are writing lots of data, use InnoDB, but if you are using SELECT much more, then use MyISAM. Also, I am not getting into table level REPAIR, OPTIMIZE, CHECK and ANALYZE as this tutorial is covering query optimization with phpMyAdmin. Again, this is a bit out of the scope for this tutorial.

  Finally, I am going to look at WordPress as a real world example. I will be the first to tell you that I am not an expert in WordPress, but I can look at the generated queries with the best of them. From what I have seen the database with WordPress is well indexed, but once we start adding things that are outside of those main core files, those indexes might not be the best for what we need.

  «Optimization takes time. Managers, clients, and peers for that matter, do not like to hear that a project is behind schedule because of optimization.»

  Do I Need to Optimize? : Look internally

  The long answer is phpMyAdmin gives us a chance to see if we need to optimize our queries, and how badly we need to optimize them. I would imagine that you have seen this screen more than once if you have used phpMyAdmin:

  

  It’s the standard start screen for phpMyAdmin. Unless you are looking for ways to optimize, you might well go straight to your tables on the left hand menu, and never see the tab menu at the top. That menu, specifically the Status and Variables tabs are where we are going to start.

  Let’s start with the Status screen, which might be the most important tool that phpMyAdmin provides:

  

  This is the top of the status screen. While it does have some interesting data, if you have never gone below the scroll you have missed out on some very important information. For the sake of brevity, I want to look at two very simple counter values which I obsess over, the first from my test environment:

  

  The two values to pay very close attention to are, Handler_read_rnd and Handler_read_rnd_next. If those two values are in the red, then there are some queries out there that need to be checked, as when MySQL does a SELECT, it is reading the entire table. In some cases, this might be by design, as when you place an index on a table, it takes a bit longer to write, and it takes a bit more space. However, if you see something like this:

  

  chances are, this wasn’t by design. 141 Million requests to read a row on a fixed position, and 16 Billion requests to read the next row, probably means that we are missing an index or two (thousand). Obviously, this number grows based on the number of requests, so the more a search engine indexes your site, or the more visitors that you have, the greater a small missed index becomes. Full table scans are the enemy, and this gives you a quick way to spot how close that enemy is to the gates.

  Another great table to check for query performance takes a look at selects and indexes directly:

  

  This table pays particular attention to your joins. A dangerous combination is not using and index on either table, because your full table scans go up exponentially on the number of joins that you use. The more normalized your tables, the more you need to pay attention to your indexes, as well as the definition of the fields you are joining.

  Finally, depending on a global variable, you also will want to check this variable table as well:

  

  If you are logging your slow queries, this variable counter shows the number that have been identified for observation, depending on the setting of long query time. Those variables can be found from the variables tab. A quick look in my test environment shows this setting (for now):

  

  These two tabs show quite a bit more information, some of which is absolutely vital for tuning your MySQL server. PhpMyAdmin makes it real easy for even the novice to spot a problem, and to have a basic understanding of what that problem might be. If a value is green, we are good. If it is red, it needs some attention. It also allows us to understand that we made some progress. When we restart our server, these session variables are all flushed. If we have made changes, we can see right off the bat if we made any impact.

  EXPLAIN : Understanding the Gibberish

  Now that we have identified that we need to do some optimization, let’s look at some of the tools that we are going to use prior to finding our problems. The first of the tools, and probably the most helpful is to use EXPLAIN. EXPLAIN basically gives us our query execution plan. This tells us what MySQL plans to do with this query before it executes.

  Without reading up on EXPLAIN, the output might not mean much to you. Using a table I created for a past tutorial, let’s look at an unoptimized execution plan. My table only has two fields in this case, one being sales_id, and the other being sale_amount. Here’s the query that I am working with:

  Источник