I'm trying to figure out how best to create a reporting-only instance of
Foreman. This is because the reports being run on my production cluster
have a tendency to overwhelm the DB and cause performance issues.
My current idea:
Roll out a slave DB that's read-only, roll out a Foreman instance reading
from the slave, tell all users to run reports against the r/o Foreman. I've
done the first two items.
My current problem:
Passenger won't start because the DB is read only.
[ E 2017-11-10 12:53:27.1852 5260/Tr age/Cor/App/Implementation.cpp:304 ]:
Could not spawn process for application /usr/share/foreman: An error
occurred while starting up the preloader.
Error ID: e350b8ee
Error details saved to: /tmp/passenger-error-uysq1A.html
Message from application: PG::Error: ERROR: cannot execute UPDATE in a
read-only transaction
: UPDATE "settings" SET "full_name" = NULL WHERE "settings"."category" IN
('Setting::DefaultHostgroup') AND "settings"."id" = $1
(ActiveRecord::StatementInvalid)
Is there an easy way to deal with this? While I'd love to find a magical
bit to flip (didn't find one listed in the docs), I realize there might be
some additional complexity here. For a limited deployment like this, I
would consider some form of LB for reads vs writes (limited to this foreman
instance).
Versions, OS, etc:
Foreman 1.12
PostgreSQL 9.4.9
RHEL 6
Puppet 3.8
(yes, I would love to upgrade, but I'm stuck on this setup for a little
bit.)
> I'm trying to figure out how best to create a reporting-only instance of
> Foreman. This is because the reports being run on my production cluster
> have a tendency to overwhelm the DB and cause performance issues.
>
> My current idea:
> Roll out a slave DB that's read-only, roll out a Foreman instance reading
> from the slave, tell all users to run reports against the r/o Foreman. I've
> done the first two items.
>
> My current problem:
> Passenger won't start because the DB is read only.
>
> [ E 2017-11-10 12:53:27.1852 5260/Tr age/Cor/App/Implementation.cpp:304 ]:
> Could not spawn process for application /usr/share/foreman: An error
> occurred while starting up the preloader.
> Error ID: e350b8ee
> Error details saved to: /tmp/passenger-error-uysq1A.html
> Message from application: PG::Error: ERROR: cannot execute UPDATE in a
> read-only transaction
> : UPDATE "settings" SET "full_name" = NULL WHERE "settings"."category" IN
> ('Setting::DefaultHostgroup') AND "settings"."id" = $1
> (ActiveRecord::StatementInvalid)
>
>
> Is there an easy way to deal with this? While I'd love to find a magical
> bit to flip (didn't find one listed in the docs), I realize there might be
> some additional complexity here. For a limited deployment like this, I
> would consider some form of LB for reads vs writes (limited to this foreman
> instance).
>
Hi Chris,
I don't think there is one: being able to write to a db has always been one
of the assumptions while running and I'm not sure adding the ability to run
read only is feasible long term (with enforcing this ability on all
potential plugins)
I'm not sure its possible, but if you could achieve the read-only mode only
on a subset of tables (I'm sure settings, users and audits would need to be
writable), this could be the way to go. Otherwise, periodic dumps to a
separate rw database would need to be used: maybe others have better
suggestions for this.
– Ivan
···
pá 10. 11. 2017 v 22:20 odesílatel Chris Baldwin
Versions, OS, etc:
Foreman 1.12
PostgreSQL 9.4.9
RHEL 6
Puppet 3.8
(yes, I would love to upgrade, but I’m stuck on this setup for a little
bit.)
···
On Friday, November 10, 2017 at 2:20:40 PM UTC-7, Chris Baldwin wrote:
>
> I'm trying to figure out how best to create a reporting-only instance of
> Foreman. This is because the reports being run on my production cluster
> have a tendency to overwhelm the DB and cause performance issues.
>
> My current idea:
> Roll out a slave DB that's read-only, roll out a Foreman instance reading
> from the slave, tell all users to run reports against the r/o Foreman. I've
> done the first two items.
>
> My current problem:
> Passenger won't start because the DB is read only.
>
> [ E 2017-11-10 12:53:27.1852 5260/Tr age/Cor/App/Implementation.cpp:304 ]:
> Could not spawn process for application /usr/share/foreman: An error
> occurred while starting up the preloader.
> Error ID: e350b8ee
> Error details saved to: /tmp/passenger-error-uysq1A.html
> Message from application: PG::Error: ERROR: cannot execute UPDATE in a
> read-only transaction
> : UPDATE "settings" SET "full_name" = NULL WHERE "settings"."category" IN
> ('Setting::DefaultHostgroup') AND "settings"."id" = $1
> (ActiveRecord::StatementInvalid)
>
>
> Is there an easy way to deal with this? While I'd love to find a magical
> bit to flip (didn't find one listed in the docs), I realize there might be
> some additional complexity here. For a limited deployment like this, I
> would consider some form of LB for reads vs writes (limited to this foreman
> instance).
>
> Versions, OS, etc:
> Foreman 1.12
> PostgreSQL 9.4.9
> RHEL 6
> Puppet 3.8
> (yes, I would love to upgrade, but I'm stuck on this setup for a little
> bit.)
>
Thanks James. Sadly, my budget for this is $0, so buying something is out
of the question.
Ivan - Sadly, periodic dumps aren't useful in this case - they take too
long to create (1.5 hours), exacerbating the load issues on the master…
unless I run 2 PGSQL instances on my backup server, where I pg_dump/restore
the contents from the R/O DB in to a R/W periodically and make sure reports
are run off of the "fresh" data. Not a clean solution, but a start. Thanks,
I hadn't thought about that until you mentioned periodic dumps!
···
On Sunday, November 12, 2017 at 9:23:22 PM UTC-5, James Shewey wrote:
>
> You could try using ScaleArc (https://www.youtube.com/watch?v=C0jj4iaSkNE),
> or if you think you could switch you DB to MariaDB you could use MaxScale (
> https://mariadb.com/products/technology/maxscale) to do read/write
> splitting to a database cluster.
>
> On Friday, November 10, 2017 at 2:20:40 PM UTC-7, Chris Baldwin wrote:
>>
>> I'm trying to figure out how best to create a reporting-only instance of
>> Foreman. This is because the reports being run on my production cluster
>> have a tendency to overwhelm the DB and cause performance issues.
>>
>> My current idea:
>> Roll out a slave DB that's read-only, roll out a Foreman instance reading
>> from the slave, tell all users to run reports against the r/o Foreman. I've
>> done the first two items.
>>
>> My current problem:
>> Passenger won't start because the DB is read only.
>>
>> [ E 2017-11-10 12:53:27.1852 5260/Tr age/Cor/App/Implementation.cpp:304
>> ]: Could not spawn process for application /usr/share/foreman: An error
>> occurred while starting up the preloader.
>> Error ID: e350b8ee
>> Error details saved to: /tmp/passenger-error-uysq1A.html
>> Message from application: PG::Error: ERROR: cannot execute UPDATE in a
>> read-only transaction
>> : UPDATE "settings" SET "full_name" = NULL WHERE "settings"."category" IN
>> ('Setting::DefaultHostgroup') AND "settings"."id" = $1
>> (ActiveRecord::StatementInvalid)
>>
>>
>> Is there an easy way to deal with this? While I'd love to find a magical
>> bit to flip (didn't find one listed in the docs), I realize there might be
>> some additional complexity here. For a limited deployment like this, I
>> would consider some form of LB for reads vs writes (limited to this foreman
>> instance).
>>
>> Versions, OS, etc:
>> Foreman 1.12
>> PostgreSQL 9.4.9
>> RHEL 6
>> Puppet 3.8
>> (yes, I would love to upgrade, but I'm stuck on this setup for a little
>> bit.)
>>
>
