We are using helm chart for the percona deployment . The old chart that we were using was 1.12.2 and it was upgraded to 1.15.0 chart version . same goes for my percona operator as well
So current running percona db image version is “8.0.36-28.1”.
We have checked for the deprecations and except the authentication mechanism of caching_sha2_password there were no breaking changes at least in my view or maybe am wrong.?!!
Have found latency issue currently running 3 node clusters:
DB performance was too slow compared to older version. Webpages used to login within 3-5 sec where now post upgrading it takes 30-50sec . If you are going to point me to indexing stuff no here that’s not the case because we have enabled slow query log and did indexing for the slow queries which ever was available which was not really needed in previous version.
However was able to workaround with the replica count to 1 and we found that performance is back normal . However this does not look correct . Because I need my replicas also to be available and performance to be same as old.
any help would be much appreciated!!!
my.cnf
skip_name_resolve
innodb_buffer_pool_instances=8
innodb_flush_log_at_trx_commit=0
innodb_io_capacity=300
innodb_open_files="8000"
character_set_server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_allowed_packet=16M
max_binlog_size=1G
binlog_expire_logs_seconds=604800
max_connections=800
slow_query_log=1
pxc_strict_mode=MASTER
sync_binlog=0
wsrep_provider_options="gcs.fc_limit=10000; gcs.fc_factor=1.0"
innodb_redo_log_capacity=1G
for one of the env as soon as we reduced to 1 it was better performance . However for few it did not and. then there we have to increase innodb_redo_log_capacity which helped. And also I have noticed that after upgrade the wsrep_cluster_address shows gcomm however previously it used to point the k8s service name of all running nodes of pxc .
also my cluster is running with limits set as below
cpu: 8000m
memory: 28G
I can see this is still underutilised only so it does not look like memory problem
| wsrep_node_address | 10.8.xx.xx
| wsrep_node_incoming_address | percona-review-db-pxc-0.percona-review-db-pxc.review-general.svc.cluster.local:3306
| wsrep_node_name | percona-review-db-pxc-0
wsrep_cluster_address | gcomm://
wsrep_cluster_name | percona-review-db-pxc
| wsrep_data_home_dir | /var/lib/mysql/
| wsrep_provider_options | allocator.disk_pages_encryption = no; allocator.encryption_cache_page_size = 32K; allocator.encryption_cache_size = 16777216; base_dir = /var/lib/mysql/; base_host = 10.8.xx.xx; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 1; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.encryption = no; gcache.encryption_cache_page_size = 32K; gcache.encryption_cache_size = 16777216; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = galera.cache; gcache.page_size = 128M; gcache.recover = yes; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_auto_evict_threshold = 0.75; gcs.fc_auto_evict_window = 0; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 10000; gcs.fc_master_slave = no; gcs.fc_single_primary = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = ssl://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 10.8.142.15; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.wait_restored_prim_timeout = PT0S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 11; socket.checksum = 2; socket.recv_buf_size = auto; socket.send_buf_size = auto; socket.ssl = YES; socket.ssl_ca = /etc/mysql/ssl-internal/ca.crt; socket.ssl_cert = /etc/mysql/ssl-internal/tls.crt; socket.ssl_cipher = ; so
attaching few limited configs of wsrep since am unable to attach it completely due to size limitations