Thread: Script and tool to monitoring sessions
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 -- View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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
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
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.
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 (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
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.
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 -- View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867172.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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.
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
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.
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 (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice
Hi, there is only one connection that is closed after the commit. We need to use the software to load data. Angelo -- View this message in context: http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867210.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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)
On Thu, Sep 24, 2015 at 5:58 AM, abdujaparov <afmulone@gmail.com> 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 gray.ru@gmail.com
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
--
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.
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')"
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 (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
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.
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
--
View this message in context:
http://postgresql.nabble.com/Script-and-tool-to-monitoring-sessions-tp5867117p5867352.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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? |
|