AWS SNS: that will be setup with run command so that notifications is available on success and failure of command for further action
AWS S3: to store the logs of the shell script and apart from that we can setup events also
AWS Dynamo DB Table: making a global database for status.
AWS ec2, which is having the shell script
Apart from these services, we also need to setup Some of the IAM role and policies so that these services can interact with each other.
let’s assume we have an ec2 instance that has a bash shell script. We will start our process by ensuring that the run command can be executed on that instance. So before doing automation, we will invoke manually.
AWS RUN Command :
AWS System Manager
Click on Run Command [ Used to execute commands remotely, the only pre-requisite is your machine should be able to connect with system manager]. Although some of the images already have SSM installed and running, you can check the status by running
Search for the document AWS -RunShellScript. Either you can make a copy of this document or you can use this directly. This will require 3 inputs in which is mandatory, which is the command itself.
Rest two parameters are optional [working directory & Execution Timeout]
Next is, you need to choose ec2 instance
Next, you can choose an S3 bucket to store the logs, [whatever is echoed or output of sell script, will be available under logs]
and You can choose SNS for the notification with default settings [Notifcation executed on basis of status change (in progress, success, failure)]
So far, lambda should be executing the run command, where notifications are published to SNS, status is written ton dynamo DB, and logs are written to S3.
Now the last thing is, you want to put the execution of lambda behind a REST API call.
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
