Announcement

Announcement Module
Collapse
No announcement yet.

Please help with optimization

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

  • Please help with optimization

    Hello. I have server with 24 processors and 16 GB of RAM. Mysql files are located on partition which is built with 3 64GB SSD hard drives in RIAD5. OS is Fedora 11 x86_64. My database is mainly InnoDB (95% of tables).
    My problem is that some not complicated queries are getting stuck for 100+ seconds even when server is not under heavy load.
    Can anybody please help to understand and fix this problem. I think I made something wrong in my.cnf.
    Here is my full my.cnf:

    [mysqld]
    user = mysql

    port = 3306
    socket =/var/lib/mysql/mysql.sock

    max_connections = 2048

    back_log = 128

    max_connect_errors = 1000

    connect_timeout = 2

    max_allowed_packet = 16M

    net_buffer_length = 8K


    datadir = /home/mysql/data

    log-error = /var/log/mysqld_error.log

    slow_query_log = 1
    slow_query_log_file = /var/log/mysql-slow.log
    log_output = FILE
    long_query_time = 3

    table_cache = 10000

    join_buffer_size = 32M

    tmp_table_size = 64M

    sort_buffer_size = 64M

    thread_cache_size = 64
    thread_concurrency = 8
    thread_stack = 192K

    query_cache_size = 1024M
    query_cache_type = 1
    query_cache_limit = 16M

    transaction_isolation = REPEATABLE-READ

    key_buffer_size = 256M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 128M
    bulk_insert_buffer_size = 64M
    myisam_max_sort_file_size = 10G
    myisam_repair_threads = 2



    innodb_data_home_dir =
    innodb_data_file_path=/home/mysql/ibdata/ibdata1:1G;/home/my sql/ibdata/ibdata2:1G;/home/mysql/ibdata/ibdata3:1G;/home/my sql/ibdata/ibdata4:1G;/home/mysql/ibdata/ibdata5:10M:autoext end

    innodb_file_per_table

    innodb_buffer_pool_size = 5G

    innodb_additional_mem_pool_size = 512M

    innodb_log_group_home_dir = /home/mysql/iblogs

    innodb_log_files_in_group = 4

    innodb_log_file_size = 512M

    innodb_log_buffer_size = 8M

    innodb_max_dirty_pages_pct = 80

    innodb_flush_log_at_trx_commit = 1

    innodb_lock_wait_timeout = 50

    innodb_flush_method = O_DIRECT

    innodb_thread_concurrency = 12

    innodb_fast_shutdown = 0

    innodb_max_purge_lag = 0

    innodb_support_xa = 0

  • #2
    You will have to provide more information about what the "not complicated queries" look like, and the execution plan from EXPLAIN EXTENDED for these queries.
    If we are going to have a chance to tell you what is wrong.

    Comment


    • #3
      Here is one of the queries that is getting stuck for a long time:

      mysql> EXPLAIN EXTENDED SELECT COUNT( * ) AS `count`
      -> FROM `wum_users` `users`
      -> LEFT JOIN `wum_users_groups` ug ON ( ug.`user_id` = users.`id` )
      -> LEFT JOIN `users_gps` gps ON ( gps.`user_id` = users.`id` )
      -> WHERE 1
      -> AND `users`.`enable` = '1'
      -> AND `ug`.`group_id` = '3'
      -> AND `users`.`sex` = '2'
      -> AND `users`.`age` <= '31'
      -> AND `gps`.`node_id` = '73'
      -> ORDER BY `users`.`creation_date` DESC , `users`.`has_photo` DESC;
      +----+-------------+-------+--------+----------------------- -------------------------------------------------+---------- -----------+---------+---------------------+-------+-------- --+------------------------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
      +----+-------------+-------+--------+----------------------- -------------------------------------------------+---------- -----------+---------+---------------------+-------+-------- --+------------------------------+
      | 1 | SIMPLE | gps | ref | user_id,user_to_gps_node_id | user_to_gps_node_id | 4 | const | 45374 | 100.00 | Using where; Using temporary |
      | 1 | SIMPLE | users | eq_ref | PRIMARY,ind_age,ind_sex,ind_enable,obshi,ind_last_ onlines,wh os_onlines | PRIMARY | 4 | edesirs.gps.user_id | 1 | 100.00 | Using where |
      | 1 | SIMPLE | ug | ref | fk_ru,fk_rg,common | fk_ru | 4 | edesirs.gps.user_id | 1 | 100.00 | Using where |
      +----+-------------+-------+--------+----------------------- -------------------------------------------------+---------- -----------+---------+---------------------+-------+-------- --+------------------------------+
      3 rows in set, 1 warning (0.00 sec)



      But my question is if my my.cnf is good for my hardware?

      Comment


      • #4
        Your config is OK.
        But there certainly isn't something in it that should cause a simple query like this with indexes and only ~45,000 rows examined in one table to take 100+ seconds.

        Exactly how is it hanging?
        What is the exact status in the processlist?

        Because it sounds like it is waiting for something else to happen.

        Comment

        Working...
        X