Re: [Engine-devel] DB Performance Monitoring

From: "Michael Kublin" <mkublin(a)redhat.com&gt; To: engine-devel(a)ovirt.org Sent: Tuesday, February 26, 2013 10:23:01 AM Subject: Re: [Engine-devel] DB Performance Monitoring ----- Original Message ----- > From: "Eli Mesika" <emesika(a)redhat.com&gt; > To: "Libor Spevak" <lspevak(a)redhat.com&gt; > Cc: engine-devel(a)ovirt.org > Sent: Friday, February 22, 2013 12:46:07 AM > Subject: Re: [Engine-devel] DB Performance Monitoring > > > > ----- Original Message ----- > > From: "Libor Spevak" <lspevak(a)redhat.com&gt; > > To: "Eli Mesika" <emesika(a)redhat.com&gt; > > Cc: engine-devel(a)ovirt.org, "Yair Zaslavsky" > > <yzaslavs(a)redhat.com&gt;, > > "Barak Azulay" <bazulay(a)redhat.com&gt; > > 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(a)redhat.com&gt; To: > > engine-devel(a)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 > > Due the following discussion I checked uses of "select * from getvdsgroupbyvdsgroupid($1, $2, $3)" query and find out that we have three call for such query during monitoring and we need only one, so the following patch should reduce a number of such queries during monitoring process http://gerrit.ovirt.org/#/c/12433/

The next step is, by my opinion, is start to use cache for not often changed object, VdsGroup is classical example of such object.

_______________________________________________ Engine-devel mailing list Engine-devel(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel