Yii Framework Forum: Slow db session table, reason found! - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Slow db session table, reason found! finally found out Rate Topic: ***** 4 Votes

#1 User is offline   rnysmile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 07-May 09

Posted 15 January 2010 - 01:10 PM

*
POPULAR

I am using CDbHttpSession as the post http://programmersno...se-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 407ms

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


Fix Part 1: add Index

CREATE INDEX yiisession_expire_idx ON "yiisession" (expire);



Analyze shows it is round 49ms

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



Fix Part 2: why clean session for every visit?

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 it is a bug.
Before qiang fixed it, I disable autoCreateSessionTable

		'session' => array(
			'class' => 'CDbHttpSession',
			'connectionID' => 'db',
			'autoCreateSessionTable' => false,
    ),


Now page load is less than 100ms
5

#2 User is offline   qiang 

  • Yii Project Lead
  • Yii
  • Group: Yii Dev Team
  • Posts: 5,907
  • Joined: 04-October 08
  • Location:DC, USA

Posted 15 January 2010 - 01:41 PM

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

#3 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,003
  • Joined: 17-January 09
  • Location:Russia

Posted 15 January 2010 - 03:27 PM

This along with many other settings like AR schema caching should be added to http://www.yiiframew...ics.performance
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#4 User is offline   rnysmile 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 07-May 09

Posted 18 January 2010 - 01:01 PM

Continue:


Fix Part 3: Using CDbHttpSession is a bad idea (CCacheHttpSession instead)

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 4ms
1

#5 User is offline   The Peach 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 80
  • Joined: 08-October 09
  • Location:London, UK

Posted 10 February 2010 - 07:09 AM

great post, thanks a lot for sharing your tests!
0

#6 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,017
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 11 February 2010 - 02:37 AM

View Postrnysmile, on 18 January 2010 - 01:01 PM, said:

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.


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

#7 User is offline   ldg 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 13-October 09

Posted 15 February 2011 - 12:25 AM

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

#8 User is offline   Gustavo 

  • Master Member
  • Yii
  • Group: Moderators
  • Posts: 916
  • Joined: 27-July 10
  • Location:Curitiba - Brasil

Posted 15 February 2011 - 02:19 AM

Hey
Here a couple answers from my experience

Quote

does that mean it's OK to enable autoCreateSessionTable now?


no, because it generates 1 more query slowing it down for no reason
use only in production server as said

Quote

milliseconds (meaning the db times weren't all that bad, particularly after indexing)?


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

Quote

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


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
--
Extensions:
translate modue - module to handle translations
multiActiveRecord - db selection in models
redisCache - redis cache component
mpCpanel - interact with cpanel api
mUploadify - use uploadify uploader in your application

Gustavo Salomé Silva
2

#9 User is offline   Penta 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 14-June 11

Posted 15 July 2011 - 07:18 AM

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.


View PostGustavo, on 15 February 2011 - 02:19 AM, said:

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

0

#10 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,269
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 15 July 2011 - 07:46 AM

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;

programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#11 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,269
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 05 September 2011 - 06:16 AM

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.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#12 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,269
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 13 September 2011 - 07:47 AM

Looks like that limitation fell in Percona Server.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#13 User is offline   BertB 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 09-November 10
  • Location:Netherlands

Posted 20 September 2011 - 05:18 AM

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

#14 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,269
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 20 September 2011 - 07:36 AM

InnoDB isn't MySQL's default that very long. Perhaps that's why.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#15 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,017
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 20 September 2011 - 07:37 AM

View PostBert Brunekreeft, on 20 September 2011 - 05:18 AM, said:

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


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



0

#16 User is offline   BertB 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 09-November 10
  • Location:Netherlands

Posted 20 September 2011 - 07:45 AM

View PostMike, on 20 September 2011 - 07:37 AM, said:

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





You are right, it must have been my default engine.
So, switch it to InnoDB then?
0

#17 User is offline   Da:Sourcerer 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,269
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 20 September 2011 - 09:57 AM

You can try to use the memory storage engine :rolleyes:
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#18 User is offline   Rajith R 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 901
  • Joined: 20-April 11
  • Location:India

Posted 29 March 2014 - 04:40 AM

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
Rajith Ramachandran,
Wiwo inc.
| Mobile: 919995504508
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users