We are running multiple instances of mariadb using mysqld_multi on RHEL Server. We want to allocate CPU, RAM based on their individual instance workloads. We had seen that configuring innnodb parameters will do that, but what are the exact parameters we need to configure to allocate CPU Cores, RAM to the specific instance. We want to do it at database level not at OS level. What is the way to do this ?
You can sort of achieve it at database level to a limited extent, but it will never be completely clear cut. For RAM, you can do it with global allocations (innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, aria_pagecache_buffer_size), but not for everything that is allocated per thread (join_buffer_size, sort_buffer_size) or per query (max_allowed_packet) or multiple times per query (tmp_table_size). Theoretical maximum memory usage can vastly outsize the global allocations if you run with a lot of threads. For CPU you could try to limit the number of threads (innodb_thread_concurrency), and that will limit the CPU usage to that many CPU cores, but if I remember correctly that setting went away with 10.6 release, and without it I don't think there is another way. So you are out of luck if you are not willing or able to use ulimit and taskset to set hard limits on memory and CPU core usage. On Fri, 21 Mar 2025 at 19:53, jaswanth.at.work--- via discuss <discuss@lists.mariadb.org> wrote:
We are running multiple instances of mariadb using mysqld_multi on RHEL Server. We want to allocate CPU, RAM based on their individual instance workloads. We had seen that configuring innnodb parameters will do that, but what are the exact parameters we need to configure to allocate CPU Cores, RAM to the specific instance. We want to do it at database level not at OS level. What is the way to do this ? _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Hi Gordan, In my case, we have a process where it is doing multiple concurrent requests to mariadb database. Sometimes with the default configuration mariadb is randomly crashing and doing a recovery again. We are seeing lost connection to mariadb errors sometimes not everytime. To avoid these type of issues, we thought of tuning mariadb parameters. But we are not sure which parameters needs to change and what percentage or values we need give for them. On Fri, 21 Mar, 2025, 11:40 pm Gordan Bobic, <gordan@shatteredsilicon.net> wrote:
You can sort of achieve it at database level to a limited extent, but it will never be completely clear cut.
For RAM, you can do it with global allocations (innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size, aria_pagecache_buffer_size), but not for everything that is allocated per thread (join_buffer_size, sort_buffer_size) or per query (max_allowed_packet) or multiple times per query (tmp_table_size). Theoretical maximum memory usage can vastly outsize the global allocations if you run with a lot of threads.
For CPU you could try to limit the number of threads (innodb_thread_concurrency), and that will limit the CPU usage to that many CPU cores, but if I remember correctly that setting went away with 10.6 release, and without it I don't think there is another way.
So you are out of luck if you are not willing or able to use ulimit and taskset to set hard limits on memory and CPU core usage.
On Fri, 21 Mar 2025 at 19:53, jaswanth.at.work--- via discuss <discuss@lists.mariadb.org> wrote:
We are running multiple instances of mariadb using mysqld_multi on RHEL
Server. We want to allocate CPU, RAM based on their individual instance workloads. We had seen that configuring innnodb parameters will do that, but what are the exact parameters we need to configure to allocate CPU Cores, RAM to the specific instance. We want to do it at database level not at OS level. What is the way to do this ?
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
On Fri, 21 Mar 2025 at 20:17, Jaswanth Kumar <jaswanth.at.work@gmail.com> wrote:
Hi Gordan,
In my case, we have a process where it is doing multiple concurrent requests to mariadb database. Sometimes with the default configuration mariadb is randomly crashing and doing a recovery again. We are seeing lost connection to mariadb errors sometimes not everytime. To avoid these type of issues, we thought of tuning mariadb parameters. But we are not sure which parameters needs to change and what percentage or values we need give for them.
You probably want to start with identifying the actual cause of the crash. The most common one is out-of-memory errors, but there are bugs that can cause a crash. What version of MariaDB are you using? -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
Right now, am using 10.6.21 version. I am getting MariaDB 10.6.21 Signal 11 Crash (Segmentation Fau MariaDB Configuration: [mysqld] lower_case_table_names=1 innodb_buffer_pool_size=10G innodb_log_file_size=2G innodb_log_buffer_size=256M innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT table_definition_cache=8000 table_open_cache=8000 port=3319 user=root sort_buffer_size=16M join_buffer_size=16M max_heap_table_size=1G tmp_table_size=1G max_connections=500 max_sp_recursion_depth=255 slow-query-log=1 slow-query-log-file=/home/admin/log/mariadb/query/db_slow_query_log.txt long-query-time=0 Attached is the error log... On Fri, 21 Mar, 2025, 11:57 pm Gordan Bobic, <gordan@shatteredsilicon.net> wrote:
On Fri, 21 Mar 2025 at 20:17, Jaswanth Kumar <jaswanth.at.work@gmail.com> wrote:
Hi Gordan,
In my case, we have a process where it is doing multiple concurrent
requests to mariadb database. Sometimes with the default configuration mariadb is randomly crashing and doing a recovery again. We are seeing lost connection to mariadb errors sometimes not everytime. To avoid these type of issues, we thought of tuning mariadb parameters. But we are not sure which parameters needs to change and what percentage or values we need give for them.
You probably want to start with identifying the actual cause of the crash. The most common one is out-of-memory errors, but there are bugs that can cause a crash. What version of MariaDB are you using?
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
It looks like you may be hitting this bug: https://jira.mariadb.org/browse/MDEV-36333 It would be useful to know what query triggers this crash. join_buffer_size of 16MB sounds absurdly high. Maybe you need it for really bad queries with sub-queries that create huge temporary tables that the optimizer can't figure out how to index but if you genuinely need it this big and it makes a difference, you really have much bigger problems that you should be directing your energy into solving. Did you calibrate what sort_buffer_size you actually need? Increasing sort buffer size as high as you have it set usually turns performance gains negative: https://www.percona.com/blog/impact-of-the-sort-buffer-size-in-mysql/ innodb_log_buffer_size - in 25 years of working with MySQL and MariaDB I have yet to see a single case where there is benefit to having it set as high as 256M, the most I have ever seen beneficial is around the 64MB mark. Do you have any measurements/evidence that you need innodb_log_buffer_size, sort_buffer_size and join_buffer_size set to the values you have? Also note that long_query_time = 0 will fundamentally limit you to about 30,000 QPS on modern hardware, assuming small queries - less if your queries are large, because slow log handling is single-threaded. If you are not peaking anywhere near 30K QPS, then it's not a problem. On Fri, 21 Mar 2025 at 20:38, Jaswanth Kumar <jaswanth.at.work@gmail.com> wrote:
Right now, am using 10.6.21 version. I am getting
MariaDB 10.6.21 Signal 11 Crash (Segmentation Fau
MariaDB Configuration:
[mysqld] lower_case_table_names=1 innodb_buffer_pool_size=10G innodb_log_file_size=2G innodb_log_buffer_size=256M innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT table_definition_cache=8000 table_open_cache=8000 port=3319 user=root sort_buffer_size=16M join_buffer_size=16M max_heap_table_size=1G tmp_table_size=1G max_connections=500 max_sp_recursion_depth=255 slow-query-log=1 slow-query-log-file=/home/admin/log/mariadb/query/db_slow_query_log.txt long-query-time=0
Attached is the error log...
On Fri, 21 Mar, 2025, 11:57 pm Gordan Bobic, <gordan@shatteredsilicon.net> wrote:
On Fri, 21 Mar 2025 at 20:17, Jaswanth Kumar <jaswanth.at.work@gmail.com> wrote:
Hi Gordan,
In my case, we have a process where it is doing multiple concurrent requests to mariadb database. Sometimes with the default configuration mariadb is randomly crashing and doing a recovery again. We are seeing lost connection to mariadb errors sometimes not everytime. To avoid these type of issues, we thought of tuning mariadb parameters. But we are not sure which parameters needs to change and what percentage or values we need give for them.
You probably want to start with identifying the actual cause of the crash. The most common one is out-of-memory errors, but there are bugs that can cause a crash. What version of MariaDB are you using?
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
-- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net
participants (3)
-
Gordan Bobic -
Jaswanth Kumar -
jaswanth.at.work＠gmail.com