On Thu, Dec 2, 2010 at 3:36 AM, Mario Splivalo
mario.spliv...@megafon.hr wrote:
On 12/01/2010 09:43 AM, Pierre C wrote:
Note that in both cases postgres reports that the FK checks take 92-120
milliseconds... which is a normal time for about 4000 rows.
Inserting 4000 lines with just a few
On 12/01/2010 10:43 PM, Pierre C wrote:
On Wed, 01 Dec 2010 18:24:35 +0100, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
There is a operating system which comes with a very decent extent
based file system and a defragmentation tool,
Mario Splivalo mario.spliv...@megafon.hr wrote:
It is OT, but, could you please shead just some light on that?
Part of my next project is to test performance of pg9 on both
windows and linux systems so I'd appreciate any data/info you both
may have.
I don't know how much was the
On 12/01/2010 01:51 AM, Pierre C wrote:
Now I tried removing the constraints from the history table (including
the PK) and the inserts were fast. After few 'rounds' of inserts I
added constraints back, and several round after that were fast again.
But then all the same. Insert of some 11k
On 12/01/2010 02:47 AM, Joshua D. Drake wrote:
On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote:
The database for monitoring certain drone statuses is quite simple:
This is the slow part:
INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
drone_temperature,
Just once.
OK, another potential problem eliminated, it gets strange...
If I have 5000 lines in CSV file (that I load into 'temporary' table
using COPY) i can be sure that drone_id there is PK. That is because CSV
file contains measurements from all the drones, one measurement per
drone. I
So, I did. I run the whole script in psql, and here is the result for
the INSERT:
realm_51=# explain analyze INSERT INTO drones_history (2771, drone_id,
drone_log_notice, drone_temperature, drone_pressure) SELECT * FROM
tmp_drones_history;
Mario Splivalo wrote:
Yes, as Mladen Gogala had advised. No noticable change in performance -
it's still slow :)
Declaring constraints as deferrable doesn't do anything as such, you
have to actually set the constraints deferred to have an effect. You
have to do it within a
On 12/01/2010 05:34 PM, Mladen Gogala wrote:
Mario Splivalo wrote:
Yes, as Mladen Gogala had advised. No noticable change in performance -
it's still slow :)
Declaring constraints as deferrable doesn't do anything as such, you
have to actually set the constraints deferred to have an
Mario Splivalo wrote:
I'll try what Pierre suggested, on whole new filesystem. This one did
get quite filled with thousands of files that I deleted while the
database was working.
Mario
Yes, that is a good idea. That's the reason why we need a
defragmentation tool on Linux.
On Wed, Dec 01, 2010 at 12:15:19PM -0500, Mladen Gogala wrote:
Mario Splivalo wrote:
I'll try what Pierre suggested, on whole new filesystem. This one did get
quite filled with thousands of files that I deleted while the database was
working.
Mario
Yes, that is a good idea.
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
There is a operating system which comes with a very decent extent
based file system and a defragmentation tool, included in the OS.
The file system is called NTFS
Been there, done that. Not only was performance quite poor compared
to Linux,
Kenneth Marshall wrote:
Redhat6 comes with ext4 which is an extent based filesystem with
decent performance.
Ken
But e4defrag is still not available. And, of course, Red Hat 6 is still
not available, either. Maybe Red Hat 7 will do the trick? I assume it
will work beautifully with
Kevin Grittner wrote:
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
Been there, done that. Not only was performance quite poor compared
to Linux, but reliability and staff time to manage things suffered
in comparison to Linux.
I must say that I am quite impressed with Windows 7
I'm just back from vacation, so I apologize in advance if I missed
anything of importance. Here is something to consider:
Instead of using the statement you used to create the table, try the
following:
CREATE TABLE drones_history (
drone_id integer not null,
sample_id integer
On 11/29/2010 05:47 PM, Pierre C wrote:
realm_51=# vacuum analyze verbose drones;
INFO: vacuuming public.drones
INFO: scanned index drones_pk to remove 242235 row versions
DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec.
INFO: drones: removed 242235 row versions in 1952 pages
DETAIL: CPU
On 11/29/2010 05:53 PM, Pierre C wrote:
Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on
On 30/11/10 05:53, Pierre C wrote:
Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on
On 11/30/2010 05:26 PM, Mladen Gogala wrote:
At the beginning of the load, you should defer all of the deferrable
constraints, setting constraints deferred and issuing the copy statement
within a transaction block, like this:
scott=# begin; BEGIN
Time: 0.203 ms
scott=# set constraints all
Now I tried removing the constraints from the history table (including
the PK) and the inserts were fast. After few 'rounds' of inserts I added
constraints back, and several round after that were fast again. But then
all the same. Insert of some 11k rows took 4 seconds (with all
On Sun, 2010-11-28 at 12:46 +0100, Mario Splivalo wrote:
The database for monitoring certain drone statuses is quite simple:
This is the slow part:
INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
drone_temperature, drone_pressure)
SELECT * FROM tmpUpdate;
For 100
On 11/28/2010 10:50 PM, Pierre C wrote:
I pasted DDL at the begining of my post.
Ah, sorry, didn't see it ;)
The only indexes tables have are the ones created because of PK
constraints. Table drones has around 100k rows. Table drones_history
has around 30M rows. I'm not sure what
On 11/29/2010 08:11 AM, Mark Kirkwood wrote:
On 29/11/10 00:46, Mario Splivalo wrote:
This is the slow part:
INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
drone_temperature, drone_pressure)
SELECT * FROM tmpUpdate;
For 100 rows this takes around 2 seconds. For 1000 rows
realm_51=# vacuum analyze verbose drones;
INFO: vacuuming public.drones
INFO: scanned index drones_pk to remove 242235 row versions
DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec.
INFO: drones: removed 242235 row versions in 1952 pages
DETAIL: CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO: index
Yes, since (sample_id, drone_id) is primary key, postgres created
composite index on those columns. Are you suggesting I add two more
indexes, one for drone_id and one for sample_id?
(sample_id,drone_id) covers sample_id but if you make searches on drone_id
alone it is likely to be very
When I remove foreign constraints (drones_history_fk__samples and
drones_history_fk__drones) (I leave the primary key on drones_history)
than that INSERT, even for 50k rows, takes no more than a second.
So, my question is - is there anything I can do to make INSERTS with PK
faster? Or,
On 11/28/2010 07:56 PM, Pierre C wrote:
When I remove foreign constraints (drones_history_fk__samples and
drones_history_fk__drones) (I leave the primary key on drones_history)
than that INSERT, even for 50k rows, takes no more than a second.
So, my question is - is there anything I can do to
I pasted DDL at the begining of my post.
Ah, sorry, didn't see it ;)
The only indexes tables have are the ones created because of PK
constraints. Table drones has around 100k rows. Table drones_history has
around 30M rows. I'm not sure what additional info you'd want but I'll
be more
On 29/11/10 00:46, Mario Splivalo wrote:
This is the slow part:
INSERT INTO drones_history (sample_id, drone_id, drone_log_notice,
drone_temperature, drone_pressure)
SELECT * FROM tmpUpdate;
For 100 rows this takes around 2 seconds. For 1000 rows this takes
around 40 seconds. For 5000 rows
29 matches
Mail list logo
