DB Crashing

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’ve been using stored procedures within MySQL and the MySQLi class in PHP for a while now and not had any problems, but am now having some major issues.

I’m reading a number of fairly big XML files (some being 400 MB big) and trying to add each record into my DB.
This is resulting in my stored procedure being called at least once a second… probably more.
To start with I’ve now moved over to using PDO and has speeded things up even more by the looks of it.

I’m just calling the procedure by using the “query” function in the PDO class.

The procedure (I though) was fairly basic, goes like this…

Checks some of the variables passed in have a value, checks to see if a record already exists.
If it does exist, it updates the record, if not, it creates a new record.
Then selects the ID and returns it.

I’m getting about 12,000 record in and then I run out of memory.
Is there any thing I can do either within MySQL or even PHP to prevent this from happening?

Thanks for your time.

2
[B]Scrutters wrote on Mon, 10 May 2010 20:00[/B]
I'm getting about 12,000 record in and then I run out of memory.
Where are you running out of memory? PHP? MySQL? On the entire machine?
3

I think it’s MySQL, but it could be the entire machine.
Eventually, the entire server goes down.
Luckily it’s a VPS and doesn’t stay down for long.

I’m going to run one of the procedures in a query analyzer and see if it gives any suggestions.

4

I don’t think it’s a query thing, my guess is more along the lines that you need to free the result set from the queries issued against mysql or something the like in your PHP code.

But have “top” running while you execute the query and see what starts to consume memory during the processing of your large xml file.