+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Very slow insert and update into ci_session (/showthread.php?tid=44086)
Very slow insert and update into ci_session - El Forum - 08-02-2011
[eluser]devastator[/eluser]
Hello, i have small problem on a live site with 5 000 unique visitors i have ci_session table with no more then 500 records and some times i have VERY VERY slow insert and/or update queries in this table like this query took 2 seconds and mysql is NOT overloaded.
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('79a7bf6b52545c9b785d9bca4072843f', '85.83.41.147', 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1;', 1312311462)
this is structure of table
CREATE TABLE IF NOT EXISTS `ci_sessions` (
`session_id` varchar(40) NOT NULL DEFAULT '0',
`ip_address` varchar(16) NOT NULL DEFAULT '0',
`user_agent` varchar(50) NOT NULL,
`last_activity` int(10) unsigned NOT NULL DEFAULT '0',
`user_data` text NOT NULL,
PRIMARY KEY (`session_id`),
UNIQUE KEY `Session_ID` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Have any idea how to optimize this?
Very slow insert and update into ci_session - El Forum - 08-02-2011
[eluser]bproctor[/eluser]
The table structure is fine. The problem is elsewhere on your server, could be anything, disk problems, server load, mysql misconfigured...
Very slow insert and update into ci_session - El Forum - 08-02-2011
[eluser]devastator[/eluser]
This is second server that i use. Before i was on shared hosting now is on VPS i dunno what to configure to be better.
Very slow insert and update into ci_session - El Forum - 08-02-2011
[eluser]Aken[/eluser]
Try to use phpMyAdmin or a similar tool to diagnose, and possibly repair your tables. Occasionally there can be a lot of leftover data that can slow down, even corrupt tables.
Very slow insert and update into ci_session - El Forum - 08-02-2011
[eluser]pickupman[/eluser]
Surprising enough, a VPS maybe slower than shared hosting. You had not mentioned if the inserts were fine on shared hosting, but now slow on VPS. A VPS has a dedicated and fixed amount of resources. A shared account is generally has access to a lot more server resources with the trade off that it is shared with other sites. If you get on a cluster that has lower traffic sites, you will have alot more resources running on a shared.
Aside from that, is the insert still taking that long running the query via phpMyAdmin or login via shell and run the query from the command line. At least that will eliminate CI being a factor in the situation.
Very slow insert and update into ci_session - El Forum - 08-06-2011
[eluser]Jaketoolson[/eluser]
I see your table is using the InnoDB engine. Have you optimized your table for InnoDB correctly?
For sessions storage, I use the MEMORY (heap) storage engine. It's MUCH faster and perfect for temporary storage.
But again, regardless of the table engine you use, you need to ensure you've optimized your table correctly. I don't mean by running optimize either. I mean by ensuring the settings within your httpd.conf file have been tested and set and you're also not using excessive space through incorrect indexes, column type and lengths...
Very slow insert and update into ci_session - El Forum - 08-06-2011
[eluser]bproctor[/eluser]
Like Jaketoolson said, using the MEMORY storage engine is probably a better choice for storing sessions. However, there is something else wrong with the system if it takes 2 seconds to do an insert.