Glances gives a quick overview of system usage on Linux

26/11/2015 Categories: Système Tags: , , Comments off

Monitor your Linux system

glances system linuxAs a Linux sysadmin it feels great power when monitoring system resources like cpu, memory on the commandline. To peek inside the system is a good habit here atleast, because that’s one way of driving your Linux system safe. Plenty of tools like Htop, Nmon, Collectl, top and iotop etc help you accomplish the task. Today lets try another tool called Glances.

Glances

Glances is a tool similar to Nmon that has a very compact display to provide a complete overview of different system resources on just a single screen area. It does not support any complex functionality but just gives a brief overview CPU, Load, Memory, Network rate, Disk IO, file system, process number and details.

As a bonus, glances is actually cross platform, which means you can use it on obsolete OSes like windows :P.

Here’s a quick glimpse of it.

glances-linux

The output is color highlighted. Green indicates optimum levels of usage whereas red indicates that the particular resource is under heavy use.

$ glances -v
Glances version 1.6 with PsUtil 0.6.1

Project homepage https://github.com/nicolargo/glances http://nicolargo.github.io/glances/

Lire la suite…

MySQL Query Profiling

26/11/2015 Categories: Bases de données Tags: , , Comments off

You can profile a query by doing following:

mysql> SET SESSION profiling = 1;
mysql> USE database_name;
mysql> SELECT * FROM table WHERE column = 'value';
mysql> SHOW PROFILES;

First line enables profiling for current mysql interactive session only. Global profiling is not recommended.

Second line selects database on which we need to fire query.

Third line is actual query. (do not use EXPLAIN here).

Fourth line shows list of recorded profiles. It’s output looks like:


mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                             |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
|        1 | 0.00008050 | SELECT DATABASE()                                                                                                                 |
|        2 | 0.00034975 | show databases                                                                                                                    |
|        3 | 0.00073850 | show tables                                                                                                                       |
|        4 | 0.00040525 | SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Above is list of queries profiled in current session.

You can get execution time breakdown by running another mysql query:

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;

It will print data like:


mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=4;
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
| QUERY_ID | SEQ | STATE                          | DURATION | CPU_USER | CPU_SYSTEM | CONTEXT_VOLUNTARY | CONTEXT_INVOLUNTARY | BLOCK_OPS_IN | BLOCK_OPS_OUT | MESSAGES_SENT | MESSAGES_RECEIVED | PAGE_FAULTS_MAJOR | PAGE_FAULTS_MINOR | SWAPS | SOURCE_FUNCTION       | SOURCE_FILE   | SOURCE_LINE |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
|        4 |   2 | starting                       | 0.000014 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | NULL                  | NULL          |        NULL |
|        4 |   3 | Waiting for query cache lock   | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         646 |
|        4 |   4 | Waiting on query cache mutex   | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         650 |
|        4 |   5 | checking query cache for query | 0.000057 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | send_result_to_client | sql_cache.cc  |        1853 |
|        4 |   6 | checking permissions           | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_parse.cc  |        5007 |
|        4 |   7 | checking permissions           | 0.000004 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | check_access          | sql_parse.cc  |        5007 |
|        4 |   8 | Opening tables                 | 0.000046 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | open_tables           | sql_base.cc   |        4944 |
|        4 |   9 | System lock                    | 0.000009 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_lock_tables     | lock.cc       |         299 |
|        4 |  10 | Waiting for query cache lock   | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         646 |
|        4 |  11 | Waiting on query cache mutex   | 0.000021 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | try_lock              | sql_cache.cc  |         650 |
|        4 |  12 | init                           | 0.000032 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_select          | sql_select.cc |        2622 |
|        4 |  13 | optimizing                     | 0.000015 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |         889 |
|        4 |  14 | statistics                     | 0.000057 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |        1099 |
|        4 |  15 | preparing                      | 0.000017 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | optimize              | sql_select.cc |        1121 |
|        4 |  16 | executing                      | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | exec                  | sql_select.cc |        1879 |
|        4 |  17 | Sending data                   | 0.000099 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | exec                  | sql_select.cc |        2423 |
|        4 |  18 | end                            | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_select          | sql_select.cc |        2658 |
|        4 |  19 | query end                      | 0.000003 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc  |        4686 |
|        4 |  20 | closing tables                 | 0.000007 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_execute_command | sql_parse.cc  |        4738 |
|        4 |  21 | freeing items                  | 0.000011 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | mysql_parse           | sql_parse.cc  |        5931 |
|        4 |  22 | logging slow query             | 0.000001 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | log_slow_statement    | sql_parse.cc  |        1625 |
|        4 |  23 | cleaning up                    | 0.000002 |     NULL |       NULL |              NULL |                NULL |         NULL |          NULL |          NULL |              NULL |              NULL |              NULL |  NULL | dispatch_command      | sql_parse.cc  |        1475 |
+----------+-----+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+
22 rows in set (0.00 sec)

Source: rtcamp.com

How To Use MySQL Query Profiling

26/11/2015 Categories: Bases de données Tags: , , Comments off

Introduction

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:

[mysqld]
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes

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

25/11/2015 Categories: Bases de données Tags: , , , Comments off

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.

Notes:

  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.

Source: rtcamp.com

WordPress Performance Tuning

25/11/2015 Categories: Logiciel Tags: , Comments off

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: ,