Script and tool to monitoring sessions

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Script and tool to monitoring sessions

abdujaparov
Hi,
I am new in Postgresql world and I always used oracle.
To monitoring sessions and check to tune database I always used, in Oracle, Oracle Enterprise Manager.
What is a similar tool for Postgresql? I need a free tool.

Moreover in Oracle to check sessions I use some reports:
awr
addm
ash

do some similar reports exist in postgresql?


Regards,
Angelo
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Jayadevan

What is a similar tool for Postgresql? I need a free tool.

There are no such free/community tools I am aware of .You could look at EnterpriseDB products. 

Moreover in Oracle to check sessions I use some reports:
awr
addm
ash

Have a look at reports from pgbadger( https://github.com/dalibo/pgbadger) . The reports are similar, though they aren't as exhaustive as awr/addm/ash.

Regards,
Jayadevan
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

hubert depesz lubaczewski-2
In reply to this post by abdujaparov
On Thu, Sep 24, 2015 at 03:41:06AM -0700, abdujaparov wrote:

> Hi,
> I am new in Postgresql world and I always used oracle.
> To monitoring sessions and check to tune database I always used, in Oracle,
> Oracle Enterprise Manager.
> What is a similar tool for Postgresql? I need a free tool.
>
> Moreover in Oracle to check sessions I use some reports:
> awr
> addm
> ash
> do some similar reports exist in postgresql?

Please consider telling us what information you need. Not everyone
(most?) is familiar with oracle and their tools, and while not everybody
might know what "awr" does, if you'll tell us what information you need
- there probably is a way to get it in pg.

Best regards,

depesz



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
Hi,
I need session class type:

IO
CPU
WAIT
CONCURRENCY
CLUSTER
CONFIGURATION
NETWORK
etc

and for each type of waits the wait event for example

cursor mutex x or hw contention or enq TX contention or undo segment extention or log file switch

I mentioned wait classes and wait events of Oracle because I know it.


Regards,
Angelo
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

amulsul
Try this:
 
Regards, Amul Sul



On Thursday, 24 September 2015 6:30 PM, abdujaparov <[hidden email]> wrote:


Hi,
I need session class type:

IO
CPU
WAIT
CONCURRENCY
CLUSTER
CONFIGURATION
NETWORK
etc

and for each type of waits the wait event for example

cursor mutex x or hw contention or enq TX contention or undo segment
extention or log file switch

I mentioned wait classes and wait events of Oracle because I know it.


Regards,
Angelo



--
View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867148.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice


Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Thomas Kellerer
In reply to this post by abdujaparov
abdujaparov schrieb am 24.09.2015 um 14:58:

> IO
> CPU
> WAIT
> CONCURRENCY
> CLUSTER
> CONFIGURATION
> NETWORK
> etc
>
> and for each type of waits the wait event for example
>
> cursor mutex x or hw contention or enq TX contention or undo segment
> extention or log file switch
>
> I mentioned wait classes and wait events of Oracle because I know it.

In general Postgres doesn't expose any "wait events" similar to the way Oracle does it.

And due to the difference in architecture many of them wouldn't exist in Postgres anyway.
e.g. everything around contention in the the shared pool, simply because Postgres doesn't
have the concept of a shared pool.

Also Postgres does not have an "undo tablespace", so there can't be any "undo segment extension"
The same is true for the "temp" tablespace - Postgres doesn't have that either the
way Oracle has it.

Cluster wait events also don't exist in Postgres because there is no RAC

Log file switches also don't exist. The most similar thing would probably be a checkpoint.
You can monitor them, by logging them:
http://www.postgresql.org/docs/current/static/runtime-config-logging.html#GUC-LOG-CHECKPOINTS

Obviously there are similar "waits" as the those that are covered by "enq: XX contention", but
they are not persisted anywhere (no ASH in Postgres), so you can only check this in a limited way
if you monitor pg_locks on a regular basis.

It would be better if you told us what underlying problem you are trying to solve, then
we can maybe suggest the corresponding tools or system views.




--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
This is my problem.


We must load  a lot of data in a postgresql database.

We tried to load 380000 rows in a not partitioned table on postgresql. To load this data we spent about 1,5 hours. The table has a serial field and the sequence cache is 10000.

After that we tried to load the same rows (380000) on a partitioned table. To load data we spent about 2,5 hours. The table is the same (serial filed and sequence cache is 10000). Following the postgresql manual to implement partitioning I created a father table, child tables, function to insert data in the correct child table and the trigger that calls the function.

In each cases I must monitor sessions on postgresql and I do not know how to monitor.


Regards,
Angelo


Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

David G Johnston
On Thu, Sep 24, 2015 at 10:39 AM, abdujaparov <[hidden email]> wrote:
This is my problem.


We must load  a lot of data in a postgresql database.

We tried to load 380000 rows in a not partitioned table on postgresql. To
load this data we spent about 1,5 hours. The table has a serial field and
the sequence cache is 10000.

After that we tried to load the same rows (380000) on a partitioned table.
To load data we spent about 2,5 hours. The table is the same (serial filed
and sequence cache is 10000). Following the postgresql manual to implement
partitioning I created a father table, child tables, function to insert data
in the correct child table and the trigger that calls the function.

In each cases I must monitor sessions on postgresql and I do not know how to
monitor.

​The documentation chapter titled: "​
Monitoring Database Activity
​" would seem like a good place to start.


The first view listed in that section is the main one for discovering what is currently running.

SELECT * FROM pg_stat_activity;

​David J.​

Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Thomas Kellerer
In reply to this post by abdujaparov
abdujaparov schrieb am 24.09.2015 um 16:39:

> This is my problem.
>
> We must load  a lot of data in a postgresql database.
>
> We tried to load 380000 rows in a not partitioned table on postgresql. To
> load this data we spent about 1,5 hours. The table has a serial field and
> the sequence cache is 10000.
>
> After that we tried to load the same rows (380000) on a partitioned table.
> To load data we spent about 2,5 hours. The table is the same (serial filed
> and sequence cache is 10000). Following the postgresql manual to implement
> partitioning I created a father table, child tables, function to insert data
> in the correct child table and the trigger that calls the function.
>
> In each cases I must monitor sessions on postgresql and I do not know how to
> monitor.
>

I just did a bulk load of well over 500000 rows using "copy from stdin" in roughly 30 seconds.
(The source file is > 600MB)

So there must be something off with the way you load the data.

How exactly are you loading those rows?
How many indexes do you have on the table?
Did you turn off autocommit?

You also usually don't need to set the sequence cache that high.

Thomas



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
We insert rows from a software that uses hibernate and there is only one final commit.
The software execute one insert per row.

Angelo
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Armand Pirvu (home)
Could it be that it does

-connect
- insert
- disconnect

connect and disconnect are key words


If yes, I would look into using pgbouncer


Just my beginner two cents



On Sep 24, 2015, at 11:27 AM, abdujaparov wrote:

> We insert rows from a software that uses hibernate and there is only one
> final commit.
> The software execute one insert per row.
>
> Angelo
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867206.html
> Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-novice mailing list ([hidden email])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice



--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
Hi,
there is only one connection that is closed after the commit.
We need to use the software to load data.
Angelo
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Thomas Kellerer
In reply to this post by abdujaparov
abdujaparov schrieb am 24.09.2015 um 18:27:
> We insert rows from a software that uses hibernate and there is only one
> final commit.
> The software execute one insert per row.
>
> Angelo
Ah, the mighty obfuscation layers.

You your problem is your application, not Postgres.

Not much you can do then, but you should at least check if batched inserts are enabled in Hibernate.

You alos might want to set "show_sql" to true, to verify that Hibernate isn't runnig
other statements as well that slow down the processing (e.g. a SELECT after each
INSERT or something similar stupid to obtain the generated sequence values)







--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Sergey Konoplev-2
In reply to this post by abdujaparov
On Thu, Sep 24, 2015 at 5:58 AM, abdujaparov <[hidden email]> wrote:
> IO
> CPU
> WAIT
> CONCURRENCY
> CLUSTER
> CONFIGURATION
> NETWORK
> etc

Check describe_*.sh and stat_*.sh scripts from pgcookbook
https://github.com/grayhemp/pgcookbook/tree/master/bin/.

> and for each type of waits the wait event for example

You might find log_lock_waits intersting here
http://www.postgresql.org/docs/9.4/static/runtime-config-logging.html#GUC-LOG-LOCK-WAITS.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
[hidden email]


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
Hi,
I am checking

SELECT * FROM pg_stat_activity;

during the ingestion process:

19028;"smapdb";1151;19029;"smap";"";"172.25.10.42";"";62921;"2015-09-25 10:30:31.523041+02";"2015-09-25 10:30:38.013215+02";"2015-09-25 10:34:17.075262+02";"2015-09-25 10:34:17.075295+02";f;"idle in transaction";;2765;"select nextval ('public.l3smap_partition_id_seq')"

This is the ingestion process that is running. I think that all the time is spent on the sequence is it correct?

Regards,
Angelo
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Jayadevan


On Fri, Sep 25, 2015 at 2:08 PM, abdujaparov <[hidden email]> wrote:
Hi,
I am checking

SELECT * FROM pg_stat_activity;

during the ingestion process:

19028;"smapdb";1151;19029;"smap";"";"172.25.10.42";"";62921;"2015-09-25
10:30:31.523041+02";"2015-09-25 10:30:38.013215+02";"2015-09-25
10:34:17.075262+02";"2015-09-25 10:34:17.075295+02";f;"idle in
transaction";;2765;"select nextval ('public.l3smap_partition_id_seq')"



To see how much time the sequence is taking, try something like 
select nextval('myseq') from generate_series(1,<replace withyour upper limit>); 
Please note that the sequence will go up to the high value once the SELECT is executed.

Regards,
Angelo



--
View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867339.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.


--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

Thomas Kellerer
In reply to this post by abdujaparov
abdujaparov schrieb am 25.09.2015 um 10:38:

> Hi,
> I am checking
>
> SELECT * FROM pg_stat_activity;
>
> during the ingestion process:
>
> 19028;"smapdb";1151;19029;"smap";"";"172.25.10.42";"";62921;"2015-09-25
> 10:30:31.523041+02";"2015-09-25 10:30:38.013215+02";"2015-09-25
> 10:34:17.075262+02";"2015-09-25 10:34:17.075295+02";f;"idle in
> transaction";;2765;"select nextval ('public.l3smap_partition_id_seq')"
>
> This is the ingestion process that is running. I think that all the time is
> spent on the sequence is it correct?
>

The column "query" only reflects the _current_ statement  if state = 'active' at the same time.

In the above result, the session is not doing anything ("idle") and the *last* statement it executed, was the "select nexval()".

The session is in "idle in transaction" which means that it started a transaction but has not yet committed it.






--
Sent via pgsql-novice mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Script and tool to monitoring sessions

abdujaparov
Hi,
I am trying to understand.

From hibernate we see that there is a begin transaction and there is only one final commit.

From Server status of pgAdmin I see that that the processes c


10505 smapdb smap 172.25.10.102:51835 2015-09-25 12:16:52+02 2015-09-25 12:16:56+02

execute select nextval ('public.L3SmAp_id_seq')

and status changes continuously from
'idle in transaction' and 'active'

After that I see two lock from pid 10505:

ExclusiveLock
AccessShareLock

both these locks are related to  select nextval ('public.L3SmAp_id_seq').

Angelo

Reply | Threaded
Open this post in threaded view
|

Last Chance to Defend Your Freedom

Robert Beyne
In reply to this post by amulsul


Dear Fellow U.S. Citizen,

As described in the letter below, this December, President Obama is planning to make the U.S. a state of a global government and is taking executive action to redistribute what we earn and own to all developing countries worldwide.

To keep this from happening, take action as soon as possible!

·         Please, read the letter below to understand what Obama and the U.N. are plotting to do against us and what your Senators and Representatives can do to stop him.

·         Forward this email unchanged to everyone you know or otherwise can contact.

·         On http://www.house.gov/, enter you zip code and press Go, or on http://www.house.gov/representatives/, click the first letter of your state, then click on the link (their name) to your Representative’s website. Navigate to your Representative’s contact page. Copy and paste the letter below into your Representative’s contact page, and send it to your Representative.

·         On http://www.senate.gov/senators/contact/, choose your state, and click the link to your Senator’s website’s contact page or website. Copy and paste the letter below into your Senator’s contact page, and send it to your Senator.

Your future depends on you. Make taking those steps a high priority. Thank you.

Here is the text of the letter …

 

SUBJECT: Last Chance to Defend Our Freedom – Impeach and Imprison Obama

Being a U.S. citizen, it is my civic duty and honor to write to you regarding a matter of grave national consequences.

This December, at the 21st meeting of the U.N. Framework Convention on Climate Change (UN FCCC) Conference of the Parties (COP-21) in Paris, France, President Obama intends to enter the U.S., that is to enter We the People, into a legally-binding agreement. This agreement will make the U.S. a state of a global government; the UN FCCC COP. This global government will make decisions that subvert our national sovereignty and the Constitution of the United States. It will assume control of the global economy; socializing it by monetizing greenhouse gas emission allowances and allocating those allowances to countries worldwide. To developing countries, it will allocate surplus allowances, but to the U.S. it will allocate less than we need to continue our present fossil fuel usage. President Obama and the COP will require We the People to purchase periodically increasing numbers of allowances from developing countries; redistributing increasing amounts of our hard earned money to foreigners through the cap-and-trade scheme that President Obama is imposing on us through executive actions. This will cause the price of every U.S. made product to increase, reduce our purchasing power, and drive us into entitlement programs such as Obama’s Making Work Pay. Furthermore, the COP will require the U.S. to revoke intellectual property rights of U.S. industries, businesses, and citizens, to redistribute our trade secrets and patents to developing countries where it will be used to create industry and businesses that will take away our jobs and dignity; also forcing us into financial hardship, heartache, and entitlement programs. When natural disasters occur in developing countries, instead of the citizens of those countries staying and rebuilding, the COP will require the U.S. to take them in as environmental refugees; giving them our land for compensation for their loss, and increasing the number of people competing for our jobs and being given our tax dollars through entitlement programs. Also, if all these actions and others cause the U.S. economy to crash, developing countries and the COP will continue to take what is ours with heartless disregard for our welfare. Obama’s and the UN FCCC’s plans against us are criminal, malevolent, and unconscionable.

When President Obama enters We the People into that legally-binding agreement with the UN FCCC, you shall take action. First, Senators, for the sake of our nation and to defend our future, reject that legally-binding agreement with the UN FCCC. Second, for waging political and economic warfare against We the People, that is for signing that agreement which will oppress us under a global government and for imposing a cap-and-trade scheme on us which will redistribute our money to the developing world, charge President Obama with treason according to Article III. Section 3. of the Constitution and with high crimes according to Article III. Section 4. of the Constitution. Representatives, impeach President Obama according to Article I. Section 2. of the Constitution and Senators, try his impeachment and impeach him according to Article I. Section 3. of the Constitution. Third, charge former President Obama with one count of capital theft for each U.S. citizen whose hard-earned income has already been stolen to fund green energy, cooperative R&D, and other projects in foreign countries and will be stolen under the UN FCCC and through Obama’s and the UN FCCC’s cap-and-trade scheme; so that he may be indicted, tried, judged, and incarcerated for multiple lifetime sentences without any opportunity for parole.

You have a decision to make regarding your service to your state constituency. Will you violate your oath to defend the Constitution of the United States from all enemies foreign and domestic, ratifying its subversion and that of our sovereignty, or will you reject further actions subverting it; defending our Constitution and our right to freedom and self-governance? Will you impose untold hardship and despair on We the People, or will you bring relief and restore hope? Will you be an accomplice to the plundering of our money, trade secrets, patents, industry, businesses, jobs, and land, or will you protect us from fraud and theft; rejecting any legally-binding agreement with the UN FCCC, impeaching President Obama, and charging him for his crimes against We the People?