How To Use MySQL Query Profiling


MySQL query profiling is a useful technique when trying to analyze the overall performance of a database driven application. When developing a mid to large size application, there tends to be hundreds of queries distributed throughout a large code base and potentially numerous queries ran against the database per second. Without some sort of query profiling techniques, it becomes very difficult to determine locations and causes of bottlenecks and applications slow down. This article will demonstrate some useful query profiling techniques using tools that are built into MySQL server.


What is the MySQL slow query log?

The MySQL slow query log is a log that MySQL sends slow, potentially problematic queries to. This logging functionality comes with MySQL but is turned off by default. What queries are logged is determined by customizable server variables that allow for query profiling based on an application’s performance requirements. Generally the queries that are logged are queries that take longer than a specified amount of time to execute or queries that do not properly hit indexes.


Setting up profiling variables

The primary server variables for setting up the MySQL slow query log are:

slow_query_log			G 
slow_query_log_file			G 
long_query_time			G / S
log_queries_not_using_indexes	G
min_examined_row_limit		G / S

NOTE: (G) global variable, (S) session variable

slow_query_log – Boolean for turning the slow query log on and off.

slow_query_log_file – The absolute path for the query log file. The file’s directory should be owned by the mysqld user and have the correct permissions to be read from and written to. The mysql daemon will likely be running as `mysql` but to verify run the following in the Linux terminal:

 ps -ef | grep bin/mysqld | cut -d' ' -f1

The output will likely display the current user as well as the mysqld user. An example of setting the directory path /var/log/mysql:

cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql

long_query_time – The time, in seconds, for checking query length. For a value of 5, any query taking longer than 5s to execute would be logged.

log_queries_not_using_indexes – Boolean value whether to log queries that are not hitting indexes. When doing query analysis, it is important to log queries that are not hitting indexes.

min_examined_row_limit – Sets a lower limit on how many rows should be examined. A value of 1000 would ignore any query that analyzes less than 1000 rows.

The MySQL server variables can be set in the MySQL conf file or dynamically via a MySQL GUI or MySQL command line. If the variables are set in the conf file, they will be persisted when the server restarts but will also require a server restart to become active. The MySQL conf file is usually located in `/etc or /usr`, typically `/etc/my.cnf` or `/etc/mysql/my.cnf`. To find the conf file (may have to broaden search to more root directories):

find /etc -name my.cnf
find /usr -name my.cnf

Once the conf file has been found, simply append the desired values under the [mysqld] heading:

slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1

Again, the changes will not take affect until after a server restart, so if the changes are needed immediately then set the variables dynamically:

mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log_file = '/var/log/mysql/localhost-slow.log';
mysql> SET GLOBAL log_queries_not_using_indexes = 'ON';
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;

To check the variable values:

mysql> SHOW GLOBAL VARIABLES LIKE 'slow_query_log';
mysql> SHOW SESSION VARIABLES LIKE 'long_query_time';

One drawback to setting MySQL variables dynamically is that the variables will be lost upon server restart. It is advisable to add any important variables that you need to be persisted to the MySQL conf file.

NOTE: The syntax for setting variables dynamically via SET and placing them into the conf file are slightly different, e.g. `slow_query_log` vs. `slow-query-log`. View MySQL’s dynamic system variables page for the different syntaxes. The Option-File Format is the format for the conf file and System Variable Name is the variable name for setting the variables dynamically.

Lire la suite…

Using MySQLTuner to Optimize MySQL

Tweaking MySQL is something you need to do regularly. Unlike PHP & Nginx tweaking, this is not a set & forget job!

We will use mysqltuner for tweaking mysql on a regular basis.

Tweaking MySQL default config first

Open /etc/mysql/my.cnf file & scroll down to [mysqld] section.

You will see many settings & some config variables. Some values are global while some are per-thread values. Its important because if you change something likejoin_buffer_size from 2M to 4M, it can shoot-up mysql’s max memory utilization by 300M memory (as per default 150 mysql’s max_connections value)

To start with, adjust following values:

max_connections = 50     #default is 150
wait_timeout = 30        #default is 28800

You can leave remaining as it is. Mysqltuner will guide you further.

Don’t forget to restart mysql. Command: service mysql restart

Using mysqltuner

If you are following our setup, you may already have mysqltuner installed. Otherwise run apt-get install mysqltuner on Ubuntu. Non-ubuntu guys can get it from here. It’s just a perl script!

When you run mysqltuner, it will show you a report with many suggestions. Just follow them. Exact suggestion will vary so its hard to cover all of them here. Rather I will give you some notes some of them are offered by mysqltuner itself.


  1. Run mysqltuner after 24 hours. It you don’t, it will remind you by showing “MySQL started within last 24 hours – recommendations may be inaccurate.” Reason: mysqltuner recommendation may prove inaccurate.
  2. If it asks you to change value oftmp_table_size ormax_heap_table_size variable, make sure you change both and keep them equal. These are global values so feel free to increase them by large chunks (provided you have enough memory on server)
  3. If it asks you to tweak join_buffer_size, tweak in small chunks as it will be multiplied by value ofmax_connections.
  4. If it asks you to increase innodb_buffer_pool_size, make it large. Ideally, it should be large enough to accomodate your all innodb databases. If you do not have enough RAM consider buying some. Otherwise try to delete unwanted database. Do not ignore this as it can degrade performance significantly.

Apart from above, always keep an eye on following lines in Performance Metrics section of mysqltuner report:

[--] Total buffers: 2.6G global + 130.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 15.3G (48% of installed RAM)
[OK] Highest usage of available connections: 81% (81/100)

Try to keep maximum possible memory less than 50%. Other lines can tell you, if your site is using too “less” mysql connections. In that case, you can reducemax_connections and increase other buffers more generously.

Also, whenever you make changes to mysql config and restart mysql server, always run mysqltuner immediately to check if by mistake you haven’t made maximum possible memory usage too high! Ignore any other suggestion it will give for next 24-hours!

mysqltuner & automatic password

As we use mysqltuner many times, it will be convenient use something like this.


WordPress Performance Tuning

26/10/2020 Categories: Logiciel Tags: , Aucun commentaire

wordpress performance tuning
WordPress is one of the most popular content publishing platform used by some of the very high traffic website. Beauty of the wordpress is in its simplicity. If you will setup your WordPress correctly then your website performance will be fantastic. In this article we will explore various aspect of the WordPress Performance Tuning. Before going further let me clear that why you need to optimize your WordPress website performance.

Why you should spend time on WordPress Performance Tuning ?

Performance tuning or Optimization is required due to following reason:

  1. If you will correctly optimize or tune your WordPress performance then your visitor experience will be better.
  2. Search engines are giving high preference to high speed website.  So your SEO will be improved.
  3. Sometime while doing your WordPress performance tuning you will analyzed and remove unwanted thing from your WordPress which reduce your server work. So your server will have less load.

Tool / Plugin / Stuff you need while WordPress Performance tuning

WordPress performance tuning does not mean to only changing some configuration of your WordPress. There are several area where you need to fine tune like apache(your webserver), Mysql etc. So before going further you need following tool/plugin

  1. Installation of plugin w3 total plugin.
  2. Firefox web browser with firebug tool.
  3. Google Page Speed Test  or GT Metrix.
  4. SSH connection to your server (only required if you want to tune Apache and MySQL performance).
  5. A good Internet speed.

Before going further for your WordPress Performance Tuning please arrange above written tool.

Analyze your WordPress Performance

Before going to optimize your WordPress Performance you need to analyze that whether your website performance is good or bad. Or in other word we can say that you need to check whether your website is served quickly or not. Following are the factor which mainly affect your website performance and you need to check.

  1. Time taken to load your web page
  2. Number of CSS, js and Images are getting downloaded on every request
  3. CSS and JS are placed correctly or not.

You can check your website performance in either Google Page Speed or Gtmetrix. I prefer to use Gtmetrix. Both tool will gives rating to your website Speed and suggestion to improve your website performance further. Gtmetrix will also provide you the timeline waterfall(what is repose time of your website and how many images, css , js or other resources are getting downloaded) of your website. I prefer Gtmetrix because of TimeLine waterfall feature.
You will get following type snapshot for your website in Gtmetrix
WordPress Performance Tuning

In above snapshot you can see the following 4 tab

  1. Page Speed : In this tab you can find your website performance analysis with GooglePageSpeed.
  2. YSlow : In this tab you can find your website performance analysis with YSlow.
  3. TimeLine : In this tab you can find your website page load water fall.
  4. History: In this tab you can find previous history of your website page speed test if you did the test in in future.

Lire la suite…

Categories: Logiciel Tags: ,

Trafic monitor small solution for Linux

Source: Trafic monitor small solution for Linux


The software is really small and fast to install. Was designed to work mostly with iptables and on Linux platform.

Installation is easy. Just add those lines to your firewall or put somewhere to start allways.
After this modification the script with the result from your iptables -L -n. And of course put the .php files somewhere to access via www and make the directory writeable. The output file must be in the directory where are the php files. By default without selecting anything will show last hour traffic. It’s pretty live(update at 6 seconds, not like other programs).

Quick example:

[root@lair trafip]# iptables -A OUTPUT -s -d
[root@lair trafip]# iptables -A INPUT -d -s
[root@lair trafip]# iptables -L -n|grep 127|grep -v ACCEPT|grep -v LOG|grep -v DROP
all --
all --

Get the strings “” and “” and put in Must be exact like iptables shows (better you copy paste that part). The script must be always running to count.

In img.php modify:

$target variable with the name where you redirect the output from (ex: $target="local";)
$ip variable with the IP (ex: $ip="";)
$maxspeed variable with the maximum traffic can be done in 6 seconds (ex: $ip="115200";). If you have black lines on your graph without stopping the interface/traffic then increase the value.
$upload variable with red or green (ex: $upload="red";)
$download variable with red or green (ex: $download="green";)
$imagetype variable with png, gif or jpg, if for output format of graph (ex: $imagetype="gif")

The output file must be something like:

11/12/02 05:57:26 10782702 149477806
11/12/02 05:57:32 10783170 149489806
11/12/02 05:57:38 10783810 149509426

(format: month/day/year[space]hour:minutte:second[space]INPUT_counter[space]OUTPUT_counter

Bandwidth monitoring with iptables

Source: By Gerard Beekmans

Linux has a number of useful bandwidth monitoring and management programs. A quick search on for bandwidth returns a number of applications. However, if all you need is a basic overview of your total bandwidth usage, iptables is all you really need — and it’s already installed if you’re using a Linux distribution based on the 2.4.x or 2.6.x kernels.

Most of the time we use iptables to set up a firewall on a machine, but iptables also provides packet and byte counters. Every time an iptables rule is matched by incoming or outgoing data streams, the software tracks the number of packets and the amount of data that passes through the rules.

It is easy to make use of this feature and create a number of “pass-through rules” in the firewall. These rules do not block or reroute any data, but rather keep track of the amount of data passing through the machine. By using this feature, we can build a simple, effective bandwidth monitoring system that does not require additional software.

Depending on how the firewall rules are set up, the setup for bandwidth monitoring may be very simple or very complex. For a desktop computer, you may need to create only two rules to log the total input and output. A system acting as a router could be set up with additional rules to show the totals for one or more subnets, right down to the individual IP address within each subnet. In addition to knowing exactly how much bandwidth each host and subnet on the network is using, this system could be used for billing or chargeback purposes as well.

Rules setup

The rules setup itself is quick and straightforward, and takes only a few minutes. Obviously, you need to be root or use sudo to insert iptables rules.

The examples in this article are based on a router that provides Internet service to various towns. The iptables rules keep track of how much bandwidth each town uses and how much bandwidth each customer in that town uses. At the end of each month, an administrator checks the counters. Individuals who use more than they were supposed to get billed for over usage, the counters are reset to zero, and the process is repeated at the beginning of the next month.

The IP addresses in this article are modified from the real addresses. We’ll use the private IP space, subnetted into smaller blocks.

First, we will create two custom chains for the two towns and put town-specific rules in them. This will keep the built-in FORWARD chain relatively clean and easy to read. In this example, the FORWARD chain will only provide the global counters (all customers combined on a per-town basis).

iptables -N town-a
 iptables -N town-b

The next data element is the total bandwidth counter. Because this machine is a router only, the INPUT and OUTPUT chains are of little interest. This machine will not be generating a significant amount of bandwidth (i.e., it is not serving as a mail or Web server), nor will it be receiving significant uploads from other hosts.

Total bandwidth downloaded by and uploaded to the two towns combined:

iptables -A FORWARD

This is the easiest of rules. The rule will match any source and any destination. Everything that is being passed through this router matches this rule and will provide the total of combined downloaded and uploaded data.

Lire la suite…