MEMORY Table is slower than INNODB - Bug or Feature?

MySQL & MariaDB Other MySQL® Questions
1

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

2

What are your data typ,es? Note that the memory engine only has fixed length rows.

3

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?

4

Just 1000 rows, try an unindexed table. Full table scan should be pretty fast.
And probably this is not a bottleneck anyways.

5

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.

6

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

7

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.