Bug 1329793 - [RFE] Provide a command line tool for DBA to manage and tune the engine database
Summary: [RFE] Provide a command line tool for DBA to manage and tune the engine data...
Keywords:
Status: CLOSED WONTFIX
Alias: None
Product: Red Hat Enterprise Virtualization Manager
Classification: Red Hat
Component: RFEs
Version: 3.6.5
Hardware: Unspecified
OS: Unspecified
unspecified
medium
Target Milestone: ---
: ---
Assignee: Scott Herold
QA Contact: Gil Klein
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2016-04-23 03:29 UTC by Marina Kalinin
Modified: 2020-02-14 17:49 UTC (History)
10 users (show)

Fixed In Version:
Doc Type: Enhancement
Doc Text:
Clone Of:
Environment:
Last Closed: 2016-05-15 06:12:21 UTC
oVirt Team: Infra
Target Upstream Version:


Attachments (Terms of Use)

Description Marina Kalinin 2016-04-23 03:29:08 UTC
Provide a command line tool for DBA  to manage and tune the engine database.

We need some tool that will allow DBA to manage and tune the engine database in a supported way, without breaking the whole engine.

If calling the tool: engine_db_control, we would have following, but not only, options:
engine_db_control vacuum
engine_db_control analyze
engine_db_control backup
engine_db_control reindex
etc

Ideas for additional verbs can be found here:
http://www.postgresql.org/docs/9.0/static/maintenance.html

Additional requirement, the tool should log into a dedicated log file each command it had run.

Comment 1 Oved Ourfali 2016-04-23 05:02:04 UTC
This is exactly the kind of utilities that the administrator should use the database tools, rather than us providing these tools by ourselves. It is similar for us not having tools to tune and maintain jboss, active directory, or any other service we rely on. 

IMO it should be closed as wontfix. 
Moran?

Comment 2 Moran Goldboim 2016-04-24 11:06:45 UTC
(In reply to Oved Ourfali from comment #1)
> This is exactly the kind of utilities that the administrator should use the
> database tools, rather than us providing these tools by ourselves. It is
> similar for us not having tools to tune and maintain jboss, active
> directory, or any other service we rely on. 
> 
> IMO it should be closed as wontfix. 
> Moran?

this fits my thoughts as well. this is out of the scope of REHV development and more into the scope of postgres.
Maybe it should be more of a documentation effort, to make sure postgres docs are clear enough for a DBA to perform above actions ontop of RHEVM DB.

Comment 3 Yaniv Kaul 2016-04-24 12:13:30 UTC
(In reply to Oved Ourfali from comment #1)
> This is exactly the kind of utilities that the administrator should use the
> database tools, rather than us providing these tools by ourselves. It is
> similar for us not having tools to tune and maintain jboss, active
> directory, or any other service we rely on. 
> 
> IMO it should be closed as wontfix. 
> Moran?

Any reason we will not run vacuum and reindex on upgrade? I vaguely remember some patch in the area (from Eli? Allon?)
Also, according to http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html#AUTOVACUUM - there is already auto-vacuuming enabled?


But to the point of the bug - I completely agree we should NOT create such a tool.

BTW, this RFE should be on oVirt, not RHEV. I've also made all the comments public. No customer-specific data here.

Comment 4 Tim Speetjens 2016-04-25 15:26:47 UTC
Though I agree this should not especially be part of RHEV or oVirt, this is a tool that may help both customers and support staff, tremendously.

In my opinion there's a gap that needs to be filled, giving RHEV (and other products using PostgreSQL) admins the ability to quickly do some basic DBA-like tasks like vacuum full, reindexing and so on. Even essential tasks like backup and restore may be put there instead too.

The reality is that more often than not, there is no DBA with PostgreSQL knowledge, in companies that deploy our products.

Comment 5 Marina Kalinin 2016-04-25 21:43:41 UTC
My argument is that we hide the database password from the user. We do not support any database operations outside of support case. That's why it could be a good idea to have such a tool to provide basic dba functionality.


Re: RFE. If I am going to attach a customer case to it, I do not really see a point creating first one usptream RFE and then cloning it to downstream.

Re: autovaccuum, it seems like it is running, based on the output of those queries on 3.6 database:
select last_autovacuum  from pg_stat_user_tables order by last_autovacuum;
select name, setting, unit, short_desc from pg_settings where name like 'autovacuum%';
So, we can assume it is doing its job.

Comment 6 Oved Ourfali 2016-04-27 06:36:20 UTC
Moran - as per your comment above, please close this one.
As per vaccum/reindex - Eli, any thoughts?
Another RFE should be opened on that, if it will be needed.

Comment 7 Eli Mesika 2016-05-01 09:14:09 UTC
(In reply to Oved Ourfali from comment #6)
> As per vaccum/reindex - Eli, any thoughts?

I am totally for using PG as is for this tasks and I think that good documentation will help people that have no PG DBA knowledge to do such tasks.
A tool for this tasks apply also bugs and maintenance overhead across OG versions and I see no real need for it.
PG has auto-vacuum on by default, in order to activate VACUUM manually, the user should be able to track the database and to decide which level of VACUUM is needed.
Same applies for re-indexing portions of the database 
So, as Yaniv, Oved & Moran I think such a tool is out of the scope of oVirt.

Please note that both  VACUUM/reindex may take long time (especially if not done on specific DB objects)and should be processed with care.

Comment 8 Martin Perina 2016-05-06 12:27:30 UTC
Moran, do you agree closing this one as WONTFIX?


Note You need to log in before you can comment on or make changes to this bug.