3

I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this).

As it says here: http://dev.mysql.com/doc/refman/5.7/en/memory-storage-engine.html

MEMORY performance is constrained by contention resulting from single-thread execution and table lock overhead when processing updates. This limits scalability when load increases, particularly for statement mixes that include writes.

I don't understand this statement. It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct? Can this be disabled? I need to write and read quite frequently and at the same time and I am not planning on doing transactions on this table:

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `data` varchar(64000) NULL,
  `expires` int(11) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`id`)
)ENGINE=MEMORY;
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Cyberlover
  • 31
  • 2

2 Answers2

2

You said in your question

It makes me think that when you insert/update/replace the table locks itself automatically and entirely so you will not be able to select anything while the write is being done. Is this correct?

You are correct.

The MEMORY storage engine performs a full table lock for every DML statement. That is why the link you mentioned shows Lock granularity : Table just like MyISAM. You cannot disable this.

In addition to this, please keep in mind that the MEMORY storage engine still imposes some mild disk I/O for both reads and writes. Why ? An open file handle is maintained on a MEMORY table to at least check on the table's existence and accessibility (See my old most I am using the MEMORY storage engine but MySQL still writes to my disk...Why?)

Please see my other old post Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM? on the pros and cons of using MEMORY tables.

You may have to resort to using InnoDB for such session data.

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
0

You can probably do a thousand simple commands on a MEMORY table per second. Can you, the single user, type fast enough to exceed that??

If you can, InnoDB may be the answer. In at least one test, high speed ingestion worked better with InnoDB, presumably because of the differences in locking.

Be aware that data varchar(64000) NULL is not efficient in MEMORY -- it is converted to CHAR, so it always takes the full 64K.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • I choose data varchar(64000) because all other people that already did this on the internet use text to store data.

    Since text max storage is 64Kb http://stackoverflow.com/questions/6766781/maximum-length-for-mysql-type-text

    I've picked varchar(64000) because it is the equivalent, because MEMORY does not allow text.

    Also I've been doing some math using this select: http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database

    With 1 register the table weight is 310Kb, with 10 is 610Kb. For 1000 rows I supose it will be 244 Mb.

     – Cyberlover Apr 15 '16 at 15:17
  • Anyway if things start to go south fast I can disable this by changing a single parameter configuration so thats that.

    Previously I did something similar by mounting the PHP session folder in RAM and never had a problem. But that requires configuration on service level.

    This is just an alternative I am testing in order to do the same in application level.

     – Cyberlover Apr 15 '16 at 15:26