Database Access Issue

[eluser]ciuser99[/eluser]
I basically agree with the assessment that I need to break up the download into smaller pieces. Right now it downloads 100 records at a time and builds up the bulk insert string by concatenating to the string. I build a bulk insert string for each table and execute each bulk insert separately - 4 in total.

Right now the bulk insert is a manual process where the user clicks a button to trigger it. I was planning to make it a cron job running every hour on the hour, but the site stalling issue has given me second thoughts. And the reliability of the bulk insert itself has given me pause.

I tried executing an insert command for each record but that was costly in terms of processing time. This is how I originally coded it and it stalled the site until the bulk inserts were completed. This is a challenge but I find it to be an interesting one that I would like to solve. There is something that Code Igniter is doing, possibly in the initialization code, that could be preventing me from accessing the database while the bulk insert is processing. Perhaps in the way it is trying to access the database.

I've studied other ways in which programmers bulk update their databases while users are hitting the site. I wonder what are the advantages/disadvantages of these strategies.

Thanks for your input. I'm gonna keep searching...

[eluser]TheFuzzy0ne[/eluser]
I don't think it's CodeIgniter. I believe that either the MySQL server is being seriously overworked, or that your script might be locking the tables, although I'm sure you'd know if it was. It might be interesting for you to run some tests and see if you can find these bottle necks and decide on the optimal way to do this.

I'm still not sure if you are concatenating your MySQL statement into bite size pieces, or just firing 100 insert statements at a time. My suggestion was to basically insert about 100 at a time, but with a single insert statement, not individually.

[eluser]ciuser99[/eluser]
I figured out the issue and it was something that I wasn't expecting. I added timestamps throughout the code to determine exactly where the bottleneck was. It turns out that sessions in the controllers plus the bulk insert slowed things down. I saw that the time difference before and after the session_start() function would be like 10 seconds or so. I use sessions only for captchas so I removed all sessions and replaced them with cookies. After I did this the site loaded fine when the bulk insert is processing. I don't know how the sessions and bulk insert relate but this solved the problem. If I ever have to use sessions then it might become a problem again. I'll have to look for another solution.

[eluser]cwt137[/eluser]
Is the web server and mysql server on the same box?

[eluser]TheFuzzy0ne[/eluser]
Are your sessions set to use the database?

[eluser]ciuser99[/eluser]
The web site and mysql database server are located separately. The session is used strictly for captcha generation and recognition. But it seems that the database processing and session affect each other in some way.