Welcome Guest, Not a member yet? Register   Sign In
Very slow insert and update into ci_session

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', '', 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1;', 1312311462)

this is structure of table

`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`)

Have any idea how to optimize this?

The table structure is fine. The problem is elsewhere on your server, could be anything, disk problems, server load, mysql misconfigured...

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.

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.

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.

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...

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.

Theme © iAndrew 2016 - Forum software by © MyBB