On Wed, Sep 12, 2012 at 7:42 PM, Scott Marlowe wrote:
> On Wed, Sep 12, 2012 at 12:57 AM, Venkat Balaji
> wrote:
>
> > We are using PostgreSQL-9.0.1.
>
> You are missing almost 2 years of updates, bug fixes, and security fixes.
>
Thank you Scott, We are planning to upgra
Thank you Jeff !
My comments are inline.
> explain SELECT tv.short_code, tv.chn as pkg_subscription_chn,
> >tv.vert as pkg_vert, ubs.campaign_id as campaign,
> > 'none'::varchar as referer,
> >CAST('CAMPAIGNWISE_SUBSCRIBER_BASE' AS VARCHAR) as vn,
> > count(tv.msi
Hello Community,
I intend to understand further on PostgreSQL Index behavior on a "SELECT"
statement.
We have a situation where-in Index on unique column is not being picked up
as expected when used with-in the WHERE clause with other non-unique
columns using AND operator.
explain SELECT tv.sho
Thanks for your valuable inputs !
The cost is "13.88" to fetch 1 row by scanning an Primary Key
>> indexed column.
>>
>> Isn't the cost for fetching 1 row is too high ?
>>
>
> Not really. The "cost" is really just an estimate to rank alternate query
> plans so the database picks the least expensiv
>
> > The cost is "13.88" to fetch 1 row by scanning an Primary Key
> > indexed column.
> >
> > Isn't the cost for fetching 1 row is too high ?
>
> I don't know, how many index pages will need to be randomly accessed
> in addition to the random heap access? How many dead versions of
> the row will
Hello,
I am trying to understand the analysis behind the "cost" attribute in
EXPLAIN output.
postgres = # explain select * from table_event where seq_id=8520960;
QUERY PLAN
--
On Wed, Nov 9, 2011 at 8:16 PM, Scott Marlowe wrote:
> On Wed, Nov 9, 2011 at 2:25 AM, Venkat Balaji
> wrote:
> > Hello Everyone,
> > I could see the following in the production server (result of the "top" M
> > command) -
> > PIDUSER PR
Hello Everyone,
I could see the following in the production server (result of the "top" M
command) -
PIDUSER PR NI VIRTRES SHR S %CPU %MEM TIME+
COMMAND
25265 postgres 15 0 3329m 2.5g 1.9g S 0.0 4.0
542:47.83 postgres: writer process
The "wri
Oh yes.
Thanks a lot Robert !
Regards
VB
On Tue, Oct 25, 2011 at 7:47 AM, Robert Haas wrote:
> On Oct 24, 2011, at 8:16 AM, Venkat Balaji wrote:
> > Thanks Greg !
> >
> > Sorry for delayed response.
> >
> > We are actually waiting to change the checkpoint_seg
Thanks Greg !
Sorry for delayed response.
We are actually waiting to change the checkpoint_segments in our production
systems (waiting for the downtime).
Thanks
VB
On Wed, Oct 5, 2011 at 11:02 AM, Greg Smith wrote:
> On 10/04/2011 07:50 PM, Venkat Balaji wrote:
>
>> I was
cs on production.
This is all calculated after considering Vacuum and Analyze jobs are
executed.
Please comment !
Sorry if this is too confusing and too long.
Thanks
VB
On Wed, Sep 21, 2011 at 6:33 PM, Shaun Thomas wrote:
> On 09/20/2011 11:22 AM, Venkat Balaji wrote:
>
> Please help me
5, 2011 at 4:02 AM, Greg Smith wrote:
> On 10/04/2011 03:50 AM, Venkat Balaji wrote:
>
>> I had a look at the pg_stat_bgwriter as well.
>>
>
> Try saving it like this instead:
>
> select now(),* from pg_stat_bgwriter;
>
> And collect two data points, space a
Thanks Heikki !
Regards,
VB
On Tue, Oct 4, 2011 at 4:38 PM, Heikki Linnakangas <
heikki.linnakan...@enterprisedb.com> wrote:
> On 04.10.2011 13:50, Venkat Balaji wrote:
>
>> I have got a situation where in i see the production system is loaded with
>> the checkpoints an
rget = 0.5
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2
Looking forward for suggestions.
Thanks
VB
On Thu, Sep 29, 2011 at 12:40 PM, Venkat Balaji wrote:
> Hello Everyone,
>
> We are experience a huge drop in performance for one of our production
>
Hello Everyone,
Generally when it comes to query performance, I check how the vacuuming and
statistics collection is performed on Tables and Indexes hit by the query.
Apart from the above i check the code logic ( for any bad joins ) and column
statistics as well.
I got hold of two catalog tables
according to the type of IOs.
I will share my recommended plan in an different email thread.
Thanks again for this detailed explanation.
Regards,
VB
On Mon, Oct 3, 2011 at 9:45 PM, Kevin Grittner
wrote:
> Venkat Balaji wrote:
>
> > We CLUSTERED a table using mostly used Index. Ap
Hello,
Thanks for your suggestions !
We CLUSTERED a table using mostly used Index. Application is performing
better now.
Thanks
VB
On Tue, Sep 27, 2011 at 6:01 PM, Venkat Balaji wrote:
> Forgot to mention -
>
> Kevin,
>
> CLUSTER seems to be an very interesting concept
CPU load was hitting 100% constantly with high IOs.
We tuned some queries to decrease the CPU usage and everything is normal
now.
Thanks
VB
On Fri, Sep 30, 2011 at 10:52 AM, Venkat Balaji wrote:
> I did not calculate the IO behavior of the server.
>
> What i noticed for the logs is
Hi Tomas,
I will let you know about "check_postgres.pl".
We will explore "pgmonitor" as well.
The other tool we are working on is "pgwatch", we found this very useful.
Thanks
VB
On Wed, Sep 28, 2011 at 5:44 PM, Tomas Vondra wrote:
> On 28 Září 2011, 9:05, Gr
Thanks Greg !
Sorry, I should have put it the other way.
Actually, I am looking for any tool (if exists) which gets me the following
information with one installation or so.
Please see my replies below.
Thanks
VB
On Wed, Sep 28, 2011 at 12:35 PM, Greg Smith wrote:
> Venkat Balaji wr
intensive.
Thanks
VB
On Thu, Sep 29, 2011 at 10:22 PM, Merlin Moncure wrote:
> On Wed, Sep 28, 2011 at 12:06 PM, Venkat Balaji
> wrote:
> > Hello Everyone,
> > I am back with an issue (likely).
> > I am trying to create a table in our production database, and is taking 5
We had performed VACUUM FULL and ANALYZE on the whole database.
Yes, the CPU is ticking at 99-100% when i see the top command.
But, we have 8 CPUs with 6 cores each.
Thanks
VB
On Thu, Sep 29, 2011 at 12:44 AM, Scott Marlowe wrote:
> On Wed, Sep 28, 2011 at 11:06 AM, Venkat Balaji
>
Hello Everyone,
I have been working on PostgreSQL for quite a while (2 yrs) now.
I have got "PostgreSQL 9.0 High Performance" book today and quite excited to
go through it.
Please let me know any source where i can get more books on PG, I am
especially looking for books on PG internals, architec
Hello Everyone,
I am back with an issue (likely).
I am trying to create a table in our production database, and is taking 5
seconds.
We have executed VACUUM FULL and yet to run ANALYZE. Can i expect the CREATE
TABLE to be faster after ANALYZE finishes ?
Or is there anything serious ?
Please sh
this in Postgres. I think we can get
this using pg_stat_user_table, pg_statio_user_tables and pg_stats.
I will post the calculation once it i get it.
Thanks
VB
On Wed, Sep 28, 2011 at 6:25 AM, Craig Ringer wrote:
> On 09/28/2011 12:26 AM, Venkat Balaji wrote:
>
>> Thanks a lot Kevin
Thanks Kevin !!
I will have a look at the source tree.
Regards
VB
On Tue, Sep 27, 2011 at 10:45 PM, Kevin Grittner <
kevin.gritt...@wicourts.gov> wrote:
> Venkat Balaji wrote:
>
> > I would like to know the difference between "n_tup_upd" and
> > "n_tup_
I would like to know the difference between "n_tup_upd" and "n_tup_hot_upd".
Thanks
VB
On Tue, Sep 27, 2011 at 9:56 PM, Venkat Balaji wrote:
> Thanks a lot Kevin !!
>
> Yes. I intended to track full table scans first to ensure that only small
> tables or tables
Hello Everyone,
I am implementing a PostgreSQL performance monitoring system (to monitor the
below) which would help us understand the database behavior -
1. Big Full Table Scans
2. Table with high IOs (hot tables)
3. Highly used Indexes
4. Tables undergoing high DMLs with index scans 0 (with unu
8:02 PM, Kevin Grittner wrote:
> Venkat Balaji wrote:
>
> > I am preparing a plan to track the tables undergoing Full Table
> > Scans for most number of times.
> >
> > If i track seq_scan from the pg_stat_user_tables, will that help
> > (considering the la
Hello Everyone,
I am preparing a plan to track the tables undergoing Full Table Scans for
most number of times.
If i track seq_scan from the pg_stat_user_tables, will that help
(considering the latest analyzed ones) ?
Please help !
Thanks
VB
Forgot to mention -
Kevin,
CLUSTER seems to be an very interesting concept to me.
I am thinking to test the CLUSTER TABLE on our production according to the
Index usage on the table.
Will let you know once i get the results.
Regards,
VB
On Tue, Sep 27, 2011 at 5:59 PM, Venkat Balaji wrote
, Kevin Grittner <
kevin.gritt...@wicourts.gov> wrote:
> Venkat Balaji wrote:
>
> > If i got it correct, CLUSTER would do the same what VACUUM FULL
> > does (except being fast)
>
> CLUSTER copies the table (in the sequence of the specified index) to
> a new set of file
correct !
Thanks
Venkat
On Wed, Sep 21, 2011 at 11:27 PM, Greg Smith wrote:
> On 09/21/2011 12:13 PM, Venkat Balaji wrote:
>
>> I as a DBA, suggested to perform VACUUM FULL and RE-INDEXING + ANALYZE to
>> ensure that IO performance and Indexing performance would be good
>
It is very important to remove it from the WIKI page.
I ran it on production PG9.0 and it does not error out and displays numbered
output.
I noticed that, this works till PG-8.2 (as per the message).
Venkat
On Wed, Sep 21, 2011 at 8:25 PM, Shaun Thomas wrote:
> On 09/21/2011 09:12 AM, Tom Lan
y basis.
Sorry for the long email !
Looking forward for your help !
Thanks
Venkat
On Wed, Sep 21, 2011 at 7:27 PM, Kevin Grittner wrote:
> Shaun Thomas wrote:
> > Venkat Balaji wrote:
>
> >> I see lot of free spaces or free pages in Tables and Indexes.
> >> Bu
Could you please let us know if you have analyzed after the re-indexing is
done ?
This must show differences for only Indexes not the Tables.
For Tables, you need to do VACUUM FULL to show the difference.
Thanks
Venkat
On Wed, Sep 21, 2011 at 12:31 PM, AI Rumman wrote:
> I am using Postgresql
Can you please help me understand what "blkno" column refers to ?
Thanks
Venkat
On Wed, Sep 21, 2011 at 11:08 AM, Venkat Balaji wrote:
> Thank Everyone for your inputs !
>
> Mark,
>
> We are using 9.0, so, i should be able to make use of this "freespacemap"
Thank Everyone for your inputs !
Mark,
We are using 9.0, so, i should be able to make use of this "freespacemap"
contrib module and would get back to you with the results.
I was using below query (which i got it by googling)..
But, was not sure, if its picking up the correct information. I want
Hello Everyone,
I had posted a query in "GENERAL" category, not sure if that was the
correct category to post.
Please help me understand how to calculate free space in Tables and Indexes
even after vacuuming and analyzing is performed.
What i understand is that, even if we perform VACUUM ANALY
:
> On Thu, Sep 1, 2011 at 11:46 PM, Venkat Balaji
> wrote:
> > Hi Scott,
> > Log generation rate -
> > 500MB size of log file is generated within minimum 3 mins to maximum of
> 20
> > mins depending on the database behavior.
> > I did not understand the &quo
IPs in the log
file ?
Thanks
Venkat
On Tue, Aug 30, 2011 at 12:09 PM, Scott Marlowe wrote:
> On Mon, Aug 29, 2011 at 11:55 PM, Venkat Balaji
> wrote:
> > If i notice high IO's and huge log generation, then i think Greg
> Spileburg
> > has suggested a good idea of using tc
Missed out looping in community...
On Wed, Aug 31, 2011 at 5:01 PM, Venkat Balaji wrote:
> Could you help us know the tables and columns on which Indexes are built ?
>
> Query is performing sorting based on key upper(column) and that is where i
> believe the cost is high.
&g
Thanks to all for your very helpful replies !
As Greg Smith rightly said, i faced a problem of missing connections between
the runs. I even ran the cron every less than a second, but, still that
would become too many runs per second and later i need to take the burden of
calculating every thing fr
, information logged would be too high and is also IO intensive.
Thanks
Venkat
On Wed, Aug 24, 2011 at 4:39 PM, Adarsh Sharma wrote:
> **
> pg_stat_activity keeps track of all this information.
>
> select * from pg_stat_activity where datname='databasename';
>
>
>
>
es to catalog tables ).
Thanks
Venkat
On Wed, Aug 24, 2011 at 1:19 PM, Guillaume Lelarge
wrote:
> On Wed, 2011-08-24 at 13:05 +0530, Venkat Balaji wrote:
> > Hello Everyone,
> >
> > I am working on an alert script to track the number of connections with
> the
> &g
Hello Everyone,
I am working on an alert script to track the number of connections with the
host IPs to the Postgres cluster.
1. I need all the host IPs making a connection to Postgres Cluster (even for
a fraction of second).
2. I would also want to track number of IDLE connections, IDLE IN
TRANS
46 matches
Mail list logo
