Tagged: LAMP

Monitoring LAMP Application using Zabbix

A complete guide for monitoring LAMP [Linux/Apache/Mysql/PHP] application through Zabbix and setting up email notifications.

In the following section, I am going to use the following stack,

1- for Zabbix server: ubuntu20 / zabbix 5.0

2: For application [LAMP server] AMI 2 and steps are mentioned for AMI1 and ubuntu 18. Steps are a bit the same for another platform as well.

3. I am utilizing AWS for all Zabbix servers and LAMP Server

First of All, We need to set up a Zabbix server.

Setting Up Zabbix Server

As mentioned, we are using, Zabbix 5.0 on ubuntu.

https://www.zabbix.com/download?zabbix=5.0&os_distribution=ubuntu&os_version=20.04_focal&db=mysql&ws=apache

  • We are following exactly the same steps as mentioned on this page, except one part, where we need to install MySQL server because we are using the same machine to host zabbix database as well.

    wget https://repo.zabbix.com/zabbix/5.0/ubuntu/pool/main/z/zabbix-release/zabbix-release_5.0-1+focal_all.deb    
    dpkg -i zabbix-release_5.0-1+focal_all.deb
    apt update
    apt install zabbix-server-mysql zabbix-frontend-php zabbix-apache-conf zabbix-agent
    apt-get install mysql-server mysql-client
    mysql -u root -p
    zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -uzabbix -p zabbix
    vim /etc/zabbix/zabbix_server.conf
    vim etc/zabbix/apache.conf
    vim /etc/zabbix/apache.conf
    systemctl restart zabbix-server zabbix-agent apache2
    systemctl enable zabbix-server zabbix-agent apache2

Once setup is done and assuming port 80 is open on the zabbix server,

you can browser this url for the front end

http://<publicip>/zabbix

Default Credential to Login are

Admin / zabbix

Basic Flow of monitoring

  • There is a Zabbix server, which has a monitoring interface
  • There is a Zabbix agent installed and configured on the machine which requires monitoring [in our case, LAMP application], called Host
  • Create a Host configuration, just an entry with hostname and associate monitoring template
  • Attach some monitoring templates. There are a prebuilt template for Linux/apache/PHP/MySQL and many more services and you can also create
  • The template contains “items” which has a configuration for what needs to monitor
  • Item contains triggers that raise alarm on basis of the defined threshold
  • Zabbix agent [active/passive] utilized to send the data to Zabbix server on basis of these template configurations.
    • Active: where agent sends the data automatically [Prefer This to save unnecessary polling of agent]
    • Passive, Zabbix server will make a request to Zabbix agent to pull the data
  • Dashboard/Graph/Screen to monitor
  • Media to add notification

Setting Up Zabbix Agent on LAMP Server [AMI 2]

Considering the following stack of LAMP application

  • Linux: AMI 2, Apache server, php 7.4 through FPM, MySQL server

Install the agent

rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/7/x86_64/zabbix-agent-5.0.9-1.el7.x86_64.rpm
systemctl enable zabbix-agent.service 

install the agent and enable at boot

Edit Agent Config File

vim /etc/zabbix/zabbix_agentd.conf
  • Define Value in Server:<zabix server IP/ if the same network then private ip will work>, Used for passive monitoring
    • Server=172.31.23.126,18.116.121.251
  • Listen Port 10050
    • ListenPort=10050
  • Define value in ServerActive, for agetn active
    • ServerActive=172.31.23.126,18.116.121.251
  • Define Hostname, This value is used it uniquely identify in Zabbix server and IT SHOULD BE EXACT MATCH in Zabbix server host configuration
    • Hostname=lampserver

Restart the service,

systemctl restart zabbix-agent.service

Setting up Host Configuration in Zabbix Server

  • Login on Zabbix server URL : HTTP://<pulbicip>/zabbix
  • Under Configuration, click on Host, add the exact hostname
  • Add Ip of the host <private/public depend upon network>, the default port
  • Make sure 10051 and 10050 ports are open on the client and server for communication
  • Add template, you can import the custom template attached in the link

Once setup zabbix client will be able to sent data to server, You can check logs on zabbix client machine

tail -f /var/log/zabbix/zabbix_agentd.log

It will start monitoring linux basic like CPU/Memory/Disk etc

Setting Up Apache configuration on LAMP server

For monitoring purposes, We need to add some configurations so that Zabbix agent can send that data to Zabbix server.

  • Make sure server info module is enabled
    • >>> httpd -M

Once the module is enabled, add the following line in httpd.conf [/eth/httpd/conf/httpd.conf]

<Location /server-info>
SetHandler server-info
Order Deny,Allow
Deny from all
Allow from localhost
</Location>

restart the apache server

service httpd restart

test this from the command line,

curl http://localhost/server-info

It should provide server information. Once this is done, you can monitor the host and graphs, and you will be getting up detail about the apache server

You Can Monitor

  • apache load, worker status, process, etc
  • monitor long request or time taking request

Setting Up PHP-FPM for monitoring

To monitor php-fpm process , You need to modify two file,

  • edit php-fpm config from the file vim /etc/php-fpm.d/www.conf
  • uncomment folloowing
    • pm.status_path = /status
    • ping.path = /ping
    • ping.response = pong
  • Edit httpd conf file [/etc/httpd/conf/httpd.conf] and add the following code
<LocationMatch "/status">
    SetHandler "proxy:unix:/run/php-fpm/www.sock|fcgi://localhost/status"
  Allow from localhost 
</LocationMatch>

<LocationMatch "/ping">
   c
  Allow from localhost 
</LocationMatch>

note : Just check php-fpm path is same for you

For php 5.6, there may be change in name of php-fpm process, like
SetHandler “proxy:unix:/run/php5.6-fpm/www.sock|fcgi://localhost/status”

You Can Monitor

  • PHP-fpm load, process, etc
  • monitor long request or time taking request

Making Changes so that Apache access logs can be monitored

In my custom template, I have added a few custom items to monitor the number of 404 status code requests, 500 status code requests, and 404 logs

  • You will get this Data under the Host graph along with other data

Make sure, the Zabbix agent is able to read the log file, this can be ensured by executing the following command

su zabbix -s /bin/bash -c "tail /var/log/httpd/access_log"

if you are getting permission denied, please provide the permission to Zabbix users to read this directory.

Setting Up Zabbix Agent on AMI 1

Install the agent

rpm -Uvh https://repo.zabbix.com/zabbix/5.0/rhel/6/x86_64/zabbix-agent-5.0.9-1.el6.x86_64.rpm
chkconfig zabbix-agent on

Rest of the steps are same.

Setting Up Zabbix Agent on Ubuntu

Install the agent

wget https://repo.zabbix.com/zabbix/5.0/ubuntu/pool/main/z/zabbix/zabbix-agent_5.0.9-1%2Bbionic_amd64.deb
dpkg -i zabbix-agent_5.0.9-1+Bbionic_amd64.deb

install the agent and enable it at boot

Although Process is the same except there are changes in file location, as,

  • the apache config file will be located at /etc/apache2/apache.conf
  • In template item we are accessing apache logs, the location will be changed in case of ubuntu [/var/log/apach2/]

Adding Email Notification

  • Login on web interface of zabbix server http://<publicip>/zabbix
  • Click on administartion > media > Email [html]
  • Add SMTP configuration [if aws, you cand use AWS SES service]
  • Click on test to test the configuration
  • Click on User under Administration > Users
  • Click on media > add media > select email and enter email id

Monitoring Apache Logs using Zabbix

Apart from monitoring server hardware and software key variable like CPU/Memory/Disk/Process, We also require monitoring of apache logs using Zabbix to monitor all from a…
Read More

Monitoring MySql and Optimization

Next part in monitoring and optimization of LAMP stack is, Monitoring MySql and optimization.

As we are aware that MySQL is RDBMS, so, here, I will detail out the Mysql server monitoring and configuration to Database level optimization.

Monitoring MySql

your MySQL server may contain more than one Databases, which will be connected to different applications.

There are various tools available, command line to Graphical to connect with MySQL server, some of the most utilized tools are

[Graphical]

[Command line]

  • mysqladmin [mysqladmin -u <user> -p status] [default installed with MySQL command-line utilities]
Uptime: 783136  Threads: 1  Questions: 46141  Slow queries: 0  Opens: 73993  Flush tables: 1  Open tables: 2000  Queries per second avg: 0.058
MySQL on localhost (5.7.27)                                                                                                                               load 2.64 2.93 2.86 1/1457 17872 up 9+01:35:50 [13:06:43]
  Queries: 45.0k    qps:    0 Slow:     0.0         Se/In/Up/De(%):    78/08/02/00 
 Key Efficiency: 87.7%  Bps in/out:  18.3/ 86.6   
    Id      User         Host/IP         DB       Time    Cmd    State Query                                                                                                                                        --      ----         -------         --       ----    ---    ----- ----------                                                                                                                                  828      root       localhost                     0  Query starting show full processlist                                                                                                                    

All these tools will help you figure out the current processlist , processed, running queries, server configuration and stats about the past data.

As these are just an interface to visualize information, let’s start with most utilized tool, PHPMyAdmin

If you login PHPMyAdmin as an admin, you will get an status tab, which will show,

There are also more details available. let’s figure out the meaning of these stats, which is processes, showing current executing processes. This can also be gathered by executing query

show processlist;

Most Important variables those are adjusted

  • max_conenctions
SHOW VARIABLES LIKE "max_connections";
  • innodb_buffer_pool_size
  • innodb_io_capacity
  • query_cache_size

Tuning / Optimization MySql as server

Once you get the status, and now, you want to lookup for what can be optimized. Although PHPMyAdmin also has an advisor which is suggesting tuning the server variables,

apart from that, there is a Perl script that can also look up the database logs and provide your recommendations.

You can download this script and run it on server, like

perl mysqltuner.pl

output of this command will be

        MySQLTuner 1.8.5 - Major Hayden [email protected]
      Bug reports, feature requests, and downloads at http://mysqltuner.pl/
      Run with '--help' for additional options and output filtering    
 [--] Skipped version check for MySQLTuner script
 [!!] Successfully authenticated with no password - SECURITY RISK!
 [OK] Currently running supported MySQL version 5.7.27-0ubuntu0.18.04.1
 [OK] Operating on 64-bit architecture
 -------- Log file Recommendations ------------------------------------------------------------------
 [!!] Log file /var/log/mysql/error.log doesn't exist
 -------- Storage Engine Statistics -----------------------------------------------------------------
 [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
 [--] Data in MyISAM tables: 6.0M (Tables: 36)
 [--] Data in InnoDB tables: 8.4G (Tables: 13843)
 [OK] Total fragmented tables: 0
 -------- Analysis Performance Metrics --------------------------------------------------------------
 [--] innodb_stats_on_metadata: OFF
 [OK] No stat updates during querying INFORMATION_SCHEMA.
 -------- Security Recommendations ------------------------------------------------------------------
 [OK] There are no anonymous accounts for any database users
 [OK] All database users have passwords assigned
 [!!] User 'root'@'%' has user name as password.
 [!!] User 'root'@% does not specify hostname restrictions.
 [!!] There is no basic password file list!
 -------- CVE Security Recommendations --------------------------------------------------------------
 [--] Skipped due to --cvefile option undefined
 -------- Performance Metrics -----------------------------------------------------------------------
 [--] Up for: 9d 0h 26m 43s (43K q [0.056 qps], 683 conn, TX: 60M, RX: 13M)
 [--] Reads / Writes: 88% / 12%
 [--] Binary logging is disabled
 [--] Physical Memory     : 11.4G
 [--] Max MySQL memory    : 2.7G
 [--] Other process memory: 0B
 [--] Total buffers: 192.0M global + 17.1M per thread (151 max threads)
 [--] P_S Max memory usage: 72B
 [--] Galera GCache Max memory usage: 0B
 [OK] Maximum reached memory usage: 277.3M (2.37% of installed RAM)
 [OK] Maximum possible memory usage: 2.7G (23.63% of installed RAM)
 [OK] Overall possible memory usage with other process is compatible with memory available
 [OK] Slow queries: 0% (0/43K)
 [OK] Highest usage of available connections: 3% (5/151)
 [OK] Aborted connections: 0.00%  (0/683)
 [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
 [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 9K sorts)
 [!!] Joins performed without indexes: 3515
 [!!] Temporary tables created on disk: 51% (5K on disk / 10K total)
 [OK] Thread cache hit rate: 99% (5 created / 683 connections)
 [OK] Table cache hit rate: 94% (46K hits / 49K requests)
 [!!] table_definition_cache(1400) is lower than number of tables(14158) 
 [OK] Open file limit used: 0% (7/5K)
 [OK] Table locks acquired immediately: 100% (886 immediate / 886 locks)
 -------- Performance schema ------------------------------------------------------------------------
 [--] Memory used by P_S: 72B
 [--] Sys schema is installed.
 -------- ThreadPool Metrics ------------------------------------------------------------------------
 [--] ThreadPool stat is disabled.
 -------- MyISAM Metrics ----------------------------------------------------------------------------
 [!!] Key buffer used: 18.2% (2.9M used / 16.0M cache)
 [OK] Key buffer size / total MyISAM indexes: 16.0M/330.0K
 [!!] Read Key buffer hit rate: 37.5% (8 cached / 5 reads)
 -------- InnoDB Metrics ----------------------------------------------------------------------------
 [--] InnoDB is enabled.
 [--] InnoDB Thread Concurrency: 0
 [OK] InnoDB File per table is activated
 [!!] InnoDB buffer pool / data size: 128.0M/8.4G
 [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
 [OK] InnoDB buffer pool instances: 1
 [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
 [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
 [OK] InnoDB Read buffer efficiency: 99.75% (1225155 hits/ 1228218 total)
 [!!] InnoDB Write Log efficiency: 77.65% (8621 hits/ 11103 total)
 [OK] InnoDB log waits: 0.00% (0 waits / 2482 writes)
 -------- Aria Metrics ------------------------------------------------------------------------------
 [--] Aria Storage Engine not available.
 -------- TokuDB Metrics ----------------------------------------------------------------------------
 [--] TokuDB is disabled.
 -------- XtraDB Metrics ----------------------------------------------------------------------------
 [--] XtraDB is disabled.
 -------- Galera Metrics ----------------------------------------------------------------------------
 [--] Galera is disabled.
 -------- Replication Metrics -----------------------------------------------------------------------
 [--] Galera Synchronous replication: NO
 [--] No replication slave(s) for this server.
 [--] Binlog format: ROW
 [--] XA support enabled: ON
 [--] Semi synchronous replication Master: Not Activated
 [--] Semi synchronous replication Slave: Not Activated
 [--] This is a standalone server
 -------- Recommendations ---------------------------------------------------------------------------
 General recommendations:
     Set up a Secure Password for 'root'@'%' user: SET PASSWORD FOR 'root'@'%' = PASSWORD('secure_password');
     Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
     RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
     Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
     We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
              See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
              (specially the conclusions at the bottom of the page).
     When making adjustments, make tmp_table_size/max_heap_table_size equal
     Reduce your SELECT DISTINCT queries which have no LIMIT clause
     Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
 Variables to adjust:
     join_buffer_size (> 256.0K, or always use indexes with JOINs)
     tmp_table_size (> 16M)
     max_heap_table_size (> 16M)
     table_definition_cache(1400) > 14158 or -1 (autosizing if supported)
     innodb_buffer_pool_size (>= 8.4G) if possible.
     innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
MySql server becomes slow if the queries running over the server takes much time to execute, by this, upcoming connections queries will be waiting for locks.

All about Slow Query and Slow Query Logs.

There is a variable at the server, called “slow_query_log”, which will setup mysql to log all queries with some statistics which are taking more than defined time to execute.

This configuration can be defined either at configuration file or by setting up globally by using MySQL query

set global slow_query_log = 'ON';
set global log_queries_not_using_indexes = 'ON';
set global slow_query_log_file ='/var/log/mysql/slow-query.log';
set global long_query_time = 2; // 2 second

by setting from MySQL query prompt or in configuration file [.cnf] like

slow_query_log = ON
log_queries_not_using_indexes = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2

How to read slow query log file ?

These log files can be parsed into a more analyzed format through various utilities. One of the most used utility is pt-query-digest

pt-query-digest <path to slow query log>

This will output like

# 4.3s user time, 200ms system time, 39.12M rss, 4.12G vsz
# Current date: Thu Nov 29 10:02:45 2018
# Hostname: JJG.local
# Files: slowquery.log
# Overall: 4.51k total, 36 unique, 1.27 QPS, 15.65x concurrency __________
# Time range: 2018-11-27 21:00:23 to 21:59:38
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         55640s      5s    118s     12s     16s      4s     13s
# Lock time        18446744073714s    34us 18446744073710s 4085657602s   260us 271453769812s   194us
# Rows sent          2.95M       0 103.99k  684.27       0   8.14k       0
# Rows examine     293.63M       0  18.67M  66.59k    0.99 583.21k    0.99
# Query size        44.63M      79   1.22M  10.12k   2.16k  97.85k   2.06k

# Profile
# Rank Query ID                      Response time    Calls R/Call  V/M  
# ==== ============================= ================ ===== ======= =====
#    1 0x5AE6E128A4790517E5CFFD03… 52666.0213 94.7%  4363 12.0711  0.86 UPDATE some_table
#    2 0x222A6FC43B63B119D2C46918…   618.3909  1.1%    29 21.3238  1.91 UPDATE some_table
#    3 0xD217B90797E8F34704CF55AF…   463.7665  0.8%    29 15.9919  0.07 SELECT some_other_table some_other_other_table

You can also log the slow query in a mysql table rather than file.

--log_output=TABLE  // by default it is file.

By setting up this variable, logs can be written to MySQL table as well.

that table is present under the default MySQL database.

SHOW CREATE TABLE mysql.slow_log;

That table has the following structure.

FieldTypeNullKeyDefaultDescription
start_timetimestamp(6)NOCURRENT_TIMESTAMP(6)Time the query began.
user_hostmediumtextNONULLUser and host combination.
query_timetime(6)NONULLTotal time the query took to execute.
lock_timetime(6)NONULLTotal time the query was locked.
rows_sentint(11)NONULLNumber of rows sent.
rows_examinedint(11)NONULLNumber of rows examined.
dbvarchar(512)NONULLDefault database.
last_insert_idint(11)NONULLlast_insert_id.
insert_idint(11)NONULLInsert id.
server_idint(10) unsignedNONULLThe server’s id.
sql_textmediumtextNONULLFull query.
thread_idbigint(21) unsignedNONULLThread id.
rows_affectedint(11)NONULLNumber of rows affected by an UPDATE or DELETE (from MariaDB 10.1.2)

As soon as you are aware of the slow queries, next thing is to detail out each query and its execution plan.

How to know the query execution plan ?

By using EXPLAIN keyword at start of the SQL, you will get the query execution plan

EXPLAIN select * from user left join company_users on company_users.userid = user.id

this will provide you the result like this

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEuserNULLALLNULLNULLNULLNULL54100.00NULL
1SIMPLEcompany_usersNULLALLcompuser_usedep_uixNULLNULLNULL9100.00Using where; Using join buffer (Block Nested Loop)

To learn more about explain

Now, you can start optimizing your tables, queries basis on recommendations.

Read Next

Monitoring Apache Logs using Zabbix

Apart from monitoring server hardware and software key variable like CPU/Memory/Disk/Process, We also require monitoring of apache logs using Zabbix to monitor all from a…
Read More