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

[eluser]WanWizard[/eluser]
I wonder if that is the real issue.

I have sites with a much higher hit rate, and I notice no delay whatsoever with MyISAM tables. The session table or one of them currently holds over 23700 records, with a gc probability of 10%.

MyISAM doesn't support concurrent write access. If your application uses session variables a lot, the standard CI session class updates the session record every time you modify a session variable. Which means that it is possible that inserts are being queued.
We use a modified sess_write() method, that skips the write. We do an explicit write after the page request has been processed and the output generated, so only one session update per page request. That reduced the number of update queries by over 90%.
#12

[eluser]RaZoR LeGaCy[/eluser]
Table holding only 1500 records.

I have on every page load from ci_sessions. One select statement and 5 updates.

Should I switch my session class, if so then to what session? Can I fix the session class?

I use Freakauth_light with it's own session (http://www.koders.com/php/fid8728438C6B3...px?s=login).
Can I just drop it and use another session like http://codeigniter.com/wiki/Session_Hybrid/


Output from profiler

Code:
TOP OF PROFILER
SELECT * FROM (`ci_sessions`) WHERE `session_id` = '248d49d92b046c60ee69245c0dfc63a9' AND `last_activity` > 1280855860  Possible keys: PRIMARY · Key Used: PRIMARY · Type: const · Rows: 1 · Speed: 0.001 ms
UPDATE `ci_sessions` SET `last_activity` = 1280863060, `user_agent` = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv', `ip_address` = 'XX.XXX.XX.XXX', `session_data` = 'DATA:I REMOVED FOR SECURITY}' WHERE session_id = '248d49d92b046c60ee69245c0dfc63a9'
UPDATE `ci_sessions` SET `last_activity` = 1280863060, `user_agent` = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv', `ip_address` = 'XX.XXX.XX.XXX', `session_data` = 'DATA:I REMOVED FOR SECURITY}' WHERE session_id = '248d49d92b046c60ee69245c0dfc63a9'
UPDATE `ci_sessions` SET `last_activity` = 1280863060, `user_agent` = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv', `ip_address` = 'XX.XXX.XX.XXX', `session_data` = 'DATA:I REMOVED FOR SECURITY' WHERE session_id = '248d49d92b046c60ee69245c0dfc63a9'


MIDDLE OF PROFILER
UPDATE `ci_sessions` SET `last_activity` = 1280863060, `user_agent` = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv', `ip_address` = 'XX.XXX.XX.XXX', `session_data` = 'Removed for security}' WHERE session_id = '248d49d92b046c60ee69245c0dfc63a9'
UPDATE `ci_sessions` SET `last_activity` = 1280863060, `user_agent` = 'Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv', `ip_address` = 'XX.XXX.XX.XXX', `session_data` = 'REMOVED FOR SECURITY}' WHERE session_id = '248d49d92b046c60ee69245c0dfc63a9'
#13

[eluser]WanWizard[/eluser]
I don't think switching session classes would be wise at this point, since you don't know the root cause of your problem.

12 seconds waiting on an insert in a simple table with only 1500 records and a low update rate is not normal. Is this the only thing that is slow? How are your other queries performing? What does the profiler say?

On what platform is this running? Dedicated or hosted (shared)? Are there other applications using this database engine that could affect the performance? Is the performance consistent? Is your mysql engine properly tuned. Do you have PHPMyAdmin handy? It has an option called status, which dumps the operational status of the database engine. Any figures reported in red?

There's a quick analysis tool at http://www.fromdual.com/mysql-performance-tuning-key. Past your server info and see what it comes up with.

For comparison, the site I mentioned earlier also has a forum section. Per user, per forum and per thread it keeps track of the read status of messages. Currently, it has close to a milion records, and inserts happen all the time, every time someone reads a forum message. I currently have 210 users reading messages. And no performance issues. This on a standard off-the-shelf Dell R200 with 2Gb, running 12 websites, including two Trac installations syncing with svn repositories. It processes on average 99.86 queries per second.
#14

[eluser]RaZoR LeGaCy[/eluser]
i have other queries running slow as well. I am listing a few from each table so you get the idea. ci_sessions is still about 50% of the mysql

Code:
# Query_time: 15  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE `hh_rViews` SET `hits` = hits+1 WHERE `rid` = '23582';
# Query_time: 16  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE `hh_rViews` SET `hits` = hits+1 WHERE `rid` = '23787';
# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
UPDATE `fa_user_profile` SET `user_score` = '0.88', `new_msgs` = 0, `tot_msgs` = 1, `tot_friends` = '0', `tot_collections` = '0', `tot_comments` = '0', `tot_profile_comments` = 0 WHERE `id` = 'XXXXXXXXXX';

I am on a VPS with Linux.

Not sure if other applications are using the database engine. Do you mean other websites? If so yes but these sites get really low traffic. How can I tune mysql properly, not a mysql guru so any recommended resources are welcome.

I have phpmyadmin and status reports these in red plus values. My god this is a mess :O)
Slow_queries = 1
Innodb_buffer_pool_pages_dirty = 3
Innodb_buffer_pool_reads = 385 k
Handler_read_rnd = 3,958 k
Handler_read_rnd_next = 114 M
Qcache_lowmem_prunes = 147 k
Created_tmp_disk_tables = 4,626
Select_full_join = 970
Sort_merge_passes = 480
Opened_tables = 2,598
Table_locks_waited = 123

I am working on the analysis tool right now and will post results soon.
#15

[eluser]WanWizard[/eluser]
A lot of these indicate queries that don't use indexes, requiring full reads, temp tables on disk, and sort operations. That will slow things down.

Also, lots of Qcache prunes due to low memory. My guess is that your VPS is low on memory and CPU, and MySQL is suffering as a result of that.
#16

[eluser]Crimp[/eluser]
Back to the beginning and just as a sidenote FYI: InnoDB is memory hungry for a MySQL server. If running, for example, a VPS and don't need the extras of InnoDB, you can skip InnoDB entirely in your MySQL configs to save memory. It's a common practice.
#17

[eluser]RaZoR LeGaCy[/eluser]
To start; thank you both for the help.

WanWizard
I removed/optimized (Added better indexes) to a few more queries but I think I will still need to increase the Qcache. How can I do that?

Crimp
I changed some tables that do not have frequent writes to MyISAM. That should free up memory.

How long will it take in order to re-check phpMyAdmin status tab to see what is now red after these changes? Should I restart and wait 2-3 days or can I just leave the system and then re-check in 2-3 days.

I still have the issue of 5 ci_session writes per page view. I use db_session.

I know that one ci_session write writes to the
flash:new:flashMessage\";s:32:\"You have successfully logged in.\";,
then the other writes to
flash:new:flashMessage\";s:32:\"You have successfully logged in.\";s:22:\"flash:old:flashMessage\";s:32:\"You have successfully logged in.\";
the other
flash:old:flashMessage\";s:32:\"You have successfully logged in.\";
the other
flash:old:flashMessage\";s:32:\"You have successfully logged in.\";
then
flash:old:flashMessage\";s:32:\"You have successfully logged in.\";s:22:\"flash:new:flashMessage\";b:0;

Hope that helps in finding out this problem.
#18

[eluser]RaZoR LeGaCy[/eluser]
I forgot to ask, What is a "gc probability of 10"?
#19

[eluser]RaZoR LeGaCy[/eluser]
Using this guide I set session expiration to 1200 (20 mins) and gc probability to 10. ci_sessions went from 3500 to 300 records.
http://dev.fyicenter.com/faq/php/php_usi...ions_4.php

I also modified the DB_session library and now it only writes 3 times. Saved 2 writes.
#20

[eluser]sqwk[/eluser]
[quote author="WanWizard" date="1280879058"]
We use a modified sess_write() method, that skips the write. We do an explicit write after the page request has been processed and the output generated, so only one session update per page request. That reduced the number of update queries by over 90%.[/quote]

Wan, could you post that modified sess_write()? Any disadvantages?

P.S: Sorry to roll up this old post.




Theme © iAndrew 2016 - Forum software by © MyBB