GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MEMORY Table is slower than INNODB - Bug or Feature?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MEMORY Table is slower than INNODB - Bug or Feature?

    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.

    Comment


    • #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?

      Comment


      • #4
        One more point:

        The table is really tiny.
        As you can see its around 50-100 byte width and it has usually only about 1000 rows.

        So its really really tiny.

        Its surprising that such a tiny table gets so slow with the MEMORY engine in MySQL.

        Comment


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

          Comment


          • #6
            > 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.

            Comment


            • #7
              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

              Comment


              • #8
                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.

                Comment

                Working...
                X