GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Several MySQL performance questions

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

  • Several MySQL performance questions

    Hi,

    We use MySQL as DB in our product. Recently we found there are some performance issues in our product, so we are performing some DB testing and optimization. We found some issues in our testing and we don't know how to explain them. Could anyone help to look at them? We are looking for your help eek:



    The testing environment is SUN X2100 server, Redhat Linux 3 update 8 and MySQL 4.1.13.



    1. Integer index slower than string index

    There is a huge MyISAM table with about 100M entries. To improve the access efficiency of this table, we are planning to change index of this table. Currently the index is

    partitionID (varchar (36), fixed value of 9 characters string in our testing)

    + userID (varchar (36), 10 digits numberic string in testing)

    + timestamp



    We want to introduce a new interger uID to replace partitionID + userID, so the new index will be:

    uid (integer)

    + timestamp



    But in our testing, access the table with new table is slower than old one. The 'query' operation is a little (10%) slower, and 'update' operation is similar. We can't find any reason that the interger index is slower than string index. Do you have any idea?

    Btw, the testing was performed by a single thread in sequential mode. The testing result is repeatable.



    2. Peak of response time

    In out testing, we found there are perodically peak response time. For example, the first 10 queries only spent 5ms as average, but the 11th queries spent 30ms. Then another fast 10 queries and one slow query follows.



    Since we disabled MySQL cache through set query_cache_size to 0, we suspect this peak is caused by OS cache. Do you have other idea?





    3. High I/O wait when innoDB table syncs

    Recently we found that when we write a innoDB table to master D-node quickly, the I/O wait time of slave D node is higher than master DB. It's 80% I/O wait CPU usage on master DB and about 90% I/O wait on slave.

    That table was MyIsam table before, we just found this issue when we change it to InnoDB table. And if changes the storage back to MyIsam, the I/O wait CPU usage will go down again.



    4. Error 2013:
    Lost connection to MySQL server during query when dumping table `user_location` at row: 7076



    We use the following command to dump the data form master node to slave node, but every time we got error 2013 after it running about 2.5 hours.

    /opt/lps/current/mysql/bin/mysqldump --user=lps -h lcm-dev14-n1-int.qd.lucent.com --opt --master-data --all-databases | /opt/lps/current/bin/mysql_client.sh --user=lps --host=acm-st14-n1-int.qd.lucent.com



    In mysql_client.sh, the mysql script will be invoked.



    In master DB, there is one big table with MyIsam storage-user_log; there are about 12M records in this table. The replication for that big table is finished correctly. After that table, when began to replicate another small table user_location with 10K records, this error is returned. If I truncate the big table user_log, the replication can be finished without any error.

  • #2
    I can only really provide an answer for 1) at the moment:

    Does the new table have an additional field? More fields + indexes = increased UPDATE / INSERT times, plus more usage of the MyISAM key buffer.

    Do you use key compression on your non-numerical type columns? This will reduce space / index size.

    How big are your buffers?

    Comment


    • #3
      Speeple,
      Thanks for your help.
      We just the integer uid in the new table:
      - Each entry of old table is about 90 bytes, most of columns are string (varchar).
      - The entry of new table has a new integer column.

      We use the default key compression. It seems the numeric key is not compressed and non-numeric key is compressed. I don't know which buffer you are asking. How can I get the number of it?

      Even with the non-numeric key compression, I think the integer index should not be slower than non-numeric index. I think even if it's not quite faster than compressed non-numeric key, the integer key should always be the fastest.

      Thanks again.

      Comment


      • #4
        Yes, from all my experience INT types tend to be faster.

        Try this to get the size of cache for MyISAM indexes:

        SHOW VARIABLES LIKE 'key_buffer_size'

        That's the size in bytes.

        Then you can guess the optimum value (making sure it's with limits of your available RAM) by doing:

        SHOW TABLE STATUS WHERE Engine='MyISAM';

        The sum of all the "Index_length" columns would be your optimum key_buffer size (plus a few megs).

        Comment


        • #5
          Speeple,
          The 'key_buffer_size' for our user is 8388600.And the "Index_length" for our MyISAM table is:831503360. You know, there are about 100 millions entries in this table. So the index length is too big.
          Now, we suspect the new table speed is slower due to frequently adding and deleting entries in this table. Since we are performing different testing on this table, so the entries were added and deleted frequently. We suspect this adding/deleting lead to index fragments, which increases DB operation time.
          We'll update this when we get more testing result.

          Comment


          • #6
            So your index size is around 800Mb, but your key buffer size is ONLY ~8Mb?

            How much RAM does this machine have? Surely you can afford to give the buffers more than 8Mb?

            Comment

            Working...
            X