Welcome Guest, Not a member yet? Register   Sign In
Why is the "ci_sessions" table type MyISAM?
#1

[eluser]taewoo[/eluser]
If you are using DB session library, you'll notice that the "ci_sessions" table is of type MyISAM. I'm no DB guru, but isn't InnoDB a better choice b/c of row level locking? Wouldn't concurrent multiple users be slow w/the MyISAM table?
#2

[eluser]Aniket[/eluser]
Hello,

i think this resource available at the following link may help you.

http://www.mysqlperformanceblog.com/2008...vs-innodb/
#3

[eluser]taewoo[/eluser]
NOt really a power DB user... not certain what the article is saying.
#4

[eluser]Colin Williams[/eluser]
You create the table yourself so you can make it whatever engine you prefer.
#5

[eluser]Référencement Google[/eluser]
InnoDB takes his power while there is relational things in the DB, but is slower than MyIsam engine, you don't need relations for session stuff, don't you?

Storing session data would be even better to do with the Memory engine, but it won't accept the CI text field used for datas, so your best choice is to keep it as MyIsam.
#6

[eluser]waspfactoryuk[/eluser]
My understanding is that the key advantage of InnoDB here is that it has row level locking whereas MyISAM has table level locking. If you are updating a MyISAM table, the table is locked and reads are queued until the write is completed. With InnoDB, a write only locks the affected row so the remaining unaffected rows in the table can still be read.

If the site doesn't have a large number of concurrent users, there probably won't be much difference, but as soon as you have multiple users all trying to read/write the same table, it quickly becomes a bottleneck.

Yes, MyISAM may be quicker and InnoDB may enforce relationships, but these factors are irrelevant here. The issue as I see it is that MyISAM can only be quicker if the table you are trying to read from is not locked therefore InnoDB is a more appropriate choice for concurrent users.

Sorry to drag up an old post but there is not a great deal around on this subject.
#7

[eluser]mehike[/eluser]
another question:

if application is heavy used, then hold sessions in database - it is good choice?
I think DB connections and querys is slow for sessions?
or not?
#8

[eluser]RaZoR LeGaCy[/eluser]
Running APC plus memcache.

I am having a lot of slow queries for ci_sessions, the list is longer but you get the idea. MySQL log returns:

Code:
# Time: 100727 18:51:45

# Query_time: 14  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('e27c96e05f2a22bf87b77ebb5aac2b2d', '67.195.114.215', 'Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://', 1280271091);
# Time: 100727 18:53:10

# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('664d9ed5da1e7086dbce48bf48fc48c8', '118.81.99.85', 'Mozilla/3.0 (compatible; Indy Library)', 1280271178);
# Time: 100727 19:08:53

# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('094498eaed248968bad4fa045190b2ae', '66.249.65.145', 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww', 1280272121);
# Time: 100727 19:14:13

# Query_time: 13  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('229c467afb6bb42b9f0bceaef8155542', '66.249.65.145', 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww', 1280272440);
# Time: 100727 19:15:20

# Query_time: 14  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('d2a7795692eda11379ca56247d4f675f', '67.195.114.215', 'Mozilla/5.0 (compatible; Yahoo! Slurp/3.0; http://', 1280272506);
# Time: 100727 19:47:55

# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use ci;
INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`) VALUES ('7b808ec0c2de270abc78adbe2c74350e', '66.249.65.220', 'Mozilla/5.0 (compatible; Googlebot/2.1; +http://ww', 1280274463);

DB Structure
Code:
CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `session_id` varchar(40) collate utf8_unicode_ci NOT NULL default '0',
  `ip_address` varchar(16) collate utf8_unicode_ci NOT NULL default '0',
  `user_agent` varchar(50) collate utf8_unicode_ci NOT NULL default '',
  `last_activity` int(10) unsigned NOT NULL default '0',
  `session_data` text collate utf8_unicode_ci,
  `user_data` text collate utf8_unicode_ci,
  PRIMARY KEY  (`session_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

config/database.php
Code:
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "cache";

config/config.php
Code:
$config['sess_expiration']      = 7200; // 2hours

$config['sess_encrypt_cookie']  = TRUE;

$config['sess_use_database']    = TRUE;

$config['sess_table_name']      = 'ci_sessions';

$config['sess_match_ip']        = FALSE;
$config['sess_match_useragent'] = FALSE;

$config['sess_time_to_update'] = 300;

Seems like Yahoo/Google bots are the culprits but soon users will be seeing these issues when site traffic increases.

What are some solutions to fix this?
#9

[eluser]waspfactoryuk[/eluser]
So how does it compare if you use InnoDB instead? It seems to be the inserts that are slow.
#10

[eluser]RaZoR LeGaCy[/eluser]
I recently switched from InnoDB because I was getting the same issues.

What would happen if I switch $config['sess_use_database'] = FALSE;? I currently use one query that may get messed up which display data querying this table. Can I still do the below:

In total there are 29 users online :: 5 registered and 24 guests (based on users active over the past 30 minutes)

Registered users: [ Admin ], User1, User2, User3

Legend: [ Adminstrator ], ++Moderator Team, --Editorial Team

Can I switch off inserts for bots?




Theme © iAndrew 2016 - Forum software by © MyBB