[Engine-devel] DB Performance Monitoring

[Eli Mesika]

----- 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.


> 
> 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
>