[eluser]ciuser99[/eluser]
I'm running CodeIgniter v1.7 on a Linux box. The issue I'm having is that we have a scheduled job that runs every hour and imports about 20,000 records into 8 tables after truncating these tables. When this update is occurring the site doesn't load until the bulk import is done. The bulk insert takes anywhere from 10 seconds to about a minute based on the speed of the data download. There are 4 main tables that the site pulls data from. I created a duplicate table for each of these so I could have the site pull data from these duplicate tables while the main tables are being updated. The data is synchronized in the two sets of tables.
I made sure that the data models all contained logic that first determines which database is being updated and use the other one during that time. I've tested it out pretty thoroughly. There is a sizeable time gap (the time duration of the bulk insert) between when the execution of the code is in the constructor of the controller and the index() function as the bulk insert is occurring. I've done a query profile of each query and they run fine in terms of execution time. The time gap is occurring sometime before the models are being loaded and the queries are being executed.
I've been trying to figure out how Code Igniter connects to the database and communicates with it before the models are loaded. I believe that this is where the issue may lie. As the bulk insert is occurring, Code Igniter is having trouble connecting to the database and stalls until the bulk insert is complete. This is my assumption, which would very well be wrong.
The interesting thing is that when I run the bulk insert and execute a standalone PHP script that queries one of the databases and prints out all of the records, it works fine. So the issue doesn't seem to be caused by the database getting too many requests from the bulk insert and not being able to run any other queries during this time.
I hope I have explained this issue clearly. Any ideas on what the source of this problem might be?
