In this blog post, I’ve associated several PostgreSQL commands with what I know in MySQL. Below is a table of some of the frequently used commands.
| MySQL | PostgreSQL | Description |
| mysqldump | pg_dumpall | extract a PostgreSQL database cluster into a script file |
| mysqldump | pg_dump | extract a PostgreSQL database into a script file or other archive file |
| N/A | pg_restore | restore a PostgreSQL database from an archive file created by pg_dump |
| N/A | pg_top | pg_top is a PostgreSQL interactive monitoring utility, similar to the Unix top utility, providing a dynamically updated view of activity within a PostgreSQL database |
| mysql | psql | PostgreSQL interactive terminal |
| Exit Mysql {quit|exit| [ctrl-c]} | Exit Postgres\q | quit psql, |
| show databases; | \l or \list | View all of the defined databases on the server |
| use [dbname]; | \c [dbname] | Connect to a database |
| show tables; | \dt or \dt+ | List all the tables in the current schema |
| describe [tablename]; | \d [tablename] | Shows the columns, types, modifiers, indexes, and tables referenced by keys. |
| show create table [tablename]; | No direct equivalent, use below command from shell: pg_dump -st tablename dbname | This will provide the sql used to create a table. |
| select * from mysql.user; | select * from pg_user; \du | Lists all database roles |
| show full processlist; | select * from pg_stat_activity; | Show what queries are currently running in the DB |
| show variables; | show all; | Displays the current setting of run-time parameters |
| show engine innodb status\G | SELECT * FROM pg_stat_activity; SELECT * FROM pg_stat_database; SELECT * FROM pg_stat_user_tables; SELECT * FROM pg_stat_user_indexes; SELECT * FROM pg_locks; | There is no central place in PostgreSQL to get all of the information obtained by running show engine InnoDB status in MySQL. However, there are a number of queries you can run to get roughly equivalent data. |
| show slave status\G | select * from pg_stat_replication; select now() – pg_last_xact_replay_timestamp() AS replication_delay; | Monitor replication |
| \G | \x | Sets or toggles expanded table formatting mode |
Cheers!
Here is a script to get the Top 10 Time-Consuming Queries (This will work for PostgreSQL version 13 and above)
We will make use of the pg_stat_statements view. This view will tell us which types of queries are slow and how often these queries are called.
In AWS RDS, this module is already installed by default. So, all you need to do is to create the extension before running the script.
create extension pg_stat_statements;
In Tencent DB for PostgreSQL, both module and the extension are enabled by default. So, there is no need to do anything before running the query.
Disable expanded display
\x
Script to get Top 10 Time Consuming Queries
SELECT
t2.rolname,
t3.datname,
queryid,
calls,
(
total_plan_time + total_exec_time
) / 1000 as total_time_seconds,
(min_plan_time + min_exec_time) / 1000 as min_time_seconds,
(max_plan_time + max_exec_time) / 1000 as max_time_seconds,
(mean_plan_time + mean_exec_time) / 1000 as mean_time_seconds,
(
stddev_plan_time + stddev_exec_time
) / 1000 as stddev_time_seconds,
rows,
shared_blks_hit,
shared_blks_read,
shared_blks_dirtied,
shared_blks_written,
local_blks_hit,
local_blks_read,
local_blks_dirtied,
local_blks_written,
temp_blks_read,
temp_blks_written,
blk_read_time / 1000 as blk_read_time_seconds,
blk_write_time / 1000 as blk_write_time_seconds
FROM
pg_stat_statements t1
JOIN pg_roles t2 ON (t1.userid = t2.oid)
JOIN pg_database t3 ON (t1.dbid = t3.oid)
WHERE
t2.rolname != 'rdsadmin'
AND queryid IS NOT NULL;
Cheers!
In this blog post, I will show you how to:
Enabling Logging and Monitoring
In Production, the best practice is to send our deployment logs and metrics to a dedicated monitoring deployment. Monitoring indexes logs and metrics into Elasticsearch and these indexes consume storage, memory, and CPU cycles like any other index. We can avoid affecting other production deployments and view the logs and metrics, even when production deployment is unavailable, by using a separate monitoring deployment.We need a minimum of three monitoring nodes to make monitoring highly available. Read More
Curator is an index management tool provided by open source Elasticsearch. This tool allows you to create, delete, and disable indexes.
It also allows you to merge index segments.
This blog postdescribes how to install Curator and how to delete old indices based on time.
Installing Curator
pip3 install elasticsearch-curator
Check curator version
curator --version
Note: If you encounter this error while installing.
ERROR: Cannot uninstall ‘PyYAML’. It is a distutils installed project and thus we cannot accurately determine which files belong to it which would lead to only a partial uninstall.
Execute the command below to fix it.
sudo -H pip3 install --ignore-installed PyYAML
Create a curator.yml file
In this file, indicate the host, port, username, and password.
Reference https://www.elastic.co/guide/en/elasticsearch/client/curator/5.0/configfile.html
# Remember, leave a key empty if there is no value. None will be a string,
# not a Python "NoneType"
client:
hosts:
- 192.168.1.1
port: 9200
url_prefix:
use_ssl: False
certificate:
client_cert:
client_key:
ssl_no_validate: False
username: elastic
password: Password
timeout: 30
master_only: False
logging:
loglevel: INFO
logfile:
logformat: default
blacklist: ['elasticsearch', 'urllib3']
Create a delete_indices_time_base.yml file
Reference: https://www.elastic.co/guide/en/elasticsearch/client/curator/current/ex_delete_indices.html
The example configuration below will delete indices with a prefix pattern basketbal-scores- (full index format: basketbal-scores-2022.04.01) older than 14 days.
---
actions:
1:
action: delete_indices
description: >-
Delete indices older than 14 days (based on index name), for logstash-
prefixed indices. Ignore the error if the filter does not result in an
actionable list of indices (ignore_empty_list) and exit cleanly.
options:
ignore_empty_list: True
timeout_override:
continue_if_exception: False
disable_action: False
filters:
- filtertype: pattern
kind: prefix
value: basketbal-scores-
exclude:
- filtertype: age
source: name
direction: older
timestring: '%Y.%m.%d'
unit: days
unit_count: 14
exclude:
Manually run script
/usr/local/bin/curator /home/scripts/delete_indices_time_base.yml --config /home/scripts/curator.yml
Schedule the script to run daily via cronjob
# Housekeep indices more than 14 days 0 0 * * * /usr/local/bin/curator /home/scripts/delete_indices_time_base.yml --config /home/scripts/curator.yml >> /home/scripts/log/curator_purging_time_base.log 2>&1
This blog describes the steps to create and deploy Redis Enterprise Cloud Subscription and Databases as code on GCP.
Install Terraform in a centos VM. If you want to test the connection with the Redis private endpoint in the same VM after provisioning the databases, then use a VM that is part of the VPC network that you want to peer with Redis Enterprise later.
sudo yum install -y yum-utils sudo yum-config-manager --add-repo https://rpm.releases.hashicorp.com/RHEL/hashicorp.repo sudo yum -y install terraform # Verify version terraform --version
You may also run the terraform scripts in the cloud shell.
Here are the Terraform scripts that you need to upload to your server.
provider.tf
terraform {
required_providers {
google = {
source = "hashicorp/google"
version = "4.21.0"
}
rediscloud = {
source = "RedisLabs/rediscloud"
version = "0.2.9"
}
}
}
provider "rediscloud" {
# Configuration options
api_key = "${var.api_key}"
secret_key = "${var.secret_key}"
}
variables.tf
# You may get the values of api_key and secret_key (Also known as API user keys) by following the steps from this site https://docs.redis.com/latest/rc/api/get-started/manage-api-keys/#:~:text=Sign%20in%20to%20your%20Redis,select%20the%20API%20Keys%20tab
variable "api_key" {
type = string
default = "<my_api_key>"
}
variable "secret_key" {
type = string
default = "<my_secret_key>"
}
# Project name as Prefix of the Redis instance
# Example: "${projname}-gacc-cache-redis"
variable "projname" {
type = string
default = "test-project"
}
variable "region" {
type = string
default = "us-west1"
}
variable "preferred_zones" {
type = string
default = "us-west1-a"
}
variable "multiple_zones" {
type = bool
default = false
}
variable "cidr" {
type = string
default = "192.168.1.0/24"
}
main.tf
# Generates a random password for the database
resource "random_password" "passwords" {
count = 3 # this number should be equal to the number of Redis database to be created
length = 20
upper = true
lower = true
numeric = true
special = false
}
resource "rediscloud_subscription" "MY-REDISCLOUD-SUBSCRIPTION" {
name = "My-Redis-Subscription"
memory_storage = "ram"
cloud_provider {
# Running in GCP on Redis resources
provider = "GCP"
region {
region = "${var.region}"
networking_deployment_cidr = "${var.cidr}"
preferred_availability_zones = ["${var.preferred_zones}"]
multiple_availability_zones = "${var.multiple_zones}"
}
}
database {
name = "${var.projname}-redis-database1"
protocol = "redis"
memory_limit_in_gb = 6
replication = true
data_persistence = "none"
throughput_measurement_by = "operations-per-second"
throughput_measurement_value = 10000
password = random_password.passwords[0].result
alert {
name = "dataset-size"
value = 80
}
alert {
name = "throughput-higher-than"
value = 10000
}
}
database {
name = "${var.projname}-redis-database2"
protocol = "redis"
memory_limit_in_gb = 6
replication = true
data_persistence = "none"
throughput_measurement_by = "operations-per-second"
throughput_measurement_value = 10000
password = random_password.passwords[1].result
alert {
name = "dataset-size"
value = 80
}
alert {
name = "throughput-higher-than"
value = 10000
}
}
database {
name = "${var.projname}-redis-database3"
protocol = "redis"
memory_limit_in_gb = 13
replication = true
data_persistence = "aof-every-1-second"
throughput_measurement_by = "number-of-shards"
throughput_measurement_value = 4
password = random_password.passwords[2].result
alert {
name = "dataset-size"
value = 80
}
alert {
name = "throughput-higher-than"
value = 10000
}
}
}
outputs.tf
# Terraform output values
output "database_private_endpoints" {
description = "Output private endpoints"
sensitive = true
value = {
for database in rediscloud_subscription.MY-REDISCLOUD-SUBSCRIPTION.database:
database.name => database.private_endpoint}
}
output "database_passwords" {
description = "Output passwords"
sensitive = true
value = {
for database in rediscloud_subscription.MY-REDISCLOUD-SUBSCRIPTION.database:
database.name => database.password}
}
Few Notes
Update Redis Enterprise Cloud API Keys in variables.tf
You can get the values of api_key and secret_key (Also known as API user keys) by following the steps from this site.
The Account key identifies the account associated with the Redis Enterprise Cloud subscription.
The User key (secret_key) identifies the user and (optionally) the context of a request. Generated by account owners.
In main.tf file, add database blocks with configuration based on application requirements.
Example:
Also in the main.tf, edit the count parameter under random_password resource. The number should be equal to the number of Redis instance to be created.
Executing Terraform Commands To Create Redis Enterprise Databases
Execute terraform init command to initialize a working directory containing Terraform configuration files.
terraform init
Run terraform plan command to create an execution plan, which lets you preview the changes that Terraform plans to make to your infrastructure.
terraform plan
If the plan is okay, then execute terraform apply.
terraform apply
Outputting sensitive data
The database_private_endpoints and database_passwords are sensitive data. So, the contents will not be instantly outputted after executing terraform apply.
| terraform output -json database_private_endpoints terraform output -json database_passwords |
To output the contents, run the commands below.
terraform output -json database_private_endpoints terraform output -json database_passwords
Verifying Redis databases from the console.
Bonus Section: VPC Peering
Google Cloud VPC Network Peering allows internal IP address connectivity across two Virtual Private Cloud (VPC) networks regardless of whether they belong to the same project or the same organization.
In your Redis subscription, Go to Connectivty tab, then click + Add peering.
Provide Project ID and Network Name. Then Copy the Google cloud command.
Click Initiate peering.
Configure your GCP project and region.
gcloud config set core/projectgcloud config set compute/zone zone
Execute the command to accept VPC Peering. This is the Google cloud command you copied from Redis Enterprise portal.
Example:
gcloud compute networks peerings create rl-c19541-us-west1-1-rlrcp --project--network --peer-network=c19541-us-west1-1-rlrcp --peer-project=g0c4be81afe238245-tp
Check the peering status in Redis Enterprise portal. It may take several minutes to become active.
Once the peering is successful, the status will become green.
Cheers!
In this blog post, I’m gonna talk about MySQL Memory Issues. What causes High memory usage and what is the impact on MySQL database instances.
But first, we can do a quick review on the following topics:
Private memory is used to allocate cache upon connection to MySQL server. It is allocated for each thread. For example; sort buffer, join buffer, and temporary table.
InnoDB maintains one or more buffer pools that cache frequently used data and indexes in the main memory.
So, when a read query is executed from the client program, InnoDB checks to see if the required data pages are in the buffer pool. If it is not in the buffer pool, InnoDB requests the data from the tablespace. Then it will put the data pages in the buffer pool. And then, MySQL will return the results to the client.
innodb_buffer_pool_size is the most important tuning parameter. It caches table and index data. The buffer pool permits frequently used data to be accessed directly from memory, which speeds up processing.
Setting it too low can degrade the performance. Setting it too high can increase the memory consumption causing the DB to crash.
MySQL allocation is not only from innodb_buffer_pool_size, but also from other buffers in the database such sort_buffer_size, read_buffer_size, read_rnd_buffer, join_buffer_size, and tmp_table_size and this will require additional 5-10% extra memory
Below is a formula to calculate the approximate memory usage for your MySQL:
Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)
Swapping can happen when a system requires more memory than is allocated .A large buffer may lead to swapping in the operating system and make the performance slow.
To avoid your MySQL/MariaDB data being SWAP instead of RAM, you have to play with a kernel parameter called swappiness.
A swappiness value is used to change the balance between swapping out runtimememory and dropping pages from the system page cache. The higher the value, the more the system will swap. The lower the value, the less the system will swap. The maximum value is 100, the minimum is 0, and 60 is the default.
I performed a test in AWS RDS MySQL. I ran a stored procedure that consumed a lot of memory until the server crashed. We will notice that when memory gets low, Swap usage will increase. We will also see a spike in disk IO usage.
When MySQL crashed, we will lose connection to the database. In AWS RDS, we can see the logs and EVENTs to identify the cause of the crash. The log indicates that the database process was killed by the OS due to excessive memory consumption.
I also did the same test in Azure MySQL. The difference between Azure and AWS is that when the DB crash, in Azure, it will failover to the Standby replica, but in AWS, a mysqld process crash would not trigger a failover.
In Azure, under Private DNS Zone, if we see that the IP address changed, then it means that it failed over to the standby replica successfully.
Cheers!
To block all write access and to mark all tables as ‘properly closed’ on disk, we execute the FLUSH TABLES WITH READ LOCK. Note that the tables can still be used for read operations. Open tables will be closed and all tables will be locked for all databases with a global lock.
FLUSH TABLES and RELOAD privilege are required to execute this operation.
To release the lock, you may use UNLOCK TABLES, which implicitly commits any active transaction only if any tables currently have been locked with the LOCK TABLES. If the UNLOCK TABLES is executed after FLUSH TABLES WITH READ LOCK, then the commit will not occur because FLUSH TABLES WITH READ LOCK does not acquire table locks.
Cheers!
Here is a shell script I wrote to automate the setup of Predixy Proxy in Centos 7.
This script will work if you have a single endpoint (i.e. AWS Elasticache cluster configuration endpoint or a standalone Elasticache endpoint).
#!/bin/bash
# Created By : Enrique Valencia
# Script Name : predixy_auto_setup.sh
# Description : Predixy Automatic Setup
################################################################################################################
# Usage : ./auto_predixy_setup.sh <configuration_end_point>"
# Example : ./auto_predixy_setup.sh dba-redis-test01.bbssxp.clustercfg.apse1.cache.amazonaws.com:6379"
# To Check Status : systemctl status predixy
# To Start : systemctl start predixy
# To Stop : systemctl stop predixy
# To Restart : systemctl restart predixy
################################################################################################################
################################################################################################################
##### Validate Usage
################################################################################################################
if [ $# -ne 1 ]
then
echo ""
echo "************************************************************************************************************"
echo "Usage: ./predixy_auto_setup.sh <configuration_end_point>"
echo ""
echo "Example: ./predixy_auto_setup.sh dba-redis-test01.bbssxp.clustercfg.apse1.cache.amazonaws.com:6379"
echo "************************************************************************************************************"
echo ""
exit 1
fi
################################################################################################################
##### Variable
################################################################################################################
ENDPOINT=$1
################################################################################################################
##### Install Predixy
################################################################################################################
InstallPredixy(){
mkdir /predixy_data
cd /predixy_data
sudo yum install git -y
sudo yum install libstdc++-static -y
yum -y install gcc
yum -y install gcc-c++
sudo git clone https://github.com/joyieldInc/predixy.git
cd predixy
sudo make
mkdir -p /predixy_data/predixy/log
}
################################################################################################################
##### Cluster
################################################################################################################
cluster(){
cat >> /predixy_data/predixy/conf/cluster.conf << EOF
ClusterServerPool {
MasterReadPriority 100 # 100
# Password sjwkk123456 # redis
StaticSlaveReadPriority 0 # redis slave redis 0
DynamicSlaveReadPriority 0 # redis sentinel 0
RefreshInterval 1 # predixy redis sentinel 1
ServerTimeout 1 # predixy/redis predixy redis blpop 0 redis 0
ServerFailureLimit 10 # redis 10
ServerRetryTimeout 1 # redis 1
KeepAlive 120 #predixy redis tcp keepalive 0 0
Servers {
##
+ $ENDPOINT
}
}
EOF
sed -i 's/Auth "#a complex password#" {/Auth "123456" {/' auth.conf
sed -i 's/WorkerThreads 1/WorkerThreads 4/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# Log .\/predixy.log/Log \/predixy_data\/predixy\/log\/predixy.log/' /predixy_data/predixy/conf/predixy.conf
sed -i '/# LogRotate 1d 2G/d' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# LogRotate 1d/LogRotate 1d/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/Include try.conf/# Include try.conf/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# Include cluster.conf/Include cluster.conf/' /predixy_data/predixy/conf/predixy.conf
}
################################################################################################################
##### Standalone
################################################################################################################
standalone(){
cat >> /predixy_data/predixy/conf/standalone.conf << EOF
StandaloneServerPool {
RefreshMethod fixed
Group shard001 {
+ $ENDPOINT
}
}
EOF
sed -i 's/Auth "#a complex password#" {/Auth "123456" {/' auth.conf
sed -i 's/WorkerThreads 1/WorkerThreads 4/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# Log .\/predixy.log/Log \/predixy_data\/predixy\/log\/predixy.log/' /predixy_data/predixy/conf/predixy.conf
sed -i '/# LogRotate 1d 2G/d' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# LogRotate 1d/LogRotate 1d/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/Include try.conf/# Include try.conf/' /predixy_data/predixy/conf/predixy.conf
sed -i 's/# Include sentinel.conf/Include standalone.conf/' /predixy_data/predixy/conf/predixy.conf
}
################################################################################################################
##### Setup Predixy as a Systemd Service
################################################################################################################
systemd(){
echo "/predixy_data/predixy/src/predixy /predixy_data/predixy/conf/predixy.conf" > /usr/sbin/predixy_start.sh
cat > /etc/systemd/system/predixy.service <<-EOF
[Unit]
Description=Predixy
[Service]
ExecStart=/bin/bash /usr/sbin/predixy_start.sh
[Install]
WantedBy=multi-user.target
EOF
sudo chmod 640 /etc/systemd/system/predixy.service
sudo systemctl daemon-reload
sudo systemctl enable predixy
}
################################################################################################################
##### Check Predixy Status
################################################################################################################
CheckStatus() {
if [ `ps -ef | grep predixy.conf | grep -v grep | wc -l` -gt 0 ]; then
echo ""
echo "************************************************************************"
echo "Predixy Setup Completed."
echo "************************************************************************"
echo ""
else
echo ""
echo "************************************************************************"
echo "Predixy Proxy is unable to start. Please check."
echo "************************************************************************"
echo ""
exit 1
fi
}
################################################################################################################
##### MAIN
################################################################################################################
echo -ne "
Menu:
=====
1) cluster
2) standalone
3) Exit
Please choose: "
read -r choice
case $choice in
1)
echo ""
echo "************************************************************************"
echo "You chose 1 - Cluster. Starting Predixy Setup."
echo "************************************************************************"
echo ""
sleep 3
InstallPredixy
cluster
systemd
systemctl start predixy
sleep 3
CheckStatus
;;
2)
echo ""
echo "************************************************************************"
echo "You chose 2 - Standalone. Starting Predixy Setup."
echo "************************************************************************"
echo ""
sleep 3
InstallPredixy
standalone
systemd
systemctl start predixy
sleep 3
CheckStatus
;;
3)
echo ""
echo "*********"
echo "再见"
echo "*********"
echo ""
exit 1
;;
*)
echo ""
echo "************************************************************************"
echo "Invalid Option. Please try again."
echo "************************************************************************"
echo ""
exit 1
esac
Cheers!
In this blog post, I will outline the steps to set up Grafana to monitor your Redis database.
Install Grafana
Go to Grafana website download page.
Example: For Red Hat, CentOS, RHEL, and Fedora(64 Bit).
Execute the following commands to make Grafana start automatically when the server is booted up.
sudo /bin/systemctl daemon-reload sudo /bin/systemctl enable grafana-server.service
Install Redis Plugin
The Redis Data Source for Grafana is a plugin that allows users to connect to any Redis database On-Premises and in the Cloud. It provides out-of-the-box predefined dashboards and lets you build customized dashboards to monitor Redis and application data.
grafana-cli plugins install redis-datasource
Start Grafana
Start Grafana.
sudo systemctl start grafana-server.service
Check Grafana Status.
sudo systemctl status grafana-server.service
Access Grafana Monitor Dashboard
Paste the server’s IP, plus port 3000 on your web browser to access Grafana Dashboar<Ipd.
<ServerIPaddress>:3000
The initial username and password is admin/admin.
Configure Data source
Go to Configuration > Data sources.
Add data source.
Search for “Redis“, then select the data source below.
Enter a name and address in the following format.
redis://your-redis-endpoint.ng.0001.sae1.cache.amazonaws.com:6379
Click Save and test and verify that the data source is working as expected.
Import Dashboard
This step will be performed one time only.
Go to Dashboards. Then click Import for both Redis and Redis Streaming.
View Dashboard
Go to Dashboard > Manage.
Choose Redis or Redis Streaming.
