Long database process chokes

[martinj]

I am developing a PHP/Postgres application. I have created a routine that could take a long time to execute due to the amount of database IO. However, after a number of hours the process chokes and the HTML page never gets displayed. I have tweaked some of the parameters in php.ini, but haven't figured this one out yet. Any ideas?

[[deleted]]

What kind of process is this that takes hours to complete?

[martinj]

A PostgreSQL database process.

[[deleted]]

I was thinking more along the lines of: what does the query do that it takes several hours to complete?

[martinj]

I have written a sales database to handle customer accounts, customer information , addresses, inventory, and sales transactions. The application is still in beta. I wrote a script to test my database API to the maximum allowing me to write, update, and delete hundreds of thousands of records to the database through my API. The scripts work for a few thousand records, but not more. I don't know why.

Recent test this morning confirm that it may be a postgres problem and not a PHP/Apache problem because a Linux bash shell script attempting more than 10,000 records through my API doesn't come back either.

Simply put I wrote a an application strainer to see if I could break my application and sure enough I broke it. Now I want to figure out what to do to fix it so I can trust my application with serious usage.

Thanks, Jeff

[[deleted]]

SO you really don't have a problem at all, you just wrote a script that does 'more than your dbserver can handle'?

[martinj]

If I can't figure out how to dump 100,000 records into my database through configuring the db or apache properly I do have a problem.

[[deleted]]

In what form do you have the records that you want to put into the database? textfile? other database?

[martinj]

My purpose is to test my application particularly PHP, my database API (which is PostgreSQL and Postgres stored procedures), and the Postgres database backend. so the process employed is as follows:

From a PHP page
1. Get input parameters such as number of records to write and data to make my dummy records unique.
2. loop 100,000 times calling my WriteNewAccount() stored procedure to create 100,000 new account.

Problem...works with 10,000 but not 100,000.

From a bash shell script
1. Invoke psql and call a stored procedure designed to write X dummy account records.

Problem...works with 10,000 but not 100,000.

Thanks,

Jeff

[Anon]

Have you got logging turned on in postgresql and have you examined the logs to see what they tell you?

Are you doing all of these under one connection or transaction? Does closing and reopening the connection every few thousand records help?

Do you know how to configure the linux kernel for more shared memory and file handles? Are there any kernel messages in /var/log/messages that could give a clue here?