sessions in mysql table

[antoniofalconx11426]

mine is at 4,452,839 taking up 5.7 Gigs of space

is this normal?

 

[adam]

That's A LOT of session data! How old is the data?

Sessions should be cleared from the database when a user logs out or the session expires.

 

[adam]

I've just checked our demo and local dev version and there's only data in the past 4 hours. Do you know if there was any changes to your code?

 

[antoniofalconx11426]

i applied the 3.3 update and that was it. i didn't make any code changes other than cosmetic ones.

could apache settings cause that?

 

[antoniofalconx11426]

could i empty the table without problems?

 

[adam]

Yes you can clear the data. If you execute this sql on your db it'll clear anything older than a day, just encase there are users currently logged in.

DELETE FROM `sessions` WHERE `updated_on` < (UNIX_TIMESTAMP()-60*60*24);

 

[antoniofalconx11426]

thanks, it's now 64,874 rows and 181 MB

 

[halalrizik2841]

My database shows 3 to 4 Gigs of sessions data, why isn't it clearing this automatically?

That's A LOT of session data! How old is the data?

Sessions should be cleared from the database when a user logs out or the session expires.

 

[antoniofalconx11426]

hope you figure it out and let me know. my sessions table is still gigantic. I have to truncate it before doing backups

 

[skintzel5348]

i can confirm that issue

 

[adam]

If you're running the crons it'll clear anything older than 14 days.

\admin\tasks\auto_prune.cron.php

 

[skintzel5348]

oh i thought 4 Hours.

 

[adam]

oh i thought 4 Hours.

No you need to allow for large uploads/downloads. You could probably get away with it being reduced to 3 days though.

 

[enricodias4654]

The script uses session to cache data from themes and plugins. Thats not the best way to do it and it may be slower than having no cache at all. If you tune your mysql correctly, there is no need to cache this data. Mysql itself has a cache.

 

[adam]

The script uses session to cache data from themes and plugins. Thats not the best way to do it and it may be slower than having no cache at all. If you tune your mysql correctly, there is no need to cache this data. Mysql itself has a cache.

Not true, at least in this context. For example, loading the plugin data loops over each of the plugins, loads the config files and any relating db data, then stores this in the session. So there's multiple lookups on the db. Storing this in session means there's only 1 lookup for the database and if you're using MySQL query cache it probably doesn't even hit the table on the read.

 

[enricodias4654]

But Adam, how many information is there in the plugins table? Not much. Even if it means many queries, those queries are likely to be cached in ram forever. If you replicate the same information over several sessions you are duplicating the data and creating more data. Note that information stored in text in the sessions table takes more space than information stored as numbers in other tables. The syntax to serialize this date also takes space. And I'm not sure but I think mysql doesn't even cache text fields in ram. (text, not char and varchar)

There are also problems with the query size. If the data in the sessions table is too big it will not be cached at all. And even if it does, since the table is too big your cache hits would be too low. There are some information that should be cached all the time and others that does not. Putting them all in the session takes away mysql ability to figure out what is more important.

You should not minimize the lockups on the db, you should minimize the disk access. This cache in session causes other issues, for example, the session must be recreated to load updated values from plugins and themes.

 

[enricodias4654]

There are many other issues with this. The session table have 3 of its 4 fields as indexes. Many indexes result in a larger size and slower inserts and updates. The index with the varchar 255 is also huge and may eat up the key buffer. If the id is an hex of 32 chars, why not use binary(16) in this field? It would be 2x smaller. The index can use only 8 bytes, there is no need to use 16. Collisions are unlikely in 8 bytes.

Why not use timestamp on updated_at? Does it needs to be an index? If this field is used only by the processes in background, does it need to be fast?

 

[adam]

I see your point but I'm not sure I want to rely on installs having MySQL query cache enabled. I'll setup some benchmarks at some stage and see if there's any improvements we can make to the whole sessions side.

 

[enricodias4654]

I edited my last post before you answer. But yes, some users may not be able to tune mysql correctly. But using sessions like that is worse even in the default mysql configs. Mysql 7 default configs are just 8mb of key_buffer_size and just 1mb of query_cache_size.

This session table should use around 10mb for thousands of users.

 

[paypal1352]

just noticed my sessions table is almost 900mb, autoprune runs everyday