Re: [SLUG] PostgreSQL slowing down on INSERT

[Rick Welykochy]

Rick Welykochy wrote this and replies to hisself:


Howard Lowndes wrote:


I have a PHP script that inserts around 100K of records into a table on
each time that it runs.

It starts off at a good pace but gets progressively slower until it falls
over complaining that it cannot allocate sufficient memory.


When I need to do this in MySQL, I used a LOADDATA INFILE sql command
that loads the data from a CSV or TSV file. Completes very quickly,
with only one round trip to the server.

Is there a similar command in PostgresSQL?


Found it. It is called COPY in PostgreSQL.



They recommend to turn AUTOCOMMIT off.

Then take Sonia's recommendation of enforcing a unique index
so the dupes are chucked out.

Fire off one single COPY command from PHP and see how that works.


cheers
rickw


--

Rick Welykochy || Praxis Services || Internet Driving Instructor

Tis the dream of each programmer before his life is done,
To write three lines of APL and make the damn thing run.
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html

Re: [SLUG] PostgreSQL slowing down on INSERT

[Robert Collins]

On Wed, 2008-04-30 at 02:32 +1000, Howard Lowndes wrote:
> I have a PHP script that inserts around 100K of records into a table on
> each time that it runs.
> 
> It starts off at a good pace but gets progressively slower until it falls
> over complaining that it cannot allocate sufficient memory.
> 
> I have increased the memory allocation in the script with:
> ini_set('max_execution_time', '3600');
> ini_set('memory_limit', '128M');
>  but this only seems to delay the crash.
> 
> I have also tried closing and reoprning the database` every 10K inserts,
> but that doesn't seem to speed things up either.
> 
> Any other suggestions?

Are you doing this as one transaction or many?

-Rob
-- 
GPG key available at: .


signature.asc
Description: This is a digitally signed message part
-- 
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html

Re: [SLUG] PostgreSQL slowing down on INSERT

[justin randell]

On Wed, Apr 30, 2008 at 2:32 AM, Howard Lowndes <[EMAIL PROTECTED]> wrote:
> I have a PHP script that inserts around 100K of records into a table on
>  each time that it runs.

maybe pastebin the relevant bits somewhere?

>  It starts off at a good pace but gets progressively slower until it falls
>  over complaining that it cannot allocate sufficient memory.
>
>  I have increased the memory allocation in the script with:
>  ini_set('max_execution_time', '3600');
>  ini_set('memory_limit', '128M');
>   but this only seems to delay the crash.
>
>  I have also tried closing and reoprning the database` every 10K inserts,
>  but that doesn't seem to speed things up either.
>
>  Any other suggestions?

php4 or php5?

php5 has some nasty memory leaks with objects that reference each other:

http://bugs.php.net/bug.php?id=33595

if this applies to you, there are workarounds in the issue.
-- 
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html

Re: [SLUG] PostgreSQL slowing down on INSERT

[Rick Welykochy]

Howard Lowndes wrote:


I have a PHP script that inserts around 100K of records into a table on
each time that it runs.

It starts off at a good pace but gets progressively slower until it falls
over complaining that it cannot allocate sufficient memory.


When I need to do this in MySQL, I used a LOADDATA INFILE sql command
that loads the data from a CSV or TSV file. Completes very quickly,
with only one round trip to the server.

Is there a similar command in PostgresSQL?


cheers
rickw


--

Rick Welykochy || Praxis Services || Internet Driving Instructor

Tis the dream of each programmer before his life is done,
To write three lines of APL and make the damn thing run.
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html

Re: [SLUG] PostgreSQL slowing down on INSERT

[Michael Chesterton]

On 30/04/2008, at 2:32 AM, Howard Lowndes wrote:

I have a PHP script that inserts around 100K of records into a  
table on

each time that it runs.

It starts off at a good pace but gets progressively slower until it  
falls

over complaining that it cannot allocate sufficient memory.


I have also tried closing and reoprning the database` every 10K  
inserts,

but that doesn't seem to speed things up either.

Any other suggestions?


How are you inserting the data? What library?
It might be that you need to free or flush the handle.


Michael Chesterton
http://chesterton.id.au/blog/
http://barrang.com.au/

--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html

[SLUG] PostgreSQL slowing down on INSERT

[Howard Lowndes]

I have a PHP script that inserts around 100K of records into a table on
each time that it runs.

It starts off at a good pace but gets progressively slower until it falls
over complaining that it cannot allocate sufficient memory.

I have increased the memory allocation in the script with:
ini_set('max_execution_time', '3600');
ini_set('memory_limit', '128M');
 but this only seems to delay the crash.

I have also tried closing and reoprning the database` every 10K inserts,
but that doesn't seem to speed things up either.

Any other suggestions?


-- 
Howard
LANNet Computing Associates 
When you want a computer system that works, just choose Linux;
When you want a computer system that works, just, choose Microsoft.

-- 
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html