[Maria-discuss] Out of sort memory, consider increasing server sort buffer size - after upgrade to 10.5.5
Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors. That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days. All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade. Thanks for reading and giving it a thought.
Thought it was worth adding, I only added the relevant details, i hope, as i've been dealing with scaling MariaDB and MySQL, with innodb, for a long time in both high data throughput and millions of sessions a day, so i've taken the normal steps to mitigate/investigate this on the server, btw is a EC2 C5XLarge, with NVMe SSDs. I'm not calling myself an expert, just looking for the non low hanging fruit. On Tue, Aug 25, 2020 at 7:24 PM Jeff Dyke <jeff.dyke@gmail.com> wrote:
Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors.
That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days.
All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade.
Thanks for reading and giving it a thought.
10.5.x still seem to be problematic. Is reverting an option for you? https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-... On Wed, 26 Aug 2020, 01:38 Jeff Dyke, <jeff.dyke@gmail.com> wrote:
Thought it was worth adding, I only added the relevant details, i hope, as i've been dealing with scaling MariaDB and MySQL, with innodb, for a long time in both high data throughput and millions of sessions a day, so i've taken the normal steps to mitigate/investigate this on the server, btw is a EC2 C5XLarge, with NVMe SSDs. I'm not calling myself an expert, just looking for the non low hanging fruit.
On Tue, Aug 25, 2020 at 7:24 PM Jeff Dyke <jeff.dyke@gmail.com> wrote:
Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors.
That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days.
All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade.
Thanks for reading and giving it a thought.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
It was actually at the end of my list. But its still there. Perhaps I need to move it up. Thanks for the link! I'll spin up another replica and downgrade it. Is there any notes out there about experience. Most of what i wanted out of 10.5 was galera improvements and not a lot of application level perks. Any successes or failures that you've seen. I can do it in a vacuum, and i will...but just in case. On Wed, Aug 26, 2020 at 12:53 AM Gordan Bobic <gordan.bobic@gmail.com> wrote:
10.5.x still seem to be problematic. Is reverting an option for you?
https://www.percona.com/blog/2020/08/14/evaluating-performance-improvements-...
On Wed, 26 Aug 2020, 01:38 Jeff Dyke, <jeff.dyke@gmail.com> wrote:
Thought it was worth adding, I only added the relevant details, i hope, as i've been dealing with scaling MariaDB and MySQL, with innodb, for a long time in both high data throughput and millions of sessions a day, so i've taken the normal steps to mitigate/investigate this on the server, btw is a EC2 C5XLarge, with NVMe SSDs. I'm not calling myself an expert, just looking for the non low hanging fruit.
On Tue, Aug 25, 2020 at 7:24 PM Jeff Dyke <jeff.dyke@gmail.com> wrote:
Unfortunately, I can't easily give you a repro case, or show you the queries. I upgraded a replica/DW server from 10.4.latest to 10.5.5. I did this on Friday night. We're not a high volume site (by design) so nothing would really pop up over the weekend. We use google data studio to present reports of aggregated data after the daily events have run. Any given report page will generate from 5-25 connections from datastudio, lots of joins, group bys etc. All of the daily events and reporting and ad-hoc querying by our data analyst was working fine on 10.4, but on 10.5.5, started generating "Out of sort memory, consider increasing server sort buffer size" errors.
That's the background, there were no query changes, no significant increase in data. I changed sort_buffer_size and made a couple other optimizations that fit our environment and table types. And i'll have more information in the next few days.
All that, leads me to a simple question, that I have not been able to find an answer to or missed something in the changelogs that would indicate this behaviour would have changed. Has anyone seen anything similar. Or does this ring a bell to a change I need to tune other than increasing sort_buffer_size. The only thing that changed was an upgrade.
Thanks for reading and giving it a thought.
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (2)
-
Gordan Bobic -
Jeff Dyke