Re: proposal for 9.5: monitoring lock time for slow queries

2014-08-13 13:59 GMT+02:00 MauMau <maumau307(at)gmail(dot)com>:

> From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
>
> isn't it too heavy?
>>
>
> Are you concerned about the impactof collection overhead on the queries
> diagnosed? Maybe not light, but I'm optimistic. Oracle has the track
> record of long use, and MySQL provides performance schema starting from 5.6.

partially, I afraid about total performance (about impact on IO) - when we
use a usual tables, then any analyses without indexes are slow, so you need
a indexes, and we cannot deferred index update. You should thinking about
retention policy - and without partitioning you got massive deletes. So I
cannot to imagine a usage of table based solution together with some higher
load. Our MVCC storage is not practical for storing only inserted data, and
some custom storage has no indexes - so this design is relative big project.

>
>
> I have just terrible negative experience with Vertica, where this design
>> is
>> used - almost all information about queries are available, but any query
>> to
>> related tables are terrible slow, so I am inclined to more simple design
>> oriented to log based solution. Table based solutions is not practical
>> when
>> you exec billions queries per day. I understand to motivation, but I
>> afraid
>> so it can be very expensive and slow on highly load servers.
>>
>
> Which do you mean by "query related to tables", the queries from
> applications being diagnosed, or the queries that diagnose the performance
> using statistics views?
>
> Could you elaborate on your experience with Vertica? That trouble may be
> just because Vertica's implementation is not refined.
>
>
sure - Vertica is not mature database. More it has only one storage type
optimized for OLAP, what is wrong for long catalog, and for working with
performance events too.

> I understand the feeling of being inclined to log based solution for its
> implementation simplicity. However, the server log is difficult or
> impossible to access using SQL queries. This prevents the development of
> performance diagnostics functionality in GUI administration tools. Also,
> statistics views allow for easy access on PAAS like Amazon RDS and Heroku.
>

I prefer a possibility to read log via SQL (maybe some FDW) than use tables
for storing log. These tables can be relative very large in few days - and
we cannot to write specialized engine like MySQL simply.

Pavel

>
> Regards
> MauMau
>
>