sessions table in memory

[Gabriel Torres]

Hi,

It is a common recommendation here to move the sessions table to memory.

However, today I got the following issue: couldn't login to ACP (database error), online user list showed only 17 online users (when we usually have around 1000~2000).

Checking mysql log, found this:

2014-07-28 18:16:22 23604 [ERROR] /usr/local/mysql/bin/mysqld: The table 'sessions' is full

Checking the sessions table:

mysql> SELECT count(*) FROM sessions;
+----------+
| count(*) |
+----------+
| 17 |
+----------+
1 row in set (0.00 sec)

Strange, huh? Fixed the problem with DELETE FROM sessions; However, I wonder what may have caused this.

Asked support for an opinion, and they replied this:

Hello, we don't recommend using memory for that table, please change that to innodb if supported, if not use myisam. If your server is low on memory, then it will fill up etc.

[Rhett]

This is actually an old recommendation, it's best to use innodb if your server supports it, if not use myisam as mentioned.

[Gabriel Torres]

Hi Rhett, I posted publicly here the answer you gave me so other people are aware of this issue, as members of this community were the ones who recommended me to move the sessions table to memory.

I moved back to InnoDB/XtraDB.

Thanks,

Gabriel.

[Dmacleo]

I was under the assumption that using memory for that table was still the best, I am (iirc) all innodb so I will swap it if that is the best way.

[Mark H]

If you have plenty of memory, and don't have "daily server crashes" due to a.... well, less than stellar host, then memory works fine for that particular table. I run sessions in memory on my own server and it's worked fine for ages.

If the 'sessions' table data is lost, due to a server crash or a deliberate reboot, the only consequence is that everyone who was logged in, has to login again.

This is, of course, not the case with other tables, where their data loss would be fatal.

[Dmacleo]

on the edge ramwise (16gb) but have not had any issues, was just wondering if it would release some ram that then would be utilized as needed; most likely sql.

[RevengeFNF]

In your my.cnf what is the value you have for: max_heap_table_size?

Because that's what limit the size of your memory table,

For example, i got around between 200 and 500 users online at the same time and my session table occupies 44Mb.in memory. So yours will occupy a lot more.

[Dmacleo]

I was just looking and I see about the same amount for same online.

really not enough released to be worth anything so I guess I'll leave it as is in memory.

[Gabriel Torres]

We have 64 GiB on this server, so I thought it strange...

In your my.cnf what is the value you have for: max_heap_table_size?

That was definitely the reason why I saw this crash... Had 128 MiB configured... Thanks for pointing this out...

max_heap_table_size=128M

Anyway, I will keep it as InnoDB for now.

[Rhett]

We have 64 GiB on this server, so I thought it strange...

That was definitely the reason why I saw this crash... Had 128 MiB configured... Thanks for pointing this out...

max_heap_table_size=128M

Anyway, I will keep it as InnoDB for now.

The advantage of moving sessions to memory is/was to account for a mysql server that may not be performing too well or was having issues with sessions table crashing as myisam. If you have a good running mysql server, then using innodb or even myisam as the engine works just great, with that said, if you increase the max_heap_table_size and leave it as memory, then it would likely be fine too.