GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Why split table got worse performance

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

  • Why split table got worse performance

    We have a big table for user call. And this table can have at most 100M records. The storage for this table is MyISAM. And since this table will be updated frequently, the table_lock_waited is very high. To reduce it, we tried to split the big table into 10 small ones: t1, t2…t10.
    After that we found the performance is even worse if we access multiple small tables at the same time. (

    In our testing, in each small table, there are 2.5M records. And all tables are in the same format, the data stored are also similar. The only difference is the data stores data for different users. We used two threads to do the testing: Thread1, Thread2.

    1. When thread1 query user data from t1, thread2 also query data from t1, the average response time is about 143ms

    Thread1: access table t1
    Thread2: access table t1

    2. When thread1 query data from t1, and thread2 query data from t2, the average response time is about 338ms.

    Thread1: access table t1
    Thread2: access table t2

    3. If only one thread thread1 query data from t1 and t2 (add a counter, if the counter%10=0, read data from t1, if counter%10=1, read data from t2), the average response time is about 137ms.

    Thread1: access table t1 and table t2 alternately

    4. Only one thread thread1 read data from t1, the average response time is 77ms.

    Thread1: only access table t1

    5. We tried use two thread to access MyISAM table and Innodb table, there is no such affect. Thread1 read data from t1, thread2 read data from innodb table test, the response time for thread1 is also about 80ms.

    Thread1: access table t1
    Thread2: access table test (innodb)

    So when we use multiple threads, the response will be slow than only one thread. This can be explained by our testing method. In our testing, after the first response returned, the second request will be sent immediately. The CPU usage will be always 100% even when only one thread is reading data from DB. So when two threads are used, mysql response time will become longer.

    But we don’t know why when two tables are accessed at the same time will have worse performance than only one table is accessed.

    The variable table_cache=494. And we checked open_table=120, opened_table=140.

    The following is my MyISAM table information:

    user_id | int(11)
    call_id | int(11)
    call_direct | tinyint(4)
    my_number | varchar(255)
    start_time | datetime
    end_time | datetime

    The index for the table is: user_id+call_id

    Thanks and regards,
    Myra
Working...
X