<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi, <br>
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.<br>
<br>
The caching technology can be from a simple HashMap lookup to
deployments of something like <a class="moz-txt-link-freetext" href="http://ehcache.org/">http://ehcache.org/</a> (memory database).
<br>
<br>
I prepared two simple scenarios:<br>
<br>
- oVirt engine, 2 hosts, 1 VM, running 15 minutes (Power on + Up
state)<br>
- oVirt engine, 2 hosts, 10 VMs from one pool, running 15 minutes
(Power on + Up state)<br>
<br>
Appending 2 spreadsheets with data about the most used SQL queries
(generated by PostgreSQL standard pg_statements_stat module). <br>
<br>
e.g. <br>
- 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)<br>
- most of the queries are wrapped by a PLSQL function so we see a
wrapper:<br>
<br>
<i>select * from getvdsgroupbyvdsgroupid($1, $2, $3)</i><br>
<br>
and near to it the 'real' query:<br>
<br>
<i>SELECT vds_groups_view.*</i><i><br>
</i><i> FROM vds_groups_view</i><i><br>
</i><i> WHERE vds_group_id = v_vds_group_id</i><i><br>
</i><i> AND (NOT v_is_filtered OR EXISTS (SELECT 1</i><i><br>
</i><i> FROM
user_vds_groups_permissions_view</i><i><br>
</i><i> WHERE user_id =
v_user_id AND entity_id = v_vds_group_id))</i><i><br>
</i><br>
Just as an example, I selected in yellow color some queries, which
probably do not change often, but are very frequent.<br>
<br>
Other tools like 'pg_top' can provide runtime statistics of db
processes (cpu, mem, locks, ... views).<br>
<br>
By enabling debug level logging of PostgreSQL we can check real
values to the queries.<br>
<br>
Of course, it would be useful to run such tests with many hosts and
VMs to predict scaling issues.<br>
<br>
More info about tools configuration:<br>
<a class="moz-txt-link-freetext" href="http://www.ovirt.org/Engine_database_performance_monitoring">http://www.ovirt.org/Engine_database_performance_monitoring</a><br>
<br>
Regards,<br>
Libor<br>
</body>
</html>