Thread: Announcement: pgbench-1.1 released
Hi,
I have made a small tool called "pgbench," that may be useful for
some stress testings and performance measurements for PostgreSQL.
pgbench can be obtained from:
ftp://ftp.sra.co.jp/pub/cmd/postgres/pgbench/
Pgbench runs small transactions similar to TPC-B concurrently and
reports the number of transactions actually done per second (tps).
Pgbench uses the asynchronous functions of libpq to simulate
concurrent clients. Example outputs from pgbench are shown below:
number of clients: 4
number of transactions per client: 100
number of processed transactions: 400/400
tps = 19.875015(including connections establishing)
tps = 20.098827(excluding connections establishing)
(above result was reported on my PowerBook with 603e CPU(180MHz), 80MB
mem running PostgreSQL 6.5.2 with -F option, and Linux 2.2.1 kernel)
Pgbench does not require any special libraries other than libpq. It
comes with a configure script and should be very easy to build.
*CAUTION*
pgbench will blow away tables named accounts, branches, history and
tellers. It is best to create a new database for pgbench before
running it.
BTW, the greatest tps I have ever seen was around 260 on a Linux box
running RedHat 6.0, having 2 Pentiumn III 600MHz CPUs and 512MB mem.
Enjoy,
---
Tatsuo Ishii
Hi, With many cooperators, I have made a *bash built-in command* for PostgreSQL called "pgbash". The pgbash is the system which offers the 'direct SQL'/'embedded SQL' interface for PostgreSQL by being included in the bash-2.x shell. Features of pgbash ------------------- 1.The pgbash has a function which is equivalent to psql except for the interactive input processing function. 2.It is possible that pgbash carries out the interactive input processing using the hysteresis editing function ( history,!, fc command ) of bash. 3.An output of retrieval result and database information of pgbash uses PSprint() which improved PQprint(). By PSprint(),it is possible to output by plain table type, plain table + outer frame type and HTML table type. And, it ispossible to display NULL value string(like '-NULL-') and bit zero string(like '-0-'). 4.It is possible that pgbash manipulates multiple databases using CONNECT, DISCONNECT and SET CONNECTION (or -d option ). 5.The pgbash has a function which substitutes the retrieval result for the shell variable using FETCH INTO statement. 6.It is possible to set CGI mode. In CGI mode, the pgbash switches the output to HTML, and read the datat by GET/POST method,and read the data of HTTP_COOKIE. 7.The pgbash sets "error code", "error message", "number of tuples", etc to the shell variable. Therefore, it is possibleto know the condition after the SQL execution. Details is as follows. http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html # I am very glad, if many people will use the pgbash. -- Regards. SAKAIDA Masaaki -- Osaka, Japan # Sorry, I am not good at English.
That's really cool !
I just install and played a little bit.
I found a minor problem :
I have to connect to any database to issue
exec_sql -l database
I have no my personal database
Here is a log:
bash-2.03$ exec_sql -l database
(-402)FATAL 1: Database megera does not exist in pg_database
bash-2.03$ exec_sql "connect to discovery"
# PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
# CONNECT TO discovery:5432 AS discovery USER megera
bash-2.03$ exec_sql -l database
# Databases list
datname |datdba|encoding|datpath
---------+------+--------+---------
template1| 505| 16|template1
apod | 11| 16|apod
I don't understand this requirements just to list all databases
Regards,
Oleg
On Fri, 1 Oct 1999, SAKAIDA wrote:
> Date: Fri, 01 Oct 1999 17:24:57 +0900
> From: SAKAIDA <sakaida@psn.co.jp>
> To: hackers@postgreSQL.org
> Cc: pgsql-interfaces@postgreSQL.org
> Subject: [HACKERS] pgbash-1.1.1 release
>
> Hi,
>
> With many cooperators, I have made a *bash built-in command* for
> PostgreSQL called "pgbash".
>
> The pgbash is the system which offers the 'direct SQL'/'embedded
> SQL' interface for PostgreSQL by being included in the bash-2.x
> shell.
>
>
> Features of pgbash
> -------------------
>
> 1.The pgbash has a function which is equivalent to psql except for
> the interactive input processing function.
>
> 2.It is possible that pgbash carries out the interactive input
> processing using the hysteresis editing function ( history, !,
> fc command ) of bash.
>
> 3.An output of retrieval result and database information of pgbash
> uses PSprint() which improved PQprint(). By PSprint(), it is
> possible to output by plain table type, plain table + outer frame
> type and HTML table type. And, it is possible to display NULL
> value string(like '-NULL-') and bit zero string(like '-0-').
>
> 4.It is possible that pgbash manipulates multiple databases using
> CONNECT, DISCONNECT and SET CONNECTION (or -d option ).
>
> 5.The pgbash has a function which substitutes the retrieval result
> for the shell variable using FETCH INTO statement.
>
> 6.It is possible to set CGI mode. In CGI mode, the pgbash switches
> the output to HTML, and read the datat by GET/POST method, and
> read the data of HTTP_COOKIE.
>
> 7.The pgbash sets "error code", "error message", "number of tuples",
> etc to the shell variable. Therefore, it is possible to know the
> condition after the SQL execution.
>
>
> Details is as follows.
> http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html
>
> # I am very glad, if many people will use the pgbash.
>
> --
> Regards.
>
> SAKAIDA Masaaki -- Osaka, Japan$B!!(B
> # Sorry, I am not good at English.
>
>
> ************
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> wrote: > That's really cool ! Thank you. > I just install and played a little bit. > I found a minor problem : > I have to connect to any database to issue > exec_sql -l database > I have no my personal database > Here is a log: > > bash-2.03$ exec_sql -l database > (-402)FATAL 1: Database megera does not exist in pg_database If CONNECT have not been executed yet, "CONNECT TO DEFAULT" will be automatically issued when -l option is executed. If user name is "megera", then "CONNECT TO DEFAULT" is equal to "CONNECT TO megera USER megera". > bash-2.03$ exec_sql "connect to discovery" > # PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1 > # CONNECT TO discovery:5432 AS discovery USER megera > > bash-2.03$ exec_sql -l database > # Databases list > > datname |datdba|encoding|datpath > ---------+------+--------+--------- > template1| 505| 16|template1 > apod | 11| 16|apod > > I don't understand this requirements just to list all databases This approach is equal to psql. # However, I consider that "CONNECT TO template1" may be better than "CONNECT TO <User_naame>" in the case of "-l database". -- Regard. SAKAIDA Masaaki -- Osaka, Japan # Sorry, I am not good at English.
Oleg Bartunov <oleg@sai.msu.su> wrote:
> Sakaida,
>
> sorry for bothering you but I didn't find
> TODO file and wondering what new features (if any)
> you plan to implement to pgbash.
Please give me your opinion. My plan is not yet the concrete,
but a few of my new plan are:
1. Improvement of function of an HTML output.
exec_sql -H -O "<TABLE option>" -O "*:<TH option1>" -O "name:<TH option2>" -O "*:<TD option3>" -O
"addr:<TDoption4>" -F "name:<font color='0000ff' size=4>%-7.7s</font>" -F "addr:<fonr
color='ff0000'>%s</font>" "select * from test where ..."
2. Snapshot cursor operation. exec_sql -c cur "select * from test where ..." exec_sql "open cur" exec_sql "fetch
incur into :r1, :r2" echo "r1=$r1 r2=$r2" exec_sql "close cur" # Declare cursor operation is already implemented.
> I'm a little boried to enclose SQL statements
> into double quotes. Is't really need ?
I think that it is necessary, because in the inside of the
double quotes, it can be used $variable and it is possible to
describe the SQL statement in the multiple line.
ex) exec_sql "select * from test where name='$DATA' and addr='$ADATA'"
> I'm doubt it's possible, because
> SQL statement must begins from valid SQL word.
>
> If it's impossible to avoid probably pgbash
> might have a possibility to redefine quote character,
> so user could use
> exec_sql [select * from test]
> Notice, no need to press shift key !
> I think with a little more effort this could be
> achieved without explicit redefining of
> quote character. But this is not a big problem,
> I could use alias to define [] as a quote characters
> just as an example:
> alias sql='exec_sql -Q "[]"'
> and then use sql instead of exec_sql.
>
I use alias too, but I do not know the method for aliasing
double quotes. ex) alias E='exec_sql'
(Shell program) ----------------------------------------------------- #!/usr/local/bin/bash function E { exec_sql
"$@"} ----------------------------------------------------- (KUBO Takehiro taught me this method.)
> Anyway, I'm just speculating about enhancement
> after playing for several hours with pgbash.
> I like it ! I hope a new idea.
--
Regards.
SAKAIDA Masaaki -- Osaka, Japan
# Sorry, I am not good at English.
Oleg Bartunov <oleg@sai.msu.su> wrote:
> Sakaida,
>
> sorry for bothering you but I didn't find
> TODO file and wondering what new features (if any)
> you plan to implement to pgbash.
Please give me your opinion. My plan is not yet the concrete,
but a few of my new plan are:
1. Improvement of function of an HTML output.
exec_sql -H -O "<TABLE option>" -O "*:<TH option1>" -O "name:<TH option2>" -O "*:<TD option3>" -O
"addr:<TDoption4>" -F "name:<font color='0000ff' size=4>%-7.7s</font>" -F "addr:<fonr
color='ff0000'>%s</font>" "select * from test where ..."
2. Snapshot cursor operation. exec_sql -c cur "select * from test where ..." exec_sql "open cur" exec_sql "fetch
incur into :r1, :r2" echo "r1=$r1 r2=$r2" exec_sql "close cur" # Declare cursor operation is already implemented.
> I'm a little boried to enclose SQL statements
> into double quotes. Is't really need ?
I think that it is necessary, because in the inside of the
double quotes, it can be used $variable and it is possible to
describe the SQL statement in the multiple line.
ex) exec_sql "select * from test where name='$DATA' and addr='$ADATA'"
> I'm doubt it's possible, because
> SQL statement must begins from valid SQL word.
>
> If it's impossible to avoid probably pgbash
> might have a possibility to redefine quote character,
> so user could use
> exec_sql [select * from test]
> Notice, no need to press shift key !
> I think with a little more effort this could be
> achieved without explicit redefining of
> quote character. But this is not a big problem,
> I could use alias to define [] as a quote characters
> just as an example:
> alias sql='exec_sql -Q "[]"'
> and then use sql instead of exec_sql.
>
I use alias too, but I do not know the method for aliasing
double quotes. ex) alias E='exec_sql'
(Shell program) ----------------------------------------------------- #!/usr/local/bin/bash function E { exec_sql
"$@"} ----------------------------------------------------- (KUBO Takehiro taught me this method.)
> Anyway, I'm just speculating about enhancement
> after playing for several hours with pgbash.
> I like it ! I hope a new idea.
--
Regards.
SAKAIDA Masaaki -- Osaka, Japan
# Sorry, I am not good at English.
This works like a charm. Thanks tons! ;) Clark On Fri, 1 Oct 1999, SAKAIDA wrote: > Hi, > > With many cooperators, I have made a *bash built-in command* for > PostgreSQL called "pgbash". > > The pgbash is the system which offers the 'direct SQL'/'embedded > SQL' interface for PostgreSQL by being included in the bash-2.x > shell. > > > Features of pgbash > ------------------- > > 1.The pgbash has a function which is equivalent to psql except for > the interactive input processing function. > > 2.It is possible that pgbash carries out the interactive input > processing using the hysteresis editing function ( history, !, > fc command ) of bash. > > 3.An output of retrieval result and database information of pgbash > uses PSprint() which improved PQprint(). By PSprint(), it is > possible to output by plain table type, plain table + outer frame > type and HTML table type. And, it is possible to display NULL > value string(like '-NULL-') and bit zero string(like '-0-'). > > 4.It is possible that pgbash manipulates multiple databases using > CONNECT, DISCONNECT and SET CONNECTION (or -d option ). > > 5.The pgbash has a function which substitutes the retrieval result > for the shell variable using FETCH INTO statement. > > 6.It is possible to set CGI mode. In CGI mode, the pgbash switches > the output to HTML, and read the datat by GET/POST method, and > read the data of HTTP_COOKIE. > > 7.The pgbash sets "error code", "error message", "number of tuples", > etc to the shell variable. Therefore, it is possible to know the > condition after the SQL execution. > > > Details is as follows. > http://www.psn.co.jp/PostgreSQL/pgbash/index-e.html > > # I am very glad, if many people will use the pgbash. > > -- > Regards. > > SAKAIDA Masaaki -- Osaka, Japan$B!!(B > # Sorry, I am not good at English. > > > ************ >