Tuning proxysql

Bootleg Eighty Six

unread,

Sep 4, 2017, 9:01:14 PM9/4/17

to proxysql

René Cannaò

unread,

Sep 4, 2017, 9:08:01 PM9/4/17

to Bootleg Eighty Six, proxysql

Hi.

Can you please share any further details to understand what your tests do, and what is the environment?

What hardware are you using?

Are you running client/proxy/server all in the same box?

What is the level of parallelism? (how many connections?)
More questions may follow, but the more you describe your setup and your tests, the easier will be to find an answer.

Thanks.

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Bootleg Eighty Six

unread,

Sep 4, 2017, 9:28:54 PM9/4/17

to proxysql

The test are doing select statements for each database.
For example
use db1;
select price from table1 where expiry > date1 and expiry < date2;

We have about 5000 tests doing these same things for each DB and each table and we expect a certain number of results from each query.

These are using c++ library for mysql https://dev.mysql.com/doc/refman/5.7/en/mysql-real-connect.html

I am running the the proxy and slave on the same box

/etc/mysql/my.cnf
port 3307

/etc/proxysql.cnf
        interfaces = "0.0.0.0:3306;/var/run/proxysql/proxysql.sock"

mysql_servers = (
    {
        address="/var/run/mysqld/mysqld.sock"
        port=0
        hostgroup=1, max_connections=3000
        max_replication_lag=10
    }
)

The tests are running from cluster of 20 host so I guess we have max parallelism of 20 with each test making 1 connection.

Hope that's enough.

René Cannaò

unread,

Sep 4, 2017, 9:36:58 PM9/4/17

to Bootleg Eighty Six, proxysql

The first issue I see here is that without ProxySQL, mysqld can use all CPU resources, while with proxysql running on the same box there is CPU contention.

How many physical cores has this server?

How many databases/shards do you have in a single mysql server?

Bootleg Eighty Six

unread,

Sep 4, 2017, 10:18:17 PM9/4/17

to proxysql, boot...@gmail.com

Thanks for the prompt responses René . Much appreciated.
I don't think resource is an issue. htop shows low CPU usage and memory

Model name:            Intel(R) Xeon(R) CPU           X5675 @ 3.07GHz
CPU MHz:               3066.501
NUMA node0 CPU(s):     0-5
NUMA node1 CPU(s):     6-11

# free -h
             total       used       free     shared    buffers     cached
Mem:           47G        34G        12G       432M       354M        27G

I am not running a sharded DB. Everything is running on a single readonly slave.

Total size of DB is about 50G with about 80% of that data is in the top 50 databases out of a total of 150 databases.

One thing though, in order to test this, I changed /etc/hosts on test cluster to point to proxysql/mysql server.
So it might be a DNS resolution issue that slows things down. but I can't imagine that it will slow down this much.

This is what my stats look like.

hostgroup_id hostname port status weight compression max_connections max_replication_lag use_ssl max_latency_ms comment

1 /var/run/mysqld/mysqld.sock 0 ONLINE 1 0 3000 10 0 0

hostgroup srv_host srv_port status ConnUsed ConnFree ConnOK ConnERR Queries Bytes_data_sent Bytes_data_recv Latency_us
1 /var/run/mysqld/mysqld.sock 0 ONLINE 0 9 9 0 1776372 785147591 1509177464 192

Thanks.

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

Bootleg Eighty Six

unread,

Sep 5, 2017, 1:27:00 AM9/5/17

to proxysql, boot...@gmail.com

Checking with strace

socket(PF_INET, SOCK_STREAM, IPPROTO_TCP) = 22
connect(22, {sa_family=AF_INET, sin_port=htons(3306), sin_addr=inet_addr("10.1.0.10")}, 16) = 0
.....
setsockopt(22, SOL_IP, IP_TOS, [8], 4) = 0
setsockopt(22, SOL_TCP, TCP_NODELAY, [1], 4) = 0
setsockopt(22, SOL_SOCKET, SO_KEEPALIVE, [1], 4) = 0

.....
read(22, "Z\0\0\2\377\25\4#28000ProxySQL Error: Acc"..., 16384) = 94

Seems like Proxysql can keep it's connections to mysql server?

Bootleg Eighty Six

unread,

Sep 5, 2017, 4:05:55 AM9/5/17

to proxysql, boot...@gmail.com

OK. False alarm here.
Seems like I have an outdated user list and tests is using new user, hence, they were connecting and failing and then trying again.

I failed to catch this in the logs.

Thanks everyone.

Reply all

Reply to author

Forward