headers
Ivanko B | 1 Aug 2010 01:24
Picon

Re: To PostgreSQL users

>I find PostgreSQL quite hard to tweak to get good performance. A fresh
>install is *very* slow, and database server optimization is rather
>hard. 

Me'm disagree. 8.x versions are clever by default & their optimizer is
very well, server parameter optimization is mainly needed for
migrating from 7.x versions for huge databases. 
For example, 8.x optimizer automatcally apply hash & merge jonn w/o
any special indexing.

> Compare that to a fresh install of Firebird 2.x which is fast
>without any tweaks.  I ran the same test suite (from tiOPF project),
>using SqlDB database components (included with FPC). PostgreSQL was
>very slow compared to Firebird - doing the exact same tests. I really
>like the features of PostgreSQL, and the admin tools are very good, I
>just don't know how to get good performance out of it. :-(  Any tips
>or good websites I can look at?
>

With a pleasue :) Assume that the main goal of optimization is to
exclude any "bottle necks". The main of them is disk i/o which is
thousands times as slower than CPU & memory. 
To minimize disk i/o, we can:

1) use minimal set of nececssary indexes - they allow to locate then
to load much smaller parts  of huge DB files, but be aware not to
index data spread evenly through index files (these file may grow very
rapidly ) - low enthropy data like operator codes, office codes,
payment types - since such inedexes may envolve full loading index
files ( the worst case is to retrieve index value from very begginning
(Continue reading)

Permalink | Reply | 4 comments |
headers
Ivanko B | 1 Aug 2010 02:11
Picon

Re: To PostgreSQL users

>>I find PostgreSQL quite hard to tweak to get good performance. A fresh
>>install is *very* slow, and database server optimization is rather
>>hard. 
>

PG is considered to be free ORACLE that's a backend for serving
multiple clein simulataneously. W/o special tricks, 10 clients may
cause 1000 times slow down due to unodreded moves of HDD heads to
serve between multiple clients. 
Although there're some low-level techiques to have the heads moving in
order - SCSI TCQ, AHCI NCQ, OS/2 HPFS386 etc but the core of putting
chaotic concurrency requests to some order within HDD is DB backend :)
How is the FireBird scalability ? Does it have proportional slow down
for multiple clients or suffer from the mentioned regression ?

But for a single client, nothing can be faster than SQLite3 :)

PS:
What PS also wins is easiness to write own DB functions - in "C"
(there's the good API with macroses), Perl, TCl, PL/TCL,... 
For example, one day, me needed to replace not jet-fast 
"where (substr(a.kgp,1,3) in ('024','165') and substr(b.kgp,1,3) in
('024','165')) and (substr(a.kgp,4) = substr(b.kgp,4)" with a faster
comparator, so a C-language fucntion was written which gave 8 times
speed improvement -  with call "where kgp_comp(a.kgp,b.kgp)".
Permalink | Reply | 3 comments |
headers
Ivanko B | 1 Aug 2010 02:24
Picon

Re: To PostgreSQL users

> 10 clients may

Surely, active ones ( up to several operations per minute) - people of
politic forums, porno sites, remote cashiers...
Permalink | Reply | 2 comments |
headers
Martin Schreiber | 1 Aug 2010 10:37
Picon

Re: tsequencelink vs filter

Martin Schreiber wrote:

> Martin Schreiber wrote:
> 
>> Autoinc primary key fields are updated automatically for MySQL and
>> Sqlite3 after applyupdate(). It should also be possible to use the
>> RETURNING clause in insert statement in tmsesqlquery.onapplyrecupdate
>> with a tsqlresult, field values are writeable in onapplyrecupdate. Hmm, I
>> think I should test this method first...
> 
> Trunk 3811 has tsqlquery.SQLInsert/SQLUpdate.options uso_refresh.
> RETURNING clause used in order to update the field values after executing
> the statement.

Removed in trunk 3814, please use the new tmse*field.providerflags1 property
and pf1_refresh where refreshing the field value after insert and update
can be activated individually.
Permalink | Reply | 0 comments |
headers
Ivanko B | 1 Aug 2010 16:27
Picon

Re: To PostgreSQL users

Well illustrtated "5 steps to PG performance":

http://www.pgexperts.com/document.html?id=36
Permalink | Reply | 1 comment |
headers
Graeme Geldenhuys | 1 Aug 2010 19:30
Picon
Gravatar

Re: To PostgreSQL users

On 1 August 2010 16:27, Ivanko B wrote:
> Well illustrtated "5 steps to PG performance":
>
> http://www.pgexperts.com/document.html?id=36

Thank you very much for all the information. I'll install the latest
PostgreSQL under a fresh Ubuntu 10.04 install and try it again with
our software.

As for PostgreSQL being like a "free Oracle".... As far as I know,
PostgreSQL is actually the basis for Microsoft SQL Server. Many years
ago, some other database server was forked. MS Sql Server and
PostgreSQL was created from that fork.

I'm a certified MS SQL Server developer, and while I tried PostgreSQL
before, I could actually see some similarities between the two
database servers. I don't know Oracle products at all, so I can't
comment on that.

--

-- 
Regards,
  - Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/
_______________________________________________
MSEide-MSEgui-talk mailing list
MSEide-MSEgui-talk <at> lists.berlios.de
https://lists.berlios.de/mailman/listinfo/mseide-msegui-talk
(Continue reading)

Permalink | Reply | 0 comments |
headers
Ivanko B | 1 Aug 2010 21:52
Picon

Re: To PostgreSQL users

>I'm a certified MS SQL Server developer, and while I tried PostgreSQL
>before, I could actually see some similarities between the two
>database servers. I don't know Oracle products at all, so I can't
>comment on that.

Regardless of their common origin, curently me see them very-very
different even in conceptions:
1) "select a.code,b.name from set_function(..) a, names_table b where
a=b.code"  returning  tables in PG
2) "IF.. SELECT ELSE.." etc construtions of M$ SQL 
...
1000)..
Permalink | Reply | 0 comments |
headers
Ivanko B | 2 Aug 2010 08:06
Picon

Wishes for PostgreSQL 8+: (TMSESQLTransaction: isolation level) & (TMSESQLQuery: named savepoint to return data if the query fails thus rollback-ed)

In more details:
================================
In PostgreSQL, transactions usually run at the READ COMMITTED
isolation level. If you need to avoid the problems present in READ 
COMMITTED, you can change isolation levels using the SET TRANSACTION
command. The syntax for the SET TRANSACTION
command is
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE };
The SET TRANSACTION command affects only the current transaction (and
it must be executed before the first DML[13]
command 
within the transaction). If you want to change the isolation level for
your session (that is, change the isolation level for future 
transactions), you can use the SET SESSION command:
[13]
A DML (data manipulation language) command is any command that can
update or read the data within a 
table. SELECT, INSERT, UPDATE, FETCH, and COPY are DML commands.
SET SESSION CHARACTERISTICS AS 
TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }

---------------------------

movies=# START TRANSACTION;
START TRANSACTION
movies=# INSERT INTO customers VALUES( 5, 'Kemp, Hans' );
INSERT 44272 1
movies=# SELECT * FROM customers;
customer_id |    customer_name     |  phone   | birth_date | balance
-------------+----------------------+----------+------------+---------
(Continue reading)

Permalink | Reply | 4 comments |
headers
Martin Schreiber | 2 Aug 2010 10:36
Picon

Re: Wishes for PostgreSQL 8+: (TMSESQLTransaction: isolation level) & (TMSESQLQuery: named savepoint to return data if the query fails thus rollback-ed)

Ivanko B wrote:

> In more details:
> ================================
> In PostgreSQL, transactions usually run at the READ COMMITTED
> isolation level. If you need to avoid the problems present in READ
> COMMITTED, you can change isolation levels using the SET TRANSACTION
> command. The syntax for the SET TRANSACTION
> command is
> SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE };

Please use tmsetransaction.Params property. Example:
"
ISOLATION LEVEL READ COMMITTED
READ ONLY
"

> ---------------------------
> 
> movies=# START TRANSACTION;
> START TRANSACTION
> movies=# INSERT INTO customers VALUES( 5, 'Kemp, Hans' );
> INSERT 44272 1
> movies=# SELECT * FROM customers;
> customer_id |    customer_name     |  phone   | birth_date | balance
> -------------+-----------------------+-----------+-------------+----------
> 3 | Panky, Henry         | 555-1221 | 1968-01-21 |    0.00
> 1 | Jones, Henry         | 555-1212 | 1970-10-10 |    0.00
> 4 | Wonderland, Alice N. | 555-1122 | 1969-03-05 |    3.00
> 2 | Rubin, William       | 555-2211 | 1972-07-10 |   15.00
(Continue reading)

Permalink | Reply | 3 comments |
headers
Ivanko B | 2 Aug 2010 12:54
Picon

Re: Wishes for PostgreSQL 8+: (TMSESQLTransaction: isolation level) & (TMSESQLQuery: named savepoint to return data if the query fails thus rollback-ed)

>Please use tmsetransaction.Params property. Example:
>"
>ISOLATION LEVEL READ COMMITTED
>READ ONLY

Wow, it's cool.  To the README ?

>I don't understand your request. MSEgui uses PostgreSQL savepoints if
>tmsepqconnection.options pqco_usesavepoint is set (default on) in order to
>avoid the nasty PostgreSQL message "Transaction has been aborted..." in
>case of an SQL error.
>If you want to use savepoints in your SQL statements use executedirect() or
>a tsqlscript.

Me mean a parameter of TMSESQLquery with name of savepoint to return
to if (Insert/Update/Delete)SQL or OnApplyRecUpdate or autobuilt
update SQL of the query fails. 
Not usable ?
Permalink | Reply | 2 comments |

Gmane