Buy Percona ServicesBuy Now!

Where Do I Put ProxySQL?

Latest MySQL Performance Blog posts - July 20, 2017 - 11:57am

In this blog post, we’ll look at how to deploy ProxySQL.

ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. It was created for DBAs by René Cannaò, as a means of solving complex replication topology issues. When bringing up ProxySQL with my clients, I always get questions about where it fits into the architecture. This post should clarify that.

Before continuing, you might want to know why you should use this software. The features that are of interest include:

  • MySQL firewall
  • Connection pooling
  • Shard lookup and automated routing
  • Ability to read/write split
  • Automatically switch to another master in case of active master failure
  • Query cache
  • Performance metrics
  • Other neat features!
Initial Configuration

In general, you install it on nodes that do not have a running MySQL database. You manage it via the MySQL command line on another port, usually 6032. Once it is started the configuration in /etc is not used, and you do everything within the CLI. The backend database is actually SQLite, and the db file is stored in /var/lib/proxysql.

There are many guides out there on initializing and installing it, so I won’t cover those details here. It can be as simple as:

apt-get install proxysql

ProxySQL Architecture

While most first think to install ProxySQL on a standalone node between the application and database, this has the potential to affect query performance due to the additional latency from network hops.

 

To have minimal impact on performance (and avoid the additional network hop), many recommend installing ProxySQL on the application servers. The application then connects to ProxySQL (acting as a MySQL server) on localhost, using Unix Domain Socket, and avoiding extra latency. It would then use its routing rules to reach out and talk to the actual MySQL servers with its own connection pooling. The application doesn’t have any idea what happens beyond its connection to ProxySQL.

Reducing Your Network Attack Surface

Another consideration is reducing your network attack surface. This means attempting to control all of the possible vulnerabilities in your network’s hardware and software that are accessible to unauthenticated users.

Percona generally suggests that you put a ProxySQL instance on each application host, like in the second image above. This suggestion is certainly valid for reducing latency in your database environment (by limiting network jumps). But while this is good for performance, it can be bad for security.

Every instance must be able to talk to:

  • Every master
  • Every slave

As you can imagine, this is a security nightmare. With every instance, you have x many more connections spanning your network. That’s x many more connections an attacker might exploit.

Instead, it can be better to have one or more ProxySQL instances that are between your application and MySQL servers (like the first image above). This provides a reasonable DMZ-type setup that prevents opening too many connections across the network.

That said, both architectures are valid production configurations – depending on your requirements.

problem replicating my pcx 5.7 on an AWS instance

Lastest Forum Posts - July 20, 2017 - 7:51am
Good morning guys

I am having a problem replicating my pcx 5.7 on an AWS instance.

I've tried replicating over the internet and the VPC, the problems continue.

I can not get past these timeout errors

Does anyone have any ideas ?

Follow the logs.


Packets Pings
Loss% Snt Last Avg Best Wrst StDev
0.5% 3662 3.8 3.4 3.1 266.1 6.1

Transfer is at 15Mb.

Servers without firewall

### joiner

/var/lib/mysql# du -h
1.5G .


### joiner log

# service mysql start
2017-07-20T13:39:01.620686Z 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
2017-07-20T13:39:01.627005Z 0 [Note] WSREP: Member 0.0 (ip-x.x.x.x) requested state transfer from '*any*'. Selected 1.0 (teste-mha-01)(SYNCED) as donor.
2017-07-20T13:39:01.627024Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 55690)
2017-07-20T13:39:01.627046Z 2 [Note] WSREP: Requesting state transfer: success, donor: 1
2017-07-20T13:39:01.627070Z 2 [Note] WSREP: GCache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(a8d37d7e-5c3b-11e7-9fbf-36d50cc17d69:55690)
2017-07-20T13:39:04.034731Z 0 [Note] WSREP: (c9e807ea, 'tcp://0.0.0.0:4567') connection to peer c9e807ea with addr tcp://x.x.x.x:4567 timed out, no messages seen in PT3S
2017-07-20T13:39:04.534737Z 0 [Note] WSREP: (c9e807ea, 'tcp://0.0.0.0:4567') turning message relay requesting off
Job for mysql.service failed because a timeout was exceeded. See "systemctl status mysql.service" and "journalctl -xe" for details.
root@ip-x.x.x.x:/var/lib/mysql# systemctl status mysql.service
● mysql.service - LSB: Start and stop the mysql (Percona XtraDB Cluster) daemon
Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
Active: failed (Result: timeout) since Thu 2017-07-20 13:44:00 UTC; 8min ago
Docs: man:systemd-sysv-generator(8)
Process: 1211 ExecStart=/etc/init.d/mysql start (code=killed, signal=TERM)
Tasks: 15
Memory: 1.5G
CPU: 26.864s
CGroup: /system.slice/mysql.service
├─1253 /bin/sh /usr/bin/mysqld_safe
├─2243 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --wsrep-provider=/usr/lib/libgalera_smm.so --log-error=/var/log/mysql/mysql-error.log --open-files-limit=10
├─2254 sh -c wsrep_sst_rsync --role 'joiner' --address 'x.x.x.x' --datadir '/var/lib/mysql/' --defaults-file '/etc/mysql/my.cnf' --defaults-group-suffix '' --parent '2243' ''
├─2255 /bin/bash -ue /usr/bin/wsrep_sst_rsync --role joiner --address x.x.x.x --datadir /var/lib/mysql/ --defaults-file /etc/mysql/my.cnf --defaults-group-suffix --parent 2243
├─2339 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
├─4403 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
├─4409 rsync --daemon --no-detach --port 4444 --config /var/lib/mysql//rsync_sst.conf
└─6973 sleep 1

Jul 20 13:45:07 ip-x.x.x.x rsyncd[4398]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4402]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4403]: receiving file list
Jul 20 13:45:07 ip-x.x.x.x rsyncd[4401]: receiving file list
Jul 20 13:45:08 ip-x.x.x.x rsyncd[4404]: receiving file list
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: connect from ip-10-10-10-101.sa-east-1.compute.internal (10.10.10.101)
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: rsync to rsync_sst/./performance_schema from ip-10-10-10-101.sa-east-1.compute.internal (10.10.10.101)
Jul 20 13:45:10 ip-x.x.x.x rsyncd[4431]: receiving file list
Jul 20 13:45:37 ip-x.x.x.x rsyncd[4401]: rsync: connection unexpectedly closed (10869 bytes received so far) [generator]
Jul 20 13:45:37 ip-x.x.x.x rsyncd[4401]: rsync error: error in rsync protocol data stream (code 12) at io.c(226) [generator=3.1.1]

#### joiner conf

# cat /etc/mysql/my.cnf
[mysql]

# CLIENT #
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
federated
performance_schema

innodb_force_recovery=0

pxc_strict_mode = PERMISSIVE

# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
bind_address = 0.0.0.0
skip-name-resolve

# DATA STORAGE #
datadir = /var/lib/mysql/
tmpdir = /var/lib/mysql/tmp

# MyISAM #
key_buffer_size = 32M
myisam_recover_options = FORCE,BACKUP

# Path to Galera library
wsrep_provider = /usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3

wsrep_cluster_address = gcomm://10.10.10.101,x.x.x.x

# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW

# MyISAM storage engine has only experimental support
default_storage_engine = InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2

# Node #1 address
wsrep_node_address = x.x.x.x

# SST method
wsrep_sst_method = rsync

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 265Mb
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table = 1
innodb_buffer_pool_size = 4G
thread_pool_size = 36
innodb_doublewrite = 1
innodb_thread_concurrency = 0
innodb_max_dirty_pages_pct = 80
innodb_thread_concurrency = 0
innodb_buffer_pool_instances = 20
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
thread_pool_idle_timeout = 3600


binlog_row_event_max_size = 16M
binlog_cache_size = 16M

# Gcache
#wsrep_provider_options = "gcache.size=300M; gcache.page_size=300M; gmcast.segment=2; evs.send_window=512; evs.user_send_window=256; gcs.fc_limit=128; evs.inactive_timeout = PT90S; evs.suspect_timeout = PT30S"
wsrep_provider_options = " gcs.max_packet_size=1048576; evs.send_window=512; evs.user_send_window=256; evs.inactive_timeout = PT100S; evs.suspect_timeout = PT60S"

#myisam
#wsrep_replicate_myisam = 1

# Cluster name
wsrep_cluster_name = ev-cluster-teste

# Node Name
wsrep_node_name = ip-x-x-x-x

# Set to number of cpu cores.

wsrep_slave_threads = 8

# to enable debug level logging, set this to 1

wsrep_debug = 0

#innodb_print_all_deadlock = 1

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit = 1

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx = 1

# Generate fake primary keys for non-PK tables (required for multi-master and parallel applying operation)
wsrep_certify_nonPK = 1

#### Required for Galera
innodb_locks_unsafe_for_binlog = 1

# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 5
sync_binlog = 1
server_id = 1

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_S UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE #,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1

# CACHES AND LIMITS #
tmp_table_size = 256M
max_heap_table_size = 256M
query_cache_type = 0
query_cache_size = 0
max_connections = 100
thread_cache_size = 120
open_files_limit = 100000
table_definition_cache = 4096
table_open_cache = 4096
read_buffer_size = 8M
query_cache_limit = 1M
join_buffer_size = 6M
#table_cache = 2000

# LOGGING #
log_error = /var/log/mysql/mysql-error.log
#log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

general_log_file = /var/log/mysql/mysql-general.log
general_log = 1


#### donor

/var/lib/mysql# du -h
64G .


#### donor log

#
2017-07-20T14:07:53.707628Z 2 [Note] WSREP: DONOR thread signaled with 0
2017-07-20T14:07:53.787297Z 0 [Note] WSREP: Flushing tables for SST...
2017-07-20T14:07:53.794344Z 0 [Note] WSREP: Provider paused at a8d37d7e-5c3b-11e7-9fbf-36d50cc17d69:55690 (5)
2017-07-20T14:07:53.797108Z 0 [Note] WSREP: Table flushing completed.
2017-07-20T14:07:54.004707Z WSREP_SST: [INFO] Starting rsync of data-dir............
2017-07-20T14:07:55.810592Z 0 [Note] WSREP: (c4b642ee, 'tcp://0.0.0.0:4567') turning message relay requesting off
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]
rsync: read error: Connection timed out (110)
rsync error: error in socket IO (code 10) at io.c(802) [sender=3.1.0]

### network

net.core.somaxconn = 1000
net.core.netdev_max_backlog = 5000
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216
net.ipv4.tcp_rmem = 4096 12582912 16777216
net.ipv4.tcp_max_syn_backlog = 8096
net.ipv4.tcp_slow_start_after_idle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 10240 65535

fs.file-max=200000
kernel.sem=250 32000 100 1024
kernel.shmmax=4294967295
######
net.ipv4.tcp_retries2 = 2
net.ipv4.tcp_keepalive_time = 12
net.ipv4.tcp_keepalive_intvl = 2
net.ipv4.tcp_keepalive_probes = 9

vm.swappiness = 0
vm.dirty_ratio = 80
vm.dirty_background_ratio = 5
vm.dirty_expire_centisecs = 12000

event scheduler in replication master - slave

Lastest Forum Posts - July 20, 2017 - 5:08am
Hi there! I have a problem with percona mysql replication (master-slave) and events. When i create any event on master

CREATE EVENT `test_event`
ON SCHEDULE
EVERY 1 MINUTE
ON COMPLETION
PRESERVE
DO
insert into test1 (unix_time) values (NOW() + 0);

on master i see for this event STATUS: ENABLED and on slave STATUS: SLAVESIDE_DISABLED - this is ok, all works correct. But if i change roles between master and slave: on new master this event stay SLAVESIDE_DISABLED and on new slave - ENABLED. Why? Why event status don't change when roles changed? How i can do it automatically?
Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.