{"id":345,"date":"2022-04-16T11:44:55","date_gmt":"2022-04-16T10:44:55","guid":{"rendered":"https:\/\/blog.hslracing.com\/myhome\/?p=345"},"modified":"2024-01-16T11:46:52","modified_gmt":"2024-01-16T11:46:52","slug":"percona-xtradb-cluster","status":"publish","type":"post","link":"https:\/\/blog.hslracing.com\/myhome\/2022\/04\/percona-xtradb-cluster.html","title":{"rendered":"Percona XtraDB Cluster"},"content":{"rendered":"

After playing with my Kubernetes cluster for a while and trying out various WordPress<\/a> and MySQL<\/a> installations, I was not happy with the performance.<\/p>\n

I had created a replicated MySQL<\/a> installation with a master and 2 slaves using the Bitnami<\/a> Helm<\/a> chart and then set up a WordPress<\/a> pod that used NFS mounted from internal Longhorn<\/a> storage, the performance was terrible – it took up to 30 seconds just to load the initial page, subsequent pages were not much better,<\/p>\n

I thought maybe it was the NFS storage on the WordPress<\/a> pod that was causing the issue, but after swapping this out for dedicated local Longhorn<\/a> storage, it made almost no difference.<\/p>\n

I thought maybe it was just that the Lenovo M92 Tiny servers I was using were not up to the task, I highly doubted this as they should be more powerful than the VMs I was running on my Dell R220 ii server, so I installed the same website on the M93 Tiny that I was using to run Minecraft<\/a> and Rancher<\/a> under Docker<\/a> and set up a MySQL<\/a> and WordPress<\/a> containers on there as well.<\/p>\n

This time the website took less than a second to load the first page and all subsequent pages were just as fast, so the problem is not the hardware.<\/p>\n

Next I decided to point the Kubernetes WordPress<\/a> pod at the database on the Lenovo M93, and this was almost as fast as the local Docker<\/a> based website, again it made little difference if I was using local disk or NFS disk from within the Kubernetes cluster.<\/p>\n

The problem it would seem is that the replicated MySQL<\/a> database is too slow running under Kubernetes for the website to be even vaguely performant.<\/p>\n

Now since I am currently using 3 of the servers as master nodes for the ETCD<\/a> database that seem to do next to nothing and K3s<\/a> is happy to use an external MySQL<\/a> database, I decided to rebuild the cluster, but this time using Percona<\/a> as the database on those same 3 servers and then install K3s<\/a> on the remaining 5 nodes, having them all as master\/agent nodes.<\/p>\n

I chose not to re-install from scratch as K3s<\/a> uninstalls pretty cleanly if you use the uninstall script.<\/p>\n

I updated\/upgraded the OS, created a file system for \/var\/lib\/mysql from the unused 75GB of OS disk and set about installing Percona<\/a> according to their installation instructions.<\/p>\n

After having installed on all 3 nodes and stopped the MySQL<\/a> instances, I needed to modify the configuration<\/a> file for my environment and also create a bunch of certificates<\/a> for encrypting the inter node traffic, this is all detailed on their website.<\/p>\n

I then needed to bootstrap<\/a> the database, join<\/a> the other nodes, stop the bootstrap node, and rejoin as a normal node so I would then have 3 nodes in my cluster.<\/p>\n

Now I needed a load balancer to sit in front of the database and for this I decided to use HAProxy<\/a> on my PFSense<\/a> firewall.<\/p>\n

I created a new Virtual IP (VIP) 192.168.99.70 on the LAN interface, then created a new backend that made use of all 3 nodes of the Percona<\/a> cluster and port 3306 as the database port with Round Robin load balancing. The health check method used is HTTP GET and an advanced per server pass thru setting of ‘port 9200’.<\/p>\n

The frontend listens using the VIP and port 3306 and type tcp and the Percona<\/a> backend I just created as its default backend.<\/p>\n

While HAProxy<\/a> can check MySQL<\/a> databases if you create a login on the database for it to check with, this will not validate that the servers are in sync with each other.<\/p>\n

For this I made use of the mysqlchk and clusterchk scripts written by Olaf van Zandwijk and Raghavendra Prabhu from their github<\/a> site.<\/p>\n

I needed to install xinetd<\/a>, as this was not part of my standard OS install and follow the instructions on the github<\/a> site, although I had to make a few changes.<\/p>\n

I put the clusterchk script in \/usr\/local\/bin, so had to update the \/etc\/xinetd.d\/mysqlchk and \/etc\/systemd\/system\/mysqlchk.service scripts accordingly.<\/p>\n

As I am using Ubuntu, the default database configuration file is not \/etc\/my.cnf, but \/etc\/mysql\/mysql.conf\/mysqld.cnf, so that had to be updated in the DEFAULTS_EXTRA_FILE definition. There was also a typo on the MYSQL_USERNAME definition where the user name was not being set correctly and needed ‘:=’ removing.<\/p>\n

Once I had proven that the script worked correctly locally and also via a browser on port 9200 for each of the database servers, I could enable the load balancer in HAProxy<\/a>.<\/p>\n

I can now access my Percona<\/a> database cluster via port 3306 using the load balancer VIP of 192.168.99.70.<\/p>\n

After trying to use this clustered database for my various web server environments I ultimately gave up on this solution.<\/p>\n

Every time I imported the saved backup, at some point ruing the ‘recovery’ the active node would switch and the import would then fail.<\/p>\n

All this effort and pain for something that was just too fragile to support what I was after.<\/p>\n

I ultimately managed to persuade independent copies of MySQL<\/a> and WordPress<\/a>, one per website work with a sensible response time, so that is what I have stuck with moving forwards.<\/p>\n

I have also moved from trying to load balance\/reverse proxy from HAProxy<\/a>, and instead use Traefik<\/a> inside the\u00a0K3s<\/a> cluster.<\/p>\n","protected":false},"excerpt":{"rendered":"

After playing with my Kubernetes cluster for a while and trying out various WordPress and MySQL installations, I was not happy with the performance. I had created a replicated MySQL installation with a master and 2 slaves using the Bitnami Helm chart and then set up a WordPress pod that used NFS mounted from internal…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5],"class_list":["post-345","post","type-post","status-publish","format-standard","hentry","category-uncategorised","tag-homelab"],"_links":{"self":[{"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/posts\/345","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/comments?post=345"}],"version-history":[{"count":1,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/posts\/345\/revisions"}],"predecessor-version":[{"id":448,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/posts\/345\/revisions\/448"}],"wp:attachment":[{"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/media?parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/categories?post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.hslracing.com\/myhome\/wp-json\/wp\/v2\/tags?post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}