Slow db session table, reason found!

I am using CDbHttpSession as the post http://programmersnotes.info/2009/03/05/speeding-up-yii-or-why-should-you-use-db-sessions/ suggest




		'session' => array(

			'class' => 'CDbHttpSession',

			'connectionID' => 'db',

    ),



But I found the slowest part is the session table part, the call to Yii::app()->user->isGuest takes a lot of time working on session table:


[0.77831] system.db.CDbCommand.execute(DELETE FROM YiiSession WHERE expire<1263576215)

[0.00112] system.db.CDbCommand.query( SELECT data FROM YiiSession WHERE expire>1263576216 AND id=:id )

Analyze shows it is round [color="#8B0000"]407ms[/color]




EXPLAIN ANALYZE DELETE FROM YiiSession WHERE expire<1263576215


Seq Scan on yiisession  (cost=0.00..88.69 rows=1 width=6) (actual time=0.383..0.383 rows=0 loops=1)

  Filter: (expire < 1263576215)

Total runtime: 0.407 ms


SELECT count(*) from  YiiSession 

1435



[size="5"]Fix Part 1: add Index[/size]


CREATE INDEX yiisession_expire_idx ON "yiisession" (expire);

Analyze shows it is round [color="#8B0000"]49ms[/color]




EXPLAIN ANALYZE DELETE FROM YiiSession WHERE expire<1263576215


Index Scan using yiisession_expire_idx on yiisession  (cost=0.00..8.27 rows=1 width=6) (actual time=0.026..0.026 rows=0 loops=1)

  Index Cond: (expire < 1263576215)

Total runtime: 0.049 ms



[size="5"]Fix Part 2: why clean session for every visit? [/size]

CDbHttpSession.php


	public function openSession($savePath,$sessionName)

	{

		$db=$this->getDbConnection();

		$db->setActive(true);


		if($this->autoCreateSessionTable)

		{

			$sql="DELETE FROM {$this->sessionTableName} WHERE expire<".time();

			try

			{

				$db->createCommand($sql)->execute();

			}

			catch(Exception $e)

			{

				$this->createSessionTable($db,$this->sessionTableName);

			}

		}

		return true;

	}



Why autoCreateSessionTable will clean session table? Any why always do the cleanup? I think [color="#8B0000"]it is a bug[/color].

Before qiang fixed it, I disable autoCreateSessionTable




		'session' => array(

			'class' => 'CDbHttpSession',

			'connectionID' => 'db',

			'autoCreateSessionTable' => false,

    ),



Now page load is [color="#8B0000"]less than 100ms[/color]

Good job! Yes, it’s crucial to have proper index. Regarding autoCreateSessionTable setting, in production server this should be set as false, as you described here. When this is set true, the delete statement is used to detect the existence of the table, which should be avoided if you are sure you already have the table.

This along with many other settings like AR schema caching should be added to http://www.yiiframework.com/doc/guide/topics.performance

Continue:

[size="5"]Fix Part 3: Using CDbHttpSession is a bad idea (CCacheHttpSession instead) [/size]

The page load is now about 100ms but the session SQL

SELECT data FROM YiiSession WHERE expire>1263824920 AND id=‘82ec0310dcd4b0878c0b31cf82af74ea’

takes about 50ms. That is not a good thing. (the database runs in a separate dedicated server)

So I finally understand it is not a good idea to put session in database even with index. (make the session table in memory may be a solution). It is better to put the session in memory.

Now I changed to CCacheHttpSession




		'session' => array(

			'class' => 'CCacheHttpSession',

    ),

    'cache'=>array(

			'class'=>'system.caching.CApcCache',

    ),




The session operation only takes about [color="#8B0000"]4ms[/color]

great post, thanks a lot for sharing your tests!

It’s of course not very surprising that keeping session data in memory is very fast. But it has one huge drawback: You loose all session data with every apache restart/reload, at least with APC. My idea for a compromise would have been to keep sessions in DB and increase the query_cache setting in case you use MySQL. But that will not help much as the expire time will be different for each query.

Would like to revive this old thread as I wonder if the session handling has been improved in recent versions of Yii. I would like to use this for a site I’m currently building – the site will be load-balanced so I would like to use db-based PHP sessions (I do realize there are other options), and I am trying to decide if I should use Yii to handle the session management or another PHP-based method (which I’ve done before and seems to work pretty well); any opinions or recent experience would be appreciated.

One observation of the OP - while you seem to have been measuring the total page execution time, isn’t the MySql explain time results in (your case, fractions of) milliseconds (meaning the db times weren’t all that bad, particularly after indexing)? Also - when you say “Before qiang fixed it, I disable autoCreateSessionTable”, does that mean it’s OK to enable autoCreateSessionTable now?

I would also be interested if there is a way with Yii to combine db sessions with Memcached.

thanks.

Hey

Here a couple answers from my experience

no, because it generates 1 more query slowing it down for no reason

use only in production server as said

I also think the same, but if that are faster ways why not ?

Also if your application uses a lot of ajax, the php’s normal session system slow down your application a lot , so the faster way would be using another system, like cache or db

As for cache, as Mike pointed it out, it will clean up the logged in table everytime your server restarts, which is a bad idea

What I recommend is to use a cache system that does not clean up on restarts, which is 10 times faster than DB and will work the same way.

I did an extension to use Redis as a cache system, which is a system that will do what I just said, keep the data in memory for fast access and send it to the hard drive once in a while so you dont lose.

Best regards

One quick query… what is the tradeoff of indexing / non indexing the yiisession table on ‘expire’ column ? As update / insert operations on yiisession table are more than delete operations.

I’d like to add: I’ve made some good experiences with holding the entire session-table in memory via MySQL’s very own MEMORY storage engine. The drawback is that MEMORY does not support the TEXT data-type. You’d have to take a VARCHAR(4096) or the like. You have to properly guess how large your sessions will turn, so go on easy with this.


CREATE TABLE `session` (

  `id` char(32) NOT NULL,

  `expire` int(11) unsigned DEFAULT NULL,

  `data` varchar(4096) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `expire_idx` (`expire`) USING BTREE

) ENGINE=MEMORY DEFAULT CHARSET=utf8;

A quick follow-up: I recently learned that the MySQL MEMORY engine is treating VARCHARs like CHARs. So in the example above, the storage engine will always reserve 4kb for the session payload. Depending on your available memory, your number of unique visitors etc. this might become a problem.

Looks like that limitation fell in Percona Server.

I was wondering: why is the MyISAM database engine used for the YiiSession table?

Shouldn’t that be the InnoDB engine?

After all, there are a lot of inserts and updates on the YiiSession table, and since MyISAM uses table locking, this sometimes results in lock waits (at least at our website)

Any thoughts on this?

InnoDB isn’t MySQL’s default that very long. Perhaps that’s why.

Check the code in CDbHttpSession::createSessionTable(): There’s no engine specified here. So MyIsam is probably the default engine in your MySQL config.

[left]

[/left]

You are right, it must have been my default engine.

So, switch it to InnoDB then?

You can try to use the memory storage engine :rolleyes:

I am in a difficult situation .

Forum Post

How can i manage sessions between 2 or more web servers are using to manage Load balancing ?

The points that i found are

Use database session CDbHttpSession

Use cache session CCacheHttpSession

Use Security manager CSecurityManager