[Engine-devel] DB Performance Monitoring

[Eli Mesika]

----- Original Message -----
> From: "Libor Spevak" <lspevak at redhat.com>
> To: "Eli Mesika" <emesika at redhat.com>
> Cc: engine-devel at ovirt.org, "Yair Zaslavsky" <yzaslavs at redhat.com>, "Barak Azulay" <bazulay at redhat.com>
> Sent: Thursday, February 21, 2013 6:50:58 PM
> Subject: Re: [Engine-devel] DB Performance Monitoring
> 
> 
> On 21.2.2013 17:11, Eli Mesika wrote:
> 
> 
> ----- Original Message -----
> 
> From: "Libor Spevak" <lspevak at redhat.com> To: engine-devel at ovirt.org
> Sent: Tuesday, February 19, 2013 5:44:10 PM
> Subject: [Engine-devel] DB Performance Monitoring
> 
> 
> Hi,
> I just wanted to share a partial result from testing of engine db
> load during operation (SQL queries frequencies). Maybe, we can
> decide later, if some SQL queries result CACHING can boost
> throughput for larger deployments.
> 
> The caching technology can be from a simple HashMap lookup to
> deployments of something like http://ehcache.org/ (memory database).
> 
> I prepared two simple scenarios:
> 
> - oVirt engine, 2 hosts, 1 VM, running 15 minutes (Power on + Up
> state)
> - oVirt engine, 2 hosts, 10 VMs from one pool, running 15 minutes
> (Power on + Up state)
> 
> Appending 2 spreadsheets with data about the most used SQL queries
> (generated by PostgreSQL standard pg_statements_stat module).
> 
> e.g.
> - 2nd row shows number of granted connections from the db pool (e.g.
> can be used to set optimal connection pool size) (check of
> connection health: select 1)
> - most of the queries are wrapped by a PLSQL function so we see a
> wrapper:
> 
> select * from getvdsgroupbyvdsgroupid($1, $2, $3)
> 
> and near to it the 'real' query:
> 
> SELECT vds_groups_view.*
> FROM vds_groups_view
> WHERE vds_group_id = v_vds_group_id
> AND (NOT v_is_filtered OR EXISTS (SELECT 1
> FROM user_vds_groups_permissions_view
> WHERE user_id = v_user_id AND entity_id = v_vds_group_id))
> 
> Just as an example, I selected in yellow color some queries, which
> probably do not change often, but are very frequent. Thanks Libor for
> taking te time to do that.
> The problematic queries are those that involves all kinds of
> *permissions* checks with the complicated & expensive permission
> handling views.
> I believe that using Snapshot Materialized Views as defined in
> http://www.ovirt.org/OVirt-DB-Issues/MaterializedViews will solve
> the major part of those problems.
> We are currently verifying this approach and my recommendation is to
> have all *permissions* views as Snapshot Materialized Views that are
> updated via a cron job. I think there could be performace
> improvement with M-views, of course. There is some overhead just
> with their maintainance, e.g. refreshing, updating existing
> dependent objects (table name renames, ...),

Refresh of *permission* views was tested with 2 views 160,000 records each and was about 2 sec

> not sure about NATIVE
> implementation level status in PostgreSQL, according to e.g. Oracle
> db capabilities (MV-logs + M-views).
> 
> http://wiki.postgresql.org/wiki/Materialized_Views

AFAIK Postgres is not going to implement that in the near future

> 
> Still, if the Engine gathers statistics nearly in real time, my
> question would be, if there is a need of round-trip to the database
> and back at all for frequent queries.

Depends what is the cost of this round-trip , if it is very cheep as I showed when I tested M-Views I think that DB query is still a good and natural choice

> 
> 
> 
> 
> 
> 
> Other tools like 'pg_top' can provide runtime statistics of db
> processes (cpu, mem, locks, ... views).
> 
> By enabling debug level logging of PostgreSQL we can check real
> values to the queries.
> 
> Of course, it would be useful to run such tests with many hosts and
> VMs to predict scaling issues.
> 
> More info about tools configuration:
> http://www.ovirt.org/Engine_database_performance_monitoring Regards,
> Libor
> 
> _______________________________________________
> Engine-devel mailing list Engine-devel at ovirt.org
> http://lists.ovirt.org/mailman/listinfo/engine-devel
>