# MEMORY Table is slower than INNODB - Bug or Feature? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 21, 2009, 12:10pm UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279 "2009-10-21T12:10:42Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![gunnar](https://avatars.discourse-cdn.com/v4/letter/g/ed655f/32.png) [@gunnar](https://forums.percona.com/u/gunnar)\ **Post date:** [October 21, 2009, 12:10pm UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/1 "2009-10-21T12:10:42Z")
Hi Everybody, I tried to tune an application which uses MySQL to store sessions for multible webservers. The session table is small about 100 bytes per row. The session table has lost of insert/deletes and updates. And updates are mostly done to update “last-page click timestamps”. This setup creates of course a high IO write load for the MySQL server for data which is realtive unimportant and could be kept volatile. I though “thats easy to fix” and converted the table from type InnoDB to type Memory. This did remove the disk IO of the table. Nice! But here comes the gotcha. The MEMORY table showed up to be significant slower than InnoDB. I’ve recorded live SQL-access statements to the table (some million lines) and played these statements several times in parallel (16 threads) against the table. The Innodb needed 51 secs for this testcase. But the Memory table needs 130 seconds. There is 1 key on the table (a unique key on a session md5) I tried both types of Memory key-types (Hash and INDEX) The timing for the BTREE is slightly faster 125 seconds. But in both cases the memory table is significant slower than Innodb. Isn’t this strange? Everybody would expect that a memory table would have so much less overhead that I should be 10 times faster than Innodb. But in real live the table is 2 times slower than a disk-based table. Any ideas? Cheers Gunnar von Boehn ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [October 21, 2009, 12:50pm UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/2 "2009-10-21T12:50:18Z")
What are your data typ,es? Note that the memory engine only has fixed length rows. ---
**Author:** ![gunnar](https://avatars.discourse-cdn.com/v4/letter/g/ed655f/32.png) [@gunnar](https://forums.percona.com/u/gunnar)\ **Post date:** [October 21, 2009, 2:08pm UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/3 "2009-10-21T14:08:28Z")
> What are your data types? I’m not at work anymore, but here is the structure quoted out of my head: table fe\_sessions( md5 binary(32) Not null, ip\_group binary(10) not null, time1 int not null, time2 int not null, status tinyint, primary index on (md5) ) > Note that the memory engine only has fixed length rows. This is clear. ) The table does convert to memory very nicely. I can see that its a memory table afterwards. And as I mentioned disk IO goes down to zero too. The problem seems to be a serious bottleneck of the ISAM/MEMORY handler. Some other details: The used MySQL version is 5.1.38 with innodb-plugin 1.0.4 So basicly its last month MySQL server version. The Queries going to the table are only simple Select (by primary key) simple Deletes and simple updates But lots of these types of queries, as the DB is used as session backend. The table is a slightly sized tuned version of the original TYPO3 sessions table. The application is a typical TYPO3 application. Typo3 per default stores its sessions in a database table. The original Typo3 engine uses also a blob column but we have dropped it, as this column is mostly used by legacy stuff and not needed by us. Using this table for session management is normal for typo3. There are many HUGE Typo based webapplication which run this table structure. It ugly to see that this issues affects the whole world of them. ( Any ideas what bottleneck could be the reason for this? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [October 21, 2009, 3:58pm UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/4 "2009-10-21T15:58:15Z")
Just 1000 rows, try an unindexed table. Full table scan should be pretty fast. And probably this is not a bottleneck anyways. ---
**Author:** ![gunnar](https://avatars.discourse-cdn.com/v4/letter/g/ed655f/32.png) [@gunnar](https://forums.percona.com/u/gunnar)\ **Post date:** [October 22, 2009, 2:29am UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/5 "2009-10-22T02:29:08Z")
> Just 1000 rows, try an unindexed table. Full table scan should be pretty fast. > And probably this is not a bottleneck anyways. No, does not work. The DELETES, UPDATES use the primary key. Dropping the primary key will causes a huge slowdown. You need the key both for table type: MyISAM, MEMORY and for InnoDB. And without the key Inno would deadlock of course. Performance-Comparison: InnoDB = 52 seconds MyISAM = 140 seconds MEMORY = 130 seconds MEMORY\_without\_key = 700 seconds. ---
**Author:** ![gunnar](https://avatars.discourse-cdn.com/v4/letter/g/ed655f/32.png) [@gunnar](https://forums.percona.com/u/gunnar)\ **Post date:** [October 22, 2009, 4:00am UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/6 "2009-10-22T04:00:29Z")
I tried oprofile to get a clue where the bottleneck could be: Below are Oprofile output for the testcase, running on the Engines: InndoDB, MyISAM, and MEMORY The wallclock times clearly show that Inno is twice as fast as MEMORY engine in this benchmark. But I’ve problems spotting the bottleneck. Any ideas? Type INNODB : 51 Seconds samples % app name 5021856 35.2624 vmlinux-2.6.16.60-0.42.5-smp 4321733 30.3463 mysqld 1198274 8.4140 libc-2.4.so 1170771 8.2209 mysql 983962 6.9092 processor samples % app name symbol name 983962 6.9446 processor /processor 873131 6.1624 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_read\_port 371789 2.6240 mysqld MYSQLparse(void\*) 359913 2.5402 vmlinux-2.6.16.60-0.42.5-smp schedule 323874 2.2858 mysqld my\_pthread\_fastmutex\_lock 261642 1.8466 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_write\_port 232533 1.6412 libc-2.4.so \_int\_malloc 222472 1.5702 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_recvmsg 216794 1.5301 mysql find\_command(char\*, char) 209308 1.4773 mysql read\_and\_execute(bool) 205326 1.4491 vmlinux-2.6.16.60-0.42.5-smp try\_to\_wake\_up 170757 1.2052 mysqld my\_hash\_sort\_bin 154474 1.0902 libpthread-2.4.so pthread\_mutex\_trylock 149406 1.0545 libc-2.4.so memcpy 146667 1.0351 vmlinux-2.6.16.60-0.42.5-smp find\_busiest\_group 124021 0.8753 vmlinux-2.6.16.60-0.42.5-smp \_\_switch\_to 118086 0.8334 vmlinux-2.6.16.60-0.42.5-smp system\_call 114099 0.8053 libc-2.4.so malloc 113713 0.8026 mysqld MYSQLlex(void\*, void\*) 95782 0.6760 libpthread-2.4.so \_\_pthread\_mutex\_unlock\_usercnt 90603 0.6395 vmlinux-2.6.16.60-0.42.5-smp copy\_user\_generic 78613 0.5548 mysqld \_ZL15get\_hash\_symbolPKcjb 75052 0.5297 mysqld my\_strnncoll\_binary 74232 0.5239 libc-2.4.so free 73356 0.5177 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_sendmsg 70765 0.4994 libc-2.4.so strlen 70525 0.4978 libc-2.4.so \_int\_free 70449 0.4972 vmlinux-2.6.16.60-0.42.5-smp fget\_light 70179 0.4953 mysqld lex\_start(THD\*) 69360 0.4895 vmlinux-2.6.16.60-0.42.5-smp \_\_find\_first\_bit 68970 0.4868 vmlinux-2.6.16.60-0.42.5-smp recalc\_task\_prio 67653 0.4775 libc-2.4.so strstr 66088 0.4664 vmlinux-2.6.16.60-0.42.5-smp vfs\_read 64772 0.4571 ha\_innodb\_plugin.so.0.0.0 \_ZL14build\_templateP19row\_prebuilt\_structP3THDP8st\_tablej 64612 0.4560 mysqld dispatch\_command(enum\_server\_command, THD\*, char\*, unsigned int) 64235 0.4534 vmlinux-2.6.16.60-0.42.5-smp sys\_fcntl 60643 0.4280 vmlinux-2.6.16.60-0.42.5-smp do\_futex 59925 0.4229 ha\_innodb\_plugin.so.0.0.0 ut\_delay 59849 0.4224 mysql my\_strnncoll\_simple 56132 0.3962 vmlinux-2.6.16.60-0.42.5-smp futex\_wait\_abstime 56020 0.3954 libc-2.4.so vfprintf 55167 0.3894 libpthread-2.4.so \_\_read\_nocancel 54956 0.3879 vmlinux-2.6.16.60-0.42.5-smp thread\_return 54742 0.3864 libc-2.4.so malloc\_consolidate 54048 0.3815 vmlinux-2.6.16.60-0.42.5-smp do\_sys\_poll 53101 0.3748 libc-2.4.so memset 53039 0.3743 vmlinux-2.6.16.60-0.42.5-smp kfree 52237 0.3687 vmlinux-2.6.16.60-0.42.5-smp skb\_dequeue 51308 0.3621 mysql my\_real\_read 50888 0.3592 mysqld \_ZL12my\_real\_readP6st\_netPm 50144 0.3539 vmlinux-2.6.16.60-0.42.5-smp \_\_kmalloc 49622 0.3502 mysqld alloc\_root 48869 0.3449 libc-2.4.so strcmp * * * Type MyISAM : 113 Seconds samples % app name 1394660 45.2217 vmlinux-2.6.16.60-0.42.5-smp 723461 23.4582 mysqld 473271 15.3458 processor 190863 6.1887 libc-2.4.so 182368 5.9133 mysql samples % app name symbol name 473271 15.3939 processor /processor 437751 14.2385 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_read\_port 125258 4.0742 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_write\_port 82077 2.6697 vmlinux-2.6.16.60-0.42.5-smp schedule 62713 2.0398 mysqld MYSQLparse(void\*) 59142 1.9237 vmlinux-2.6.16.60-0.42.5-smp find\_busiest\_group 50020 1.6270 vmlinux-2.6.16.60-0.42.5-smp try\_to\_wake\_up 43896 1.4278 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_recvmsg 43360 1.4104 vmlinux-2.6.16.60-0.42.5-smp \_\_switch\_to 37246 1.2115 libc-2.4.so \_int\_malloc 32647 1.0619 mysql find\_command(char\*, char) 32326 1.0515 mysql read\_and\_execute(bool) 28468 0.9260 mysqld my\_hash\_sort\_bin 27916 0.9080 mysqld my\_pthread\_fastmutex\_lock 26518 0.8625 vmlinux-2.6.16.60-0.42.5-smp \_\_find\_first\_bit 23291 0.7576 libpthread-2.4.so pthread\_mutex\_trylock 23077 0.7506 libc-2.4.so memcpy 20867 0.6787 vmlinux-2.6.16.60-0.42.5-smp reschedule\_interrupt 19260 0.6265 vmlinux-2.6.16.60-0.42.5-smp system\_call 18283 0.5947 mysqld MYSQLlex(void\*, void\*) 17535 0.5704 libc-2.4.so malloc 14552 0.4733 vmlinux-2.6.16.60-0.42.5-smp thread\_return 13422 0.4366 mysqld \_ZL15get\_hash\_symbolPKcjb 13213 0.4298 vmlinux-2.6.16.60-0.42.5-smp copy\_user\_generic 13021 0.4235 libpthread-2.4.so \_\_pthread\_mutex\_unlock\_usercnt 12854 0.4181 mysqld my\_strnncoll\_binary 12404 0.4035 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_sendmsg 11466 0.3729 vmlinux-2.6.16.60-0.42.5-smp recalc\_task\_prio 11072 0.3601 mysqld lex\_start(THD\*) 10773 0.3504 libc-2.4.so strlen 10613 0.3452 vmlinux-2.6.16.60-0.42.5-smp fget\_light 10527 0.3424 libc-2.4.so strstr 10471 0.3406 libc-2.4.so free 10306 0.3352 vmlinux-2.6.16.60-0.42.5-smp do\_futex 10074 0.3277 libc-2.4.so \_int\_free 9979 0.3246 vmlinux-2.6.16.60-0.42.5-smp sys\_fcntl 9863 0.3208 libc-2.4.so vfprintf 9493 0.3088 mysqld dispatch\_command(enum\_server\_command, THD\*, char\*, unsigned int) 9211 0.2996 vmlinux-2.6.16.60-0.42.5-smp vfs\_read 9045 0.2942 vmlinux-2.6.16.60-0.42.5-smp find\_vma 9030 0.2937 vmlinux-2.6.16.60-0.42.5-smp skb\_dequeue 9005 0.2929 libc-2.4.so malloc\_consolidate 8951 0.2911 vmlinux-2.6.16.60-0.42.5-smp acpi\_hw\_register\_read 8886 0.2890 mysql my\_strnncoll\_simple 8578 0.2790 vmlinux-2.6.16.60-0.42.5-smp effective\_prio 8559 0.2784 libc-2.4.so memset * * * Type MEMORY : 105 Seconds samples % app name 1895417 45.9005 vmlinux-2.6.16.60-0.42.5-smp 941151 22.7915 mysqld 637113 15.4287 processor 254590 6.1653 libc-2.4.so 248435 6.0162 mysql 127835 3.0957 libpthread-2.4.so 11526 0.2791 oprofiled samples % app name symbol name 637113 15.4760 processor /processor 614390 14.9241 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_read\_port 175680 4.2674 vmlinux-2.6.16.60-0.42.5-smp acpi\_os\_write\_port 109714 2.6650 vmlinux-2.6.16.60-0.42.5-smp schedule 83065 2.0177 mysqld MYSQLparse(void\*) 80324 1.9511 vmlinux-2.6.16.60-0.42.5-smp find\_busiest\_group 64831 1.5748 vmlinux-2.6.16.60-0.42.5-smp try\_to\_wake\_up 59680 1.4497 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_recvmsg 56180 1.3647 vmlinux-2.6.16.60-0.42.5-smp \_\_switch\_to 51819 1.2587 libc-2.4.so \_int\_malloc 44999 1.0931 mysql find\_command(char\*, char) 44072 1.0705 mysql read\_and\_execute(bool) 39263 0.9537 mysqld my\_hash\_sort\_bin 35862 0.8711 mysqld my\_pthread\_fastmutex\_lock 35302 0.8575 vmlinux-2.6.16.60-0.42.5-smp \_\_find\_first\_bit 29710 0.7217 libc-2.4.so memcpy 29159 0.7083 libpthread-2.4.so pthread\_mutex\_trylock 27454 0.6669 vmlinux-2.6.16.60-0.42.5-smp reschedule\_interrupt 25848 0.6279 vmlinux-2.6.16.60-0.42.5-smp system\_call 24525 0.5957 libc-2.4.so malloc 24491 0.5949 mysqld MYSQLlex(void\*, void\*) 19673 0.4779 mysqld my\_strnncoll\_binary 19663 0.4776 vmlinux-2.6.16.60-0.42.5-smp thread\_return 17665 0.4291 mysqld \_ZL15get\_hash\_symbolPKcjb 17186 0.4175 vmlinux-2.6.16.60-0.42.5-smp copy\_user\_generic 16457 0.3998 vmlinux-2.6.16.60-0.42.5-smp unix\_stream\_sendmsg 15581 0.3785 mysqld lex\_start(THD\*) 15313 0.3720 libpthread-2.4.so \_\_pthread\_mutex\_unlock\_usercnt 14924 0.3625 vmlinux-2.6.16.60-0.42.5-smp recalc\_task\_prio 14725 0.3577 vmlinux-2.6.16.60-0.42.5-smp fget\_light 14282 0.3469 libc-2.4.so strlen 14214 0.3453 libc-2.4.so strstr 13974 0.3394 vmlinux-2.6.16.60-0.42.5-smp sys\_fcntl 13855 0.3365 libc-2.4.so free 13812 0.3355 libc-2.4.so \_int\_free 13396 0.3254 vmlinux-2.6.16.60-0.42.5-smp do\_futex ---
**Author:** ![vgatto](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vgatto/32/748_2.png) [@vgatto](https://forums.percona.com/u/vgatto)\ **Post date:** [October 22, 2009, 9:49am UTC](https://forums.percona.com/t/memory-table-is-slower-than-innodb-bug-or-feature/1279/7 "2009-10-22T09:49:33Z")
This could be due to locking granularity, since MEMORY and MyISAM will be locking at the table level and InnoDB will lock at the row level. If this is the case, reducing the number of threads in your test should bring the numbers for all storage engines closer together, and increasing the threads should move them farther apart (though it should flatten out at some point). If you don’t care about persistence of your session data, memcached will outperform MySQL hands down for this type of workload. If you do care about persistence, there’s Tokyo Tyrant/Tokyo Cabinet that was written about recently in the Percona blog that may be worth looking into.