Re: [GENERAL] pgDay Asia / talks / lightning talks

[Satoshi Nagayasu]

Hi Dan,

2016-02-16 20:43 GMT+09:00 Daniel Pocock :
> Is this the place to ask questions about pgDay Asia[1] or is there
> another mailing list for it?  The mailing list link on the pgDay Asia
> web site just takes me to a marketing list[2].  The seasiapug list[3]
> looks very quiet.

> 2. 
> http://uptime.us2.list-manage.com/subscribe/post?u=8b6e2840d44be26e9f646b9f9&id=128a96a18a

This is the list I have set up, and it is intended to provide some
update information
about the conference and the web site, especially for the attendees.

And if you have any question or comments, please send to pgday-asia
[at] googlegroups.co.jp

Regards,
-- 
Satoshi Nagayasu 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] NoSQL -vs- SQL

[Satoshi Nagayasu]

On 2010/10/12 8:46, Carlos Mennens wrote:

Just wondering how you guys feel about NoSQL and I just wanted to
share the following article...

http://www.linuxjournal.com/article/10770

Looking to read your feedback and / or opinions.


Seems a nice article. I like it. :)

I think "NoSQL is a new implementation built with old technologies".
Computing paradigm (and the hype) is repeatable.

I know there are several trade-offs on making decisions of technology
design, such as "Traditional RDBMS", "In-Memory Datatabase",
"Key-Value Store" or something like that.

A few years ago, I heard that Michael Stonebraker said
"There is no new (theoretical) invention around the database technology.
The key is integration of existing technologies". I agree with that.
At that time, he was working for the C-store.

Anyway, NoSQL is grown as a kind of storage, not a database to process
business transactions (As the article mentioned, early MySQL users knew
an importance of web-scale storage). However, when NoSQL process more
critical transactions or critical user data, it needs to be ACID-compliant,
and needs to have several technologies around traditional RDBMSes.
For example, Cassandra is now having its write-ahead-logging.

So, from my viewpoint, NoSQL is a subset of traditional database
technologies, and I agree with that it would deliver values
in some use cases, because there are several trade-offs and overheads
on existing technologies in such use cases.

However, NoSQL is still lacking important features and/or properties to
process business transactions, and there are only few sites having needs
for true Facebook-size scalability.

Thanks,
--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Please Help...

[Satoshi Nagayasu]

Hi Gavin,

On 2010/11/04, at 17:58, Gavin Burrows  wrote:
> I'm sure you have had this question many times before but I feel as though I 
> have genuinely exhausted all of my option and followed all the advise I can 
> find online. 
> 
> During installation of Postgresql at the time when it tries to create the 
> account I get the message ' user account postgress cannot be created because 
> the password is too short or not complex enough'  at which point the install 
> is terminated. This is driving me nuts as it even says this when it chooses 
> the password for you which just seems bizarre.

Which tool are you using to install PostgreSQL?
Are you trying to install from source or some binary package?

And which platform are you using? Windows?

I guess I need to know which component produces the message.

Regards,
-- 
NAGAYASU Satoshi 

Re: [GENERAL] Understanding PG9.0 streaming replication feature

[Satoshi Nagayasu]

Hi Ben,

On 2010/12/22 7:46, Ben Carbery wrote:

FYI, not looking for a detailed how to here.. I have read the manual twice and 
just can't figure which sections are relevant. The manual seems to be trying to 
cover all uses simultaneously which is always going to get confusing :) For 
example do I need I need WAL archiving or not?


My blog entry would be a good entry point for you.  :)

5 steps to implement a PostgreSQL replication system
http://pgsnaga.blogspot.com/2010/05/5-steps-to-implement-postgresql.html

It was written to be a guide for building a simple master-slave config.

Please take a look, including the comments.

Thanks,


On 2010/12/22 7:46, Ben Carbery wrote:

FYI, not looking for a detailed how to here.. I have read the manual twice and 
just can't figure which sections are relevant. The manual seems to be trying to 
cover all uses simultaneously which is always going to get confusing :) For 
example do I need I need WAL archiving or not?

On Tue, Dec 21, 2010 at 2:40 PM, Ben Carbery mailto:ben.carb...@gmail.com>> wrote:

Hi,

I am having some trouble trying to figure out how to configure this 
particular scenario..

I have a pair of pg servers that I want to put in a Master/Standby 
configuration. Currently a script dumps the master db every hour, copies it to 
the standby, restores, and restarts the server. The aim is to replace the 
dumps/restores with streaming replication and ensure the standby is always up 
to date.

In this case writes are infrequent, but reads are constant, and I only need 
high availability for reads. I would ideally like both master and standby to be 
available simultaneously to allow load-balancing.
My confusion seems to be around the fact I don't need failover - my 
applications will detect a master down and immediately start using the standby, 
so there is no need to allow writes on the standby, they will just wait for the 
master to be available again - I am not sure what the minimum config needed for 
this scenario is..

cheers,

Ben







--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] why update is slower on my pc?

[Satoshi Nagayasu]

On 2010/12/26, at 21:35, sunpeng  wrote:
> so the writing speed on disk of pc is much faster than laptop, why the update 
> sql command is much slower than my laptop? what's the reason causing such 
> decrease?

Are those PostgreSQL versions and/or configurations completely same?
How about shared_buffers, or things like that?

-- 
NAGAYASU Satoshi 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] tsearch (or related) question

[Satoshi Nagayasu]

Hi all,

I'm looking for some tricky way for tsearch query.

Now, I know when a tsvector column has an array ('a', 'b', 'c'),
tsquery ('a&c') hits the row.

But I want to search rows with considering the order of tsvector
elements.

I want to hit a row when I use 'a&b' or 'b&c' in a tsquery,
but not 'a&c'.

Is this possible?

Regards,
-- 
NAGAYASU Satoshi 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

Hi,

I'm looking for some good visual query builder which can be used by
non-tech people for some ETL tasks. Do you have any recommendation?

Now, we're moving our data from Excel to PostgreSQL to deal with large
amount of data, and we need to process some ETL tasks, with using JOIN
and GROUP BY between tables, up to 10 tables on it.

Of course, I can write ad-hoc queries by myself. However, I'd like to
allow non-tech people to issue ad-hoc queries with using some visual
query builder.

I have already looked a query builder feature in pgAdminIII, but
I'm not sure whether I can use GROUP BY with it.

Do you have any experience or recommendation about visual query builder
for PostgreSQL?

Regards,
-- 
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

(2013/02/08 20:37), Russell Keane wrote:

I'm looking for some good visual query builder which can be used by non-tech 
people for some ETL tasks. Do you have any recommendation?

Now, we're moving our data from Excel to PostgreSQL to deal with large amount 
of data, and we need to process some ETL tasks, with using JOIN and GROUP BY 
between tables, up to 10 tables on it.

Of course, I can write ad-hoc queries by myself. However, I'd like to allow 
non-tech people to issue ad-hoc queries with using some visual query builder.

I have already looked a query builder feature in pgAdminIII, but I'm not sure 
whether I can use GROUP BY with it.

Do you have any experience or recommendation about visual query builder for 
PostgreSQL?


You can do joins in the PGAdmin3 query builder but I'm fairly sure you can't do 
group by's.

Apparently, SQL Manager from here:
http://www.sqlmanager.net/en/products/postgresql/manager/
Has a "Visual query builder allowing you to build complicated queries without any 
knowledge of SQL syntax"


Thanks.
I have started trying "SQL Query for PostgreSQL" from EMS, the company 
provides SQL Manager. I think "SQL Query" is the subset of the

SQL Manager, and would be suitable for our purpose.
(and a bit cheaper :)


I may be shot for even suggesting this (and it is a bit of a hassle) but you 
could recreate your table structure in MS Access and use its graphical query 
builder to generate your SQL. It does allow for group by's, counts, etc.


Yeah, I will also look at MS Access as a query builder, although I'm not 
familiar with the software so far.


Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

(2013/02/09 0:41), Adrian Klaver wrote:

On 02/08/2013 07:33 AM, Kevin Grittner wrote:

Satoshi Nagayasu  wrote:


Of course, I can write ad-hoc queries by myself. However, I'd
like to allow non-tech people to issue ad-hoc queries with using
some visual query builder.


You should probably take a look at http://htsql.org/

It is free open source software intended for "accidental
programmers" -- people who want to pull summarized data from a
database without learning SQL or needing rigorous training.  Its
development was partially funded by grants from foundations,
including the National Science Foundation.  It does support
PostgreSQL and most definitely support counts, sums, etc.  In fact,
it can automagically give you pretty summary graphs with the
ability to drill down to supporting detail.


I second this. I have been trying it out and it is proving quite useful.
The interesting part is that if you use the HTML interface you can get
the SQL sent to the server, helps you learn that also.


Very interesting.

If non-tech people can learn a simple query language for their analytics 
purpose, it would be worth trying.


I think some "abstraction layer" is needed between non-tech users
and DBMS to allow them to issue queries themselves.

I think some query builder could be one of the solutions, and
also some simple query language could be another solution.

I will look into it.

Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Visual query builder for PosgreSQL?

[Satoshi Nagayasu]

2013/02/17 1:17, Thomas Kellerer wrote:

Gauthier, Dave wrote on 16.02.2013 17:04:

Many, many (many) years ago, while working at DIGITAL EQUIPMENT
(before it bellied up), I worked with a relational DB they created
called "RDB".


RDB/VMS was actually the first relational database I ever worked with.

Boy, is that a long time ago...


I have never seen InstantSQL itself, but I had chances several times
to go the RDB technical seminars here in Japan, where I have found
that learning database technology is really exciting. :)

So, I wish I will be able to work with RDB (and VMS) someday. :)

Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Building 3rd-party contrib/extension on Windows?

[Satoshi Nagayasu]

Hi,

PostgreSQL has lots of useful modules/extentions even outside the core
distribution. I'm trying to find out how I can build such 3rd-party
contrib/extention on Windows.

For example, I'm using PostgreSQL on Windows, bulit and distributed by
EnterpriseDB, and I want to run pgTAP on it. AFAIK, there's no Windows
binary distribution for pgTAP. So, I guess I need to build this
extention myself. Other useful extentions as well.

Is there any good guide to build such 3rd-party contrib/extention
on Windows, particularly to work with EDB distribution? Or do you have
any experience which can be shared?

Regards,
-- 
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Where in the source code does postgres write to disk?

[Satoshi Nagayasu]

Hi,

2013/04/21 0:01, Mike Levine wrote:

According
tohttp://www.postgresql.org/docs/9.2/static/storage-file-layout.html

"When a table or index exceeds 1 GB, it is divided into gigabyte-sized
segments. The first segment's file name is the same as the filenode;
subsequent segments are named filenode.1, filenode.2, etc."

I was wondering where in the source code this is dealt with. I have been
searching for the last few hours but have had no luck


Any help guiding me to the location in the source code where postgres
writes the buffer to disk would be greatly appreciated.


See mdextend() in src/backend/storage/smgr/md.c.

That's the code which deals with segment files on extending a table,
and RELSEG_SIZE defines the segment size, 1GB by default.

Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Read data from WAL

[Satoshi Nagayasu]

Hi,

2013/07/15 21:45, Baldur Þór Emilsson wrote:

Are there any projects or standard procedures for reading the data from
the WAL to get a change log for the database (or without the WAL, using
some other method)? I have searched for information about this quite
thoroughly without luck, so I thought I'd try asking here before I
started to patch Postgres :)


See xlogdump if you use 9.2 or earlier.

https://github.com/snaga/xlogdump

If you're going to use 9.3, you can find pg_xlogdump in the contrib.

http://www.postgresql.org/docs/devel/static/pgxlogdump.html

Regards,
--
Satoshi Nagayasu 
Uptime Technologies, LLC. http://www.uptime.jp


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memcached for Database server

[Satoshi Nagayasu]

Hi,

2011/05/17 14:31, Adarsh Sharma wrote:

Rick Genter wrote:

On May 16, 2011, at 10:09 PM, Adarsh Sharma wrote:



Dear all,

I need to research on Memcache in the next few days.

What I want to know is it worth to have memcahed enable in our Mysql/ Postgres 
Production Servers.
We have databases from 20 to 230 GB and it's not the OLTP just a simple OLAP 
where data is fetched and stored in some meaningful format.


What are benefits&  why we used memcahed?

What are the bottlenecks to meet?



You need to read about memcached. Memcached is not something you "enable". You 
have to program to it.



Thanks Rick, just one question..

At what stage we need memcached & what is the purpose of using it.

I just want to know whether it is worth to use memcahced or not as per our 
requirements.


I just built a software to enable query caching for PostgreSQL
with using memcached, which adds a proxy layer.

http://pgsnaga.blogspot.com/2011/03/postgresql-query-cache-pqc.html

Please take a look.

Thanks,




--
Rick Genter
rick.gen...@gmail.com







--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Memcached for Database server

[satoshi . nagayasu]

Hi,

2011/5/18 Tatsuo Ishii :
>> How do you handle statements that rely on current_timestamp, random(),
>> etc? What about if their reliance is via a function? Is that just an
>> understood limitation of the cache, that it'll cache even queries that
>> don't really make sense to cache?
>
> Probably we should cache the result of a query which containts no
> functions or a query which only contains immutable functions.

>From my point of view, that's the trade off things.

I think database doesn't need to handle all situations and conditions.
In other words, "One size does not fit all."

Honestly, I'm not interested in building a complex and huge tool.
I love "Keep it simple, stupid" discipline. So, I just created a tiny tool,
which would be useful in many situations, not *all* situations.

In pqc, a programmer is still able to handle life cycle of the cache
with using hints. I think it's enough to solve many performance issues.
That's what I wanted to pqc.

Of course, if rich customers want me to invest *more integrated*
query cache for PostgreSQL, I will welcome them. :)

Regards,
-- 
NAGAYASU Satoshi 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ECPG - Some errno definitions don't match to the manual

[Satoshi Nagayasu]

Hi all,

I'm looking into some ecpg part of the official manual,
and I have found some strange things.

I'm now investigating SQLCODE and SQLSTATE, and I have found
that some of the errno definitions don't match to the manual.

For example, the manual says that ECPG_CONVERT_BOOL could be `-207'.
However, ECPG_CONVERT_BOOL is defined as `-211' in ecpgerrno.h.

> -207 (ECPG_CONVERT_BOOL)
> 
> This means the host variable is of type bool and the datum in the 
> database is neither 't' nor 'f'. (SQLSTATE 42804) 

http://www.postgresql.org/docs/9.0/static/ecpg-errors.html

> #define ECPG_NUMERIC_FORMAT   -207
> #define ECPG_CONVERT_BOOL -211

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/include/ecpgerrno.h?rev=1.27;content-type=text%2Fx-cvsweb-markup;only_with_tag=REL9_0_STABLE

What does it mean? The manual is not up to date?

Any suggestions?

Regards,
-- 
NAGAYASU Satoshi 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Monitoring Object access

[Satoshi Nagayasu]

On 2010/09/12 23:02, adi hirschtein wrote:

I'm coming from the Oracle side of the house and In oracle for instance, you 
use shared buffer as well, but you are still able to see which session is 
waiting for which blocks
and if one session is doing the "real" I/O then the other one wait on 'wait for 
other session" event so you are able to know who did the actual I/O
the reason behind it is that you want to check which objects is being heavily 
hit by which  business processes or users and then tier your storage 
accordingly.
I agree with your point about the OS buffer cache, I need to monitor it as well.
is there any place rather than pg_stat_activity that you think I should take a 
look at?


I think you should also look at pg_locks to know
which session is processing (or waiting on locks).

http://www.postgresql.org/docs/8.4/interactive/view-pg-locks.html

pg_locks table contains several lock information
including "lock dependencies" which you may need
to monitor session activities.

--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Why would a scan take so long?

[Satoshi Nagayasu]

These three queries may help you to understand what's going on.

EXPLAIN ANALYZE SELECT count(catalog.id) FROM catalog;
EXPLAIN ANALYZE SELECT count(catalog.id) FROM catalog WHERE flag=false;
EXPLAIN ANALYZE SELECT count(DISTINCT catalog.id) FROM catalog WHERE flag=false;

Regards,
-- 
NAGAYASU Satoshi 
 

--元のメッセージ--
送信者 : Michal Politowski
送信者: pgsql-general-ow...@postgresql.org
To: pgsql-general@postgresql.org
件名: [GENERAL] Why would a scan take so long?
送信: 2010/10/2 12:13 AM

EXPLAIN SELECT count(DISTINCT catalog.id) FROM catalog WHERE flag=false;
   QUERY PLAN
-
 Aggregate  (cost=1615927.27..1615927.28 rows=1 width=8)
   ->  Seq Scan on catalog  (cost=0.00..1603214.56 rows=5085084 width=8)
 Filter: (NOT flag)

SELECT pg_size_pretty(pg_relation_size('catalog'));
 pg_size_pretty 

 9380 MB

Nothing else is going on the system, during the query disk reads rise from
around 0 to > 100MB/s, so I would assume it should take a couple minutes
and it takes ten times longer:
Time: 1495549.716 ms

What am I missing?

-- 
Michal Politowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pglesslog for Postgres 9.1.1

[Satoshi Nagayasu]

Hi Louis,

2011/10/27 19:49, mailtolouis2020-postg...@yahoo.com wrote:

Hi,

I'm sorry I'm not good in C, anyone can help to put a patch or release a new 
version for that?



Regards
Louis


---

---

*From:* Tom Lane 
*To:* "mailtolouis2020-postg...@yahoo.com" 

*Cc:* Postgres 
*Sent:* Wednesday, October 26, 2011 3:42 PM
*Subject:* Re: [GENERAL] pglesslog for Postgres 9.1.1

"mailtolouis2020-postg...@yahoo.com " 
mailto:mailtolouis2020-postg...@yahoo.com>> writes:
 > remove.c:182: error: ‘XLOG_GIN_INSERT’ undeclared (first use in this 
function)
 > remove.c:182: error: (Each undeclared identifier is reported only once
 > remove.c:182: error: for each function it appears in.)
 > remove.c:184: error: ‘XLOG_GIN_VACUUM_PAGE’ undeclared (first use in 
this function)
 > remove.c:186: error: ‘XLOG_GIN_DELETE_PAGE’ undeclared (first use in 
this function)

That stuff got moved to gin_private.h in 9.1 ...

regards, tom lane


I'm taking part in.

Try this patch,

https://gist.github.com/1321650

and build as following.

$ make USE_PGXS=1 top_builddir=/path/to/postgresql-9.1.0

Regards,

--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Hope for a new PostgreSQL era?

[Satoshi Nagayasu]

2011/12/08 21:53, Tomas Vondra wrote:

- performance monitoring and diagnostics. It's way harder to find out
what's causing load on a busy Pg server or report on frequent/expensive
queries etc. Tooling is limited and fairly primitive. It's find, but
nowhere near as powerful and easy as some if the other DBs.


True. Greg Smith actually mentioned this as one of the frequently asked
features in his post about two weeks ago
(http://blog.2ndquadrant.com/en/2011/11/global-trends-in-deploying-pos.html).
I've started to build my own tool and got it somehow working for my needs,
and there are other tools available, but none of them is really a complete
solution. Would be nice to form a dev group that would work on this.


Seems a good point. I'm trying to build "a complete solution". :)

Anyway, one of the reasons of such difficulties to build "a complete solution"
is based on necessity of the support from the *entire* core code. Without the
core support, a complete solution would never be built. Obtaining LWLock
statistics or write I/O operations is actually pretty tough work for
"non-experienced" PostgreSQL DBA, like me. :)

For examples, I've been working on investigating PostgreSQL LWLock behaviors
precisely for a few weeks, and it could not be obtained within PostgreSQL
itself, therefore, I picked up SystemTap. However, SystemTap could not be
used in a production system, because it often kills the target processes. :(
How can I observe LWLocks in the production system?

There are several tools to monitor system behaviors around operating systems,
but it is far from understanding PostgreSQL behavior. And DBAs coming from
other RDBMSes, in particular proprietary RDBMSes, need it, because they've
already been using such facilities (or tools) in their RDBMSes.
That's the reason why we need more facilities to observe inside PostgreSQL.

In addition, one more reason of the difficulties is that experienced
PostgreSQL DBAs (or hackers) do not need such facilities in general,
because they can imagine how PostgreSQL works in such particular situation.

I still think we can implement (or enhance) for those facilities if we
focus on it, but I sometimes feel it's like "a chicken and egg situation".

Regards,
--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Hope for a new PostgreSQL era?

[Satoshi Nagayasu]

2011/12/10 10:54, Greg Smith wrote:

On 12/08/2011 09:48 AM, Satoshi Nagayasu wrote:

For examples, I've been working on investigating PostgreSQL LWLock behaviors
precisely for a few weeks, and it could not be obtained within PostgreSQL
itself, therefore, I picked up SystemTap. However, SystemTap could not be
used in a production system, because it often kills the target processes. :(
How can I observe LWLocks in the production system?


I decided about a year ago that further work on using SystemTap was a black hole: time 
goes in, nothing really usable on any production server seems to come out. It can be 
useful for collecting data in a developer context. But the sort of problems people are 
more interested in all involve "why is the production server doing this?", and 
as you've also discovered the only reasonable answer so far doesn't involve SystemTap; it 
involves DTrace and either Solaris or FreeBSD (or Mac OS, for smaller server hardware 
deployments). Since those platforms are problematic to run database servers on in many 
cases, that doesn't help very much.


Absolutely. SystemTap would be useful if I'm able to reproduce the situation
outside the production system. However, in most cases, it would be actually
difficult.


I'm planning to put that instrumentation into the database directly, which is 
what people with Oracle background are asking for. There are two underlying 
low-level problems to solve before even starting that:

-How can the overhead of collecting the timing data be kept down? It's really high in 
some places. This is being worked out right now on pgsql-hackers, see "Timing 
overhead and Linux clock sources"

-How do you log the potentially large amount of data collected without killing server 
performance? Initial discussions also happening right now, see "logging in high 
performance systems".

I feel this will increasingly be the top blocker for performance sensitive 
deployments in the coming year, people used to having these tools in Oracle 
cannot imagine how they would operate without them. One of my big pictures 
goals is have this available as a compile-time option starting in PostgreSQL 
9.3 in 2013, piggybacked off the existing DTrace support. And the earlier the 
better--since many migrations have a long lead time, just knowing it's coming 
in the next version would be good enough for some people who are blocked right 
now to start working on theirs.


I'm glad to hear that. I'm very interested in focusing on it,
and will follow the threads. Thanks.

--
NAGAYASU Satoshi 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general