Re: [systemd-devel] setting cpulimit/iolimit on mysql thread not entire process
Hello. On Tue, Nov 28, 2023 at 08:35:29AM +0200, Mantas Mikulėnas wrote: > 1) the limits are per-cgroup and the entire service is a single cgroup; They could create own service unit for the DB with Delegate=cpu,io and create a subtree manually. > 2) the threads are created by mysqld, That^^ Picking random threads out of the service and enforcing control on them without considering their internal dependencies is asking for^W^W creating hard-to debug troubles. > 3) afaik, in cgroups v2 it isn't even allowed for threads of a single > process to straddle multiple cgroups anymore. It depends on enabled controllers, threaded subtrees for controllers have thread granularity (cpu does, io not). > I'm not a DBA but I've heard that one common way to handle this would be to > create a separate MySQL instance (probably on a separate machine, even) > that would replicate all the data, for the heavy users to query. That sounds like much more sensible approach. SCNR, Michal signature.asc Description: PGP signature
Re: [systemd-devel] setting cpulimit/iolimit on mysql thread not entire process
On Tue, Nov 28, 2023 at 08:35:29AM +0200, Mantas Mikulėnas wrote: > On Tue, Nov 28, 2023 at 8:27 AM jai wrote: > > > I am able to set cpulimit, iolimit, etc for a process using its pid > > through cgroups v2. But for some threads of a single mysql process, how can > > I achieve that? > > > > You cannot; 1) the limits are per-cgroup and the entire service is a single > cgroup; 2) the threads are created by mysqld, not by systemd, and systemd > does not monitor and move service processes across cgroups once the service > is already running; 3) afaik, in cgroups v2 it isn't even allowed for > threads of a single process to straddle multiple cgroups anymore. > > I'm not a DBA but I've heard that one common way to handle this would be to > create a separate MySQL instance (probably on a separate machine, even) > that would replicate all the data, for the heavy users to query. (Or the > other way around, main instance for the heavy updates ⇒ replica for regular > queries.) Generally heavy analytical queries should be on a replica. The reason is that analytical queries are less likely to need the very latest data, whereas transactions probably do. -- Sincerely, Demi Marie Obenour (she/her/hers) Invisible Things Lab signature.asc Description: PGP signature
Re: [systemd-devel] setting cpulimit/iolimit on mysql thread not entire process
On Tue, Nov 28, 2023 at 8:27 AM jai wrote: > I am able to set cpulimit, iolimit, etc for a process using its pid > through cgroups v2. But for some threads of a single mysql process, how can > I achieve that? > You cannot; 1) the limits are per-cgroup and the entire service is a single cgroup; 2) the threads are created by mysqld, not by systemd, and systemd does not monitor and move service processes across cgroups once the service is already running; 3) afaik, in cgroups v2 it isn't even allowed for threads of a single process to straddle multiple cgroups anymore. I'm not a DBA but I've heard that one common way to handle this would be to create a separate MySQL instance (probably on a separate machine, even) that would replicate all the data, for the heavy users to query. (Or the other way around, main instance for the heavy updates ⇒ replica for regular queries.) -- Mantas Mikulėnas
