# MySQL Galera: Partition Maintenance Very Slow During Heavy Load — Fast After Restart **URL:** \ **Category:** MySQL & MariaDB\ **Created:** [May 12, 2025, 12:21pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910 "2025-05-12T12:21:55Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 12, 2025, 12:21pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/1 "2025-05-12T12:21:55Z")
Hi, I have been using dedicated MySQL servers configured in Galera cluster for my application data, the application uses many tables. The servers specs are model: Intel(R) Xeon(R) CPU E5-2686 v4 @ 2.30GHz Memory: 157GB Dual socket with 72 Cores. i have dedicated 100GB to buffer\_pool. My tables are partitioned by Range when i alter table to drop/add partitions the operation takes lots of time which highly impacts the solution access to DB as these operations locks tables. but when the server is restarted the buffer\_pool is empty these operations are quickly performed, old partitions are deleted and new ones are created. below are my main configuration for the mysql. innodb\_read\_io\_threads = 20 innodb\_write\_io\_threads = 20 innodb\_purge\_threads = 20 innodb\_thread\_concurrency = 40 innodb\_flush\_neighbors = 0 innodb\_io\_capacity = 1000000 innodb\_io\_capacity\_max = 10000000 innodb\_doublewrite = 0 innodb\_buffer\_pool\_size = 100G innodb\_flush\_method = O\_DIRECT innodb\_page\_cleaners = 15 innodb\_buffer\_pool\_instances = 15 #innodb\_force\_recovery=5 log\_timestamps = SYSTEM innodb\_max\_dirty\_pages\_pct=10 innodb\_lru\_scan\_depth = 256 transaction-isolation = READ-UNCOMMITTED performance\_schema=0 can anyone help how this issue can be tackled? what additional configuration can be set to make the partition operations faster when the DB is in use and buffer is full. i can provide additional logs/configs thank you. ---
**Author:** ![lalit.choudhary](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lalit.choudhary/32/51_2.png) [@lalit.choudhary](https://forums.percona.com/u/lalit.choudhary)\ **Post date:** [May 13, 2025, 8:04am UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/2 "2025-05-13T08:04:29Z")
Hello @mrehman786 > My tables are partitioned by Range when i alter table to drop/add partitions the operation takes lots of time which highly impacts the solution access to DB as these operations locks tables. > but when the server is restarted the buffer\_pool is empty these operations are quickly performed, old partitions are deleted and new ones are created. The 1st thing I would suggest is collecting and review pt-stlak samples when you see slowness issues when dropping/adding partitions. and review OS stats like IO/CUP usage and MySQL stats like innodbstatus, processlist output. In innodbstatus look for buffer pool usage, pending IO (read,writes) etc. pt-satlk sample collection: ```auto PTDEST=/tmp/pt/collected/$(hostname)/ mkdir -p "${PTDEST}/samples"; cd /tmp/pt; wget percona.com/get/pt-stalk; chmod +x pt-stalk; sudo ./pt-stalk --no-stalk --iterations=2 --sleep=30 --dest="${PTDEST}" --user=root --password=; ``` The objective is to look for OS resource saturation, on MySQL side processlist, buffer pool size and if current size is sufficient, locking, IO read writes, etc to find an issue. > **[InnoDB Performance Optimization Basics](https://www.percona.com/blog/innodb-performance-optimization-basics-updated)** > > These InnoDB performance optimizations work well for a wide range of applications, though the optimal settings do depend on the workload. ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 13, 2025, 12:03pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/3 "2025-05-13T12:03:03Z")
Thank you for your reply. I have observed the I/O stats through the prometheus/Grafana and iostat tool but everything look normal. I haven’t used this pt-stalk tool specifically. I will configure it and will let you know of it’s stats. Thank you ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 13, 2025, 4:19pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/4 "2025-05-13T16:19:59Z")
> [@mrehman786](#): > > innodb\_io\_capacity = 1000000 > innodb\_io\_capacity\_max = 10000000 These values are wildly out of spec. Please read over these two blogs: > **[Tuning MySQL/InnoDB Flushing for a Write-Intensive Workload](https://www.percona.com/blog/tuning-mysql-innodb-flushing-for-a-write-intensive-workload/)** > > This post is a short guide for tuning the various releases of InnoDB for a write intensive workload. It aims at fixing many common misconceptions. > **[Give Love to Your SSDs - Reduce innodb\_io\_capacity\_max!](https://www.percona.com/blog/give-love-to-your-ssds-reduce-innodb_io_capacity_max/)** > > Innodb\_io\_capacity\_max is often misunderstood, leading to performance issues and excessive wear leveling on SSDs in some edge cases. > [@mrehman786](#): > > innodb\_max\_dirty\_pages\_pct=10 This is incredibly low and will result in more aggressive disk IO due to rapid flushing. The default for this parameter is 90 (75 back in 5.7). Please set this back to something like 75. ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 14, 2025, 1:43pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/5 "2025-05-14T13:43:43Z")
Thank you for your reply i have tested multiple values for innodb\_max\_dirty\_pages\_pct but none helped. The problem is with the innodb\_buffer\_pool memory, if the buffer is full the alter table add/drop partition takes lot of time but after the restart it works well. Also i have dedicated SSDs configured in RAID0 for the datadir. that’s why i have set the innodb\_io\_capacity and capacity\_max variables high. but i have tested it with reduced values 20000 and 40000 as well. so far none has helped. ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 14, 2025, 2:17pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/6 "2025-05-14T14:17:08Z")
Update i have tested with different configuration for the innodb buffer pool size. as i have Galera cluster i have set one node with innodb\_buffer\_pool\_size with 25G. as i have all primary nodes in cluster if i execute the partition management script that creates daily partitions it takes far less time than on node with 100GB of buffer pool. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 14, 2025, 4:00pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/7 "2025-05-14T16:00:06Z")
> [@mrehman786](#): > > Also i have dedicated SSDs configured in RAID0 for the datadir. that’s why i have set the innodb\_io\_capacity and capacity\_max variables high Just because you have 10000 IOPs does not mean “set innodb io=10000”. If you read the blogs I linked above, you’ll see that doing this can cause performance degradations. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [May 14, 2025, 4:03pm UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/8 "2025-05-14T16:03:35Z")
> [@mrehman786](#): > > if the buffer is full the alter table add/drop partition takes lot of time but after the restart it works well. That makes perfect sense. When you drop tables/partitions, innodb has to scan through the buffer pool linked lists (LRU) and remove the associated pages. When the pool is empty (ie; restart), there’s nothing to scan. You also have many buffer pool instances, which can cause pages from the same table to land in different pools (due to partitioning), so there’s more internal locking/concurrency issues to manage. You might consider lowering the number of pool instances to 4, or 2, and see if that helps when the pool is not empty. ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 16, 2025, 6:53am UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/9 "2025-05-16T06:53:17Z")
I have set the pool instances to 4 but it has negatively impacted the performance, it has increased the time for alter operations instead of reducing the time.. ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 16, 2025, 6:55am UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/10 "2025-05-16T06:55:12Z")
Currently i have adopted the procedure to reduce the total innodb\_buffer\_pool size dynamically from 100G to 20G then perform the partition operation it quicly creates partitions then again i increase the innodb\_buffer\_pool\_size to 100G. im not sure if it’s the correct way to deal with it. trying to figure out other options. Any help would be highly appreciated… ---
**Author:** ![mrehman786](https://avatars.discourse-cdn.com/v4/letter/m/67e7ee/32.png) [@mrehman786](https://forums.percona.com/u/mrehman786)\ **Post date:** [May 23, 2025, 9:31am UTC](https://forums.percona.com/t/mysql-galera-partition-maintenance-very-slow-during-heavy-load-fast-after-restart/37910/11 "2025-05-23T09:31:30Z")
Update upon looking into the innodb engine status it was observed Adaptive hash index had only around 30% searches while no-hash searches were around 70% so i disabled the adaptive hash index, Also i have set the max\_dirty\_pages\_pct to 50% insetead of default 90 and saw huge performance improvement, Now partition operation takes around 4 mins instead of hours.