Horrible Performance, Needs some tips

  • Filter
  • Time
  • Show
Clear All
new posts

  • Horrible Performance, Needs some tips

    Hello there, thanks for taking the time to read my post!

    I have been brought in to help consult with a local .com here. They wanted hardware consultation for upgrading / adding to the MySQL server environment. I am quite certain they actually have optimization issues with their server that could greatly improve performance. However I will need some help from experts such as yourselves, where I don't have a ton of experience tweaking mysql.

    I am going to include some pictures here :


    Please let me know if you have any input, I would greatly appreciate any suggestions. We are seeing very slow queries, I'm sure a lot of it is due to lack of query optimization, but I'm sure there are some server settings that would help. If you need me to post anything from the configuration file please let me know.

    It looks like they are currently using Myisam, would it be advantageous to use InnoDB? At this time though I need to tweak it as best as can be for what they have set up.

    Also, some interesting settings from the my.cnf file are:

    # cat /etc/my.cnf | grep -i buffer
    key_buffer_size = 2G
    sort_buffer_size = 12M
    # MUST BE SAME SIZE AS read_buffer_size
    read_buffer_size = 8M
    read_rnd_buffer_size = 12M
    myisam_sort_buffer_size = 256M
    join_buffer_size = 4M
    search_cache.key_buffer_size = 4G
    # You can set .._buffer_pool_size up to 50 - 80 %
    #innodb_buffer_pool_size = 384M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_buffer_size = 8M
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    Thank you so much for any advice you can give


  • #2
    By the way, the server is a

    3.4 GHZ x 4 CPU
    32 GB of RAM
    400 GB RAID 5 SCSI, I believe 15K SAS

    The iowait on the server is getting killed, I think some of the queries and indexes need some serious attention, but in the mean time something seems strange to me in the configuration, I'm sure you folks can help. Thanks again

    Also, when I do:

    du -ch /var/lib/mysql/*/*MYI | tail -n1
    97G total

    That seems insanely large to me, is that going to kill the performance regardless? I'm seeing things like making the key_buffer_size half of the ram, and others say it should be enough to cover the indexes, but no way is that possible.


    • #3
      Your key buffer is an issue. 97G is a lot, find out which part is actively used.

      Increasing table_cache will also help some.


      • #4
        Thank you so so much for the reply, I had just adjusted the table_cache to be larger, and it seems to be helping greatly. However, we just restarted the mysql process and already it has:

        Created_tmp_disk_tables 8,351
        Created_tmp_files 72
        Created_tmp_tables 11 k

        Also, how can I tell how much of the indexes are actually used?

        Again I really appreciate your input, thank you so much for any and all help.

        Tim G


        • #5
          You will have to analyse queries, or use a special mysql build that keeps track of index usage.


          • #6
            Great, I will look into how to do that this week! Thank so much, any other tuning tips would be greatly appreciated.



            • #7
              From what I see so far I can only say that the server is badly misconfigured, but it is not clear that this is causing any performance issues. You need to do server analysis, not configuration analysis. I would suggest getting professional help on this (of course, we do this).

              The issues I see in the configuration file are that several of the buffers are specified multiple times with different sizes, and some of them are ridiculously oversized and might cause serious problems (might, or might not). For example,

              sort_buffer_size = 256M

              That is a crime. However, the configuration cannot be examined in isolation; the workload (queries) needs to be analyzed, and the data and schema (data types, indexes) too.


              • #8
                Thanks so much for your input, get this though, the sort_buffer is actually loading in at 12M, but the myisam_sort_buffer is loading in at 256M... Is this a problem? Should they both be matching values? Is 256M way too high for myisam_sort_buffer?


                ---- Nevermind, it looks like these are acceptable values... Checking into the query log now.


                • #9
                  I am making some headway I think, this is the slowest query on the server... :

                  Count : 556 (18.58%)
                  Time : 11313.025136 s total, 20.347168 s avg, 10.033493 s to 188.013877 s max (8.00%)
                  95% of Time : 9512.041179 s total, 18.01523 s avg, 10.033493 s to 46.161019 s max Lock Time (s) : 1177.904267 s total, 2.118533 s avg, 40 µs to 53.029805 s max (61.97%)
                  95% of Lock : 452.82385 s total, 857.621 ms avg, 40 µs to 16.22464 s max
                  Rows sent : 1 avg, 1 to 1 max (0.00%)
                  Rows examined : 1.03M avg, 958.93k to 1.04M max (25.58%)
                  Database : bbox3
                  Users :
                  secretbroker@web1 : 100.00% (556) of query, 72.97% (2184) of all users
                  EXPLAIN : 1640100 produced, 2.30179865594012e+32 read
                  id: 1
                  select_type: SIMPLE
                  table: p
                  type: ALL
                  possible_keys: qty
                  rows: 958932
                  Extra: Using where; Using filesort

                  Query abstract:
                  SET timestamp=N; SELECT SQL_CALC_FOUND_ROWS * FROM bbox3_wdpi.parts AS p WHERE p.qty > 'S' ORDER BY p.partsno ASC LIMIT N, N;

                  Query sample:
                  SET timestamp=1280243874;
                  SELECT SQL_CALC_FOUND_ROWS * FROM `bbox3_wdpi`.`parts` as p WHERE p.`qty` > '0' ORDER BY p.`partsno` ASC LIMIT 0, 1;

                  It looks like it isn't using any keys... I'm thinking that's the issue. What can I do to make it use keys?



                  • #10
                    That's a good finding, but looks like it could be an outlier. Take a look at mk-query-digest from maatkit, it's a nicer query log analysis tool. Log ALL queries for a while -- several hours if you can. If you don't have a 5.1 server or a Percona build, use mk-query-digest's TCP analysis functionality to emulate it.

                    I'd question what that query is really doing. Search for sql_calc_found_rows on mysqlperformanceblog.com and you'll find lots on that. It looks like the stereotypical bad pagination pattern to me, and the app needs to be changed, rather than making changes to the query or the indexing or something.


                    • #11
                      Use tuning script to tune all system and mysql parameters