[Engine-devel] DB Performance Monitoring

[Libor Spevak]

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.

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20130219/eac53bed/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql_report20130217_1vm.ods
Type: application/vnd.oasis.opendocument.spreadsheet
Size: 27758 bytes
Desc: not available
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20130219/eac53bed/attachment-0002.ods>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql_report20130217_10vm_pool_2hosts.ods
Type: application/vnd.oasis.opendocument.spreadsheet
Size: 28977 bytes
Desc: not available
URL: <http://lists.ovirt.org/pipermail/devel/attachments/20130219/eac53bed/attachment-0003.ods>