Database Access Issue

[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?

[eluser]ciuser99[/eluser]
Anyone?

[eluser]ciuser99[/eluser]
Anyone?

[eluser]TheFuzzy0ne[/eluser]
I would look at the script that does the importing for you. Sounds like a seriously CPU intensive loop might be running in there. How is the data inserted into the database?

It might also be worth having a good look at "top" or "uptime", whilst the box is importing. You might find that the system hangs really badly. If this is the case, then you need to find a new way to import your MySQL data.

[eluser]ciuser99[/eluser]
So here is the logic behind the code. There are two processes that can run at the same time. One is the update process where the tables are truncated and new data is bulk inserted. The other is a page view, which queries whichever set of tables that are not being currently updated by the bulk insert process.

Bulk Insert
-----------
Switch database flag in database to table set 2 (live site will pull data from table set 2 (b) while table set 1 (a) is being bulk updated)

Truncate table a1
Truncate table a2
Truncate table a3
Truncate table a4

Loop
Grab 100 records from JSON API feed from external site (the feed allows a max of 100 records to be downloaded at a time, so this loops until all records are downloaded)
Attach to SQL INSERT query string
Loop (through array of items)
Attach to a different SQL INSERT query string
End Loop
End Loop
Execute 1st SQL query string -> inserts data into table a1
Execute 2nd SQL query string -> inserts data into table a2

Loop
Grab 100 records from JSON API feed from external site (the feed allows a max of 100 records to be downloaded at a time, so this loops until all records are downloaded)
Attach to SQL INSERT query string
Loop (through array of items)
Attach to a different SQL INSERT query string
End Loop
End Loop
Execute 1st SQL query string -> inserts data into table a3
Execute 2nd SQL query string -> inserts data into table a4

Switch database flag in database to table set 1 (live site will pull data from table set 1 (a) while table set 2 (b) is being bulk updated)

Truncate table b1
Truncate table b2
Truncate table b3
Truncate table b4

Loop
Grab 100 records from JSON API feed from external site (the feed allows a max of 100 records to be downloaded at a time, so this loops until all records are downloaded)
Attach to SQL INSERT query string
Loop (through array of items)
Attach to a different SQL INSERT query string
End Loop
End Loop
Execute 1st SQL query string -> inserts data into table b1
Execute 2nd SQL query string -> inserts data into table b2

Loop
Grab 100 records from JSON API feed from external site (the feed allows a max of 100 records to be downloaded at a time, so this loops until all records are downloaded)
Attach to SQL INSERT query string
Loop (through array of items)
Attach to a different SQL INSERT query string
End Loop
End Loop
Execute 1st SQL query string -> inserts data into table b3
Execute 2nd SQL query string -> inserts data into table b4


Page View
----------
Check flag to see which database to use
Make multiple queries to table set a or table set b to display data on page


I spoke with the database administrator and the SQL Server did not show any heavy strain on CPU or memory resources during this bulk insert process. Like I said before, I tried running a simple script that grabs all of table a1 and displays while the bulk insert is running and it runs fine without stalling. But Any page running on Code Igniter stalls until the entire bulk insert process is completed. I even checked the database directly to make sure that the database flag/selected database was the correct one as the bulk insert was occurring. This was working fine. So I am trying to figure out what could be the source of this issue. The stall happens somewhere between the controller constructor is being processed and the index() function of the controller is being processed.

I want the pages to display without stalling as the database is being bulk inserted into.

[eluser]TheFuzzy0ne[/eluser]
If you're going to truncate the tables, I would recommend putting your data into a single file, and then loading the file straight into the database. That way, everything is done in one swift move; there is no need for multiple queries and such like. This process will be faster, and consume a lot less resources. The only potential problem is that the SQL file you create may have syntax errors, but I don't think it's any more unsafe than the way you are doing it at present.

Hope this helps.

[eluser]cwt137[/eluser]
How about use memcached to cache your queries? Then when your tables are being updated, the data will come from cache instead of the database.

[eluser]ciuser99[/eluser]
Thanks for the replies. I tried aggregating all of the queries into one file and then executing that but the maximum query size was exceeded and the database admin does not want to increase this setting in the MySQL Server. Also, I don't think that memcached is an option on our server and our database admin doesn't want us to use memcache. I will keep looking for an answer.

[eluser]TheFuzzy0ne[/eluser]
Then break your insert queries up into smaller chunks, just like you were doing originally, only the file will only insert 100 values in each query.

I think it's strange how the admin doesn't want to change the setting, but he's happy for you to be hammering the database with thousands of insert queries.

[eluser]SitesByJoe[/eluser]
Have you considered cron tabs? I have alot of big database downloads I have to regularly do and I ended up opting to run the scripts automatically - broken into chunks like Fuzzy says.

Setting up a "drip system" of data that drips into the database.

It may not be applicable to exactly what you're doing, but it's a thought.