Re: Performance problems???
От | Hannu Krosing |
---|---|
Тема | Re: Performance problems??? |
Дата | |
Msg-id | 3BDFB647.D3A8503D@tm.ee обсуждение исходный текст |
Ответ на | Performance problems??? (James Patterson <jpatterson@amsite.com>) |
Список | pgsql-hackers |
James Patterson wrote: > > I have observed some disturbing behavior with the latest (7.1.3) version of > PotgreSQL. > > In an application that I am working on with a team of folks, there is a > specific need to execute a series of SQL statements similar to those used in > the 'loaddb.pl' script included below. Without getting into better ways to > increment rowid's (this code is part of another tool that we are using), I'd > like to know why I get the following results with PostgreSQL and MySQL. > > In 3 separate runs I get the following PostgreSQL results: > > o 1 - 2000 records inserted in 12 seconds. > o 2001 - 4000 records inserted in 16 seconds. > o 4001 - 6000 records inserted in 20 seconds. > > You see, there is a clear performance degradation here that is associated > with the number of records in the database. It appears that the main culprit > is the update statement that is issued (see 'loaddb.pl' script below). This > performance behavior is not expected. Especially with so few rows in such a > small table. > > In 3 separate runs I get the following MySQL results: > > o 1 - 2000 records inserted in 6 seconds. > o 2001 - 4000 records inserted in 5 seconds. > o 4001 - 6000 records inserted in 6 seconds. > > You see, MySQL performs as expected. There is no performance degradation > here that is related to the number of records in the database tables. > > I have been a huge fan and advocate of PostgreSQL. I was stunned to see this > behavior. I am hoping that it is either a bug that has been fixed, or that I > can alter my PostgreSQL configuration to eliminate this behavior. > > I have an urgent need to resolve this situation. If I cannot solve the > problem soon, I will be forced to drop PostgreSQL in favor of MySQL. This is > not something that I wish to do. You really should us e a sequence. You will most likely need to change the way you create sequence numbers even for mysql as the following is not safe on non-transactional DB. > my $inc_id = $dbh->prepare("update control set next_id = next_id + 1"); > my $get_id = $dbh->prepare("select next_id from control"); if two backends happen to interleave their queries 1> my $inc_id = $dbh->prepare("update control set next_id = next_id + 1"); 2> my $inc_id = $dbh->prepare("update control set next_id = next_id + 1"); 1> my $get_id = $dbh->prepare("select next_id from control"); 2> my $get_id = $dbh->prepare("select next_id from control"); then both will get the same next_id which is probably not what you want. ------------- Hannu
В списке pgsql-hackers по дате отправления: