Archive

Articles taggués ‘performance’

Simple Stateful Load Balancer with iptables and NAT

02/03/2019 Comments off

To demonstrate how iptables can perform network address translation this how-to shows how to use it to implement a over-simplified load balancer. In practice we would use a daemon such as HAProxy allowing IP tables to check packets before forwarding them.

Using the method presented in this tutorial packets get forwarded without going through the INPUT, FORWARD and OUTPUT chains.

iptables is a powerful tool that is used to create rules for how incoming or outgoing packets are handled. It keeps track of a packets state – there is NEW, ESTABLISHED, RELATED, INVALID and UNTRACKED. It can make filtering decisions based on the packets header data and the payload section of the packet, for these purposes iptables even has regular expression matching.

On top of that iptables has extensions that can be used to filter packets based on a packets history so we can keep track of packets and sessions. We can set filters to only trigger at specific times, parse the packet contents and header information searching for specific patterns, differentiate protocols such as tcp, udp, icmp, etc.

For load balancing behavior we want the incoming packets on one machine to be routed to another machine. iptables has extentions that helps us achieve this aim but we also need to muck around with its internal PREROUTING and POSTROUTING table, which is not recommended as this could potentially pose a security risk. lets use iptables to route all traffic coming in on an interface eth0 with a destination port 80 and route it to another IP address:

Allow IP forwarding

(Note: if your testing this on the same box your doing this on it won’t work, you need at least 3 machines to test this out, virtual ones work nicely)

First we enable ipv4 forwarding or this will not work:
# echo "1" > /proc/sys/net/ipv4/ip_forward

XOR

# sysctl net.ipv4.ip_forward=1

next we add a filter that changes the packets destination ip and allows us to masquerade:

# iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 80 -j DNAT --to-destination 10.0.0.3:80
# iptables -t nat -A POSTROUTING -j MASQUERADE

The above filter gets added to iptables PREROUTING chain. The packets first go through the filters in the PREROUTING chain before iptables decides where they go. The above filter says all packets input into eth0 that use tcp protocol and have a destination port 80 will have their destination address changed to 1.2.3.4 port 80. The DNAT target in this case is responsible for changing the packets Destination IP address. Variations of this might include mapping to a different port on the same machine or perhaps to another interface all together, that is how one could implement a simple stateful vlan (in theory).

The masquerade option acts as a one to many NAT server allowing one machine to route traffic with one centralized point of access. This is similar to how many commercial firewalls and network routers function.

The above ruleset results in all incoming packets to dport 80 traversing the iptables chains in a straight line from INCOMING to OUTGOING in the image below, effectively bypassing any rules we might have had in our INPUT chain. If we were to choose to implement nat like this we would need to implement those – our desired INPUT filter rules – on the machines where traffic is forwarded OR add them to the FORWARD chain if we want to block things before they are forwarded (Note: packets might go through FORWARD chain in both directions so direction needs to be considered when writing filters for this chain).

560x260xbuilt-in-chains-in-filter-table.png.pagespeed.ic.CVAYVeFJ96

Path incoming packets take through iptables chains

Lire la suite…

Nmon – A nifty little tool to monitor system resources on Linux

28/02/2019 Comments off

Source: binarytides.com

Nmon

Nmon (Nigel’s performance Monitor for Linux) is another very useful command line utility that can display information about various system resources like cpu, memory, disk, network etc. It was developed at IBM and later released open source.

It is available for most common architectures like x86, ARM and platforms like linux, unix etc. It is interactive and the output is well organised similar to htop.

Using Nmon it is possible to view the performance of different system resources on a single screen.
The man page describes nmon as

nmon is is a systems administrator, tuner, benchmark tool. It can display the CPU, memory, network, disks (mini graphs or numbers), file systems, NFS, top processes, resources (Linux version & processors) and on Power micro-partition information.

Project website
http://nmon.sourceforge.net/

Install Nmon

Debian/Ubuntu type distros have nmon in the default repos, so grab it with apt.

$ sudo apt-get install nmon

Fedora users can get it with yum

$ sudo yum install nmon

CentOS users need to install nmon from rpmforge/repoforge repository. It is not present in Epel.
Either download the correct rpm installer from

http://pkgs.repoforge.org/nmon/

Or setup the rpmforge repository by following the instructions here
http://wiki.centos.org/AdditionalResources/Repositories/RPMForge

And then install using yum

$ sudo yum install nmon

Lire la suite…

Categories: Système Tags: ,

Glances gives a quick overview of system usage on Linux

28/02/2019 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

27/02/2019 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

27/02/2019 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…