<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 21.2.2013 17:11, Eli Mesika wrote:<br>
</div>
<blockquote
cite="mid:1813120861.5022837.1361463071783.JavaMail.root@redhat.com"
type="cite">
<pre wrap="">----- Original Message -----
</pre>
<blockquote type="cite">
<pre wrap="">From: "Libor Spevak" <a class="moz-txt-link-rfc2396E" href="mailto:lspevak@redhat.com"><lspevak@redhat.com></a>
To: <a class="moz-txt-link-abbreviated" href="mailto:engine-devel@ovirt.org">engine-devel@ovirt.org</a>
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 <a class="moz-txt-link-freetext" href="http://ehcache.org/">http://ehcache.org/</a> (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.
</pre>
</blockquote>
<pre wrap="">
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 <a class="moz-txt-link-freetext" href="http://www.ovirt.org/OVirt-DB-Issues/MaterializedViews">http://www.ovirt.org/OVirt-DB-Issues/MaterializedViews</a> 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.
</pre>
</blockquote>
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,
...), not sure about NATIVE implementation level status in
PostgreSQL, according to e.g. Oracle db capabilities (MV-logs +
M-views).<br>
<br>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<a href="http://wiki.postgresql.org/wiki/Materialized_Views">http://wiki.postgresql.org/wiki/Materialized_Views</a><br>
<br>
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.<br>
<br>
<blockquote
cite="mid:1813120861.5022837.1361463071783.JavaMail.root@redhat.com"
type="cite"><br>
<blockquote type="cite">
<pre wrap="">
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:
<a class="moz-txt-link-freetext" href="http://www.ovirt.org/Engine_database_performance_monitoring">http://www.ovirt.org/Engine_database_performance_monitoring</a>
Regards,
Libor
_______________________________________________
Engine-devel mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Engine-devel@ovirt.org">Engine-devel@ovirt.org</a>
<a class="moz-txt-link-freetext" href="http://lists.ovirt.org/mailman/listinfo/engine-devel">http://lists.ovirt.org/mailman/listinfo/engine-devel</a>
</pre>
</blockquote>
</blockquote>
<br>
</body>
</html>