GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Very slow queries on server - copying to tmp table

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

  • Very slow queries on server - copying to tmp table

    Hi,

    I am have some unexpected problems with a production server.
    For few hours it become very very slowly and queries are taking a lot more than normally. I have stopped all applications and run some very simple queries and this is obvious

    While a normal query was taking 0.03 s to complete now it is taking like 100 s and I can see the query state is Copying to tmp table. It simply acts like it is not using the Indexes anymore

    Here is the my.cnf configuration from the live server (20 Gigs of RAM)

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    skip-bdb
    skip-name-resolve

    log-slow-queries=/var/lib/mysql/mysql-slow-queries.log
    long_query_time = 5
    # log-queries-not-using-indexes

    ft_min_word_len = 1
    ft_stopword_file =

    max_heap_table_size = 32M
    tmp_table_size = 128M
    max_connections = 500
    max_allowed_packet = 12M
    group_concat_max_len = 4096000
    wait_timeout = 30

    key_buffer_size = 2048M
    bulk_insert_buffer_size = 32M

    sort_buffer_size = 12M
    join_buffer_size = 8M
    read_buffer_size = 4M
    myisam_sort_buffer_size = 32M
    read_rnd_buffer_size = 10M

    thread_concurrency = 4
    thread_cache = 8
    thread_cache_size = 128
    table_cache = 1024

    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 2

    default-character-set = utf8

    # InnoDB
    innodb_data_home_dir = /var/lib/mysql/
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /var/lib/mysql/
    innodb_log_arch_dir = /var/lib/mysql/

    innodb_buffer_pool_size = 1024M
    innodb_flush_log_at_trx_commit = 2
    innodb_log_buffer_size = 8M
    # Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 256M
    innodb_flush_method = O_DIRECT
    innodb_file_per_table
    innodb_open_files = 1000

    Any advice would be really helpfull


    Art

  • #2
    Here are some errors from the mysql log around the time the problems appeared.

    091114 4:03:36 InnoDB: ERROR: the age of the last checkpoint is 120792325,
    InnoDB: which exceeds the log group capacity 120792269.
    InnoDB: If you are using big BLOB or TEXT rows, you must set the
    InnoDB: combined size of log files at least 10 times bigger than the
    InnoDB: largest such row.
    091115 3:03:13 InnoDB: ERROR: the age of the last checkpoint is 120792375,
    InnoDB: which exceeds the log group capacity 120792269.
    InnoDB: If you are using big BLOB or TEXT rows, you must set the
    InnoDB: combined size of log files at least 10 times bigger than the
    InnoDB: largest such row.
    091115 9:21:45 InnoDB: ERROR: the age of the last checkpoint is 120792453,
    InnoDB: which exceeds the log group capacity 120792269.
    InnoDB: If you are using big BLOB or TEXT rows, you must set the
    InnoDB: combined size of log files at least 10 times bigger than the
    InnoDB: largest such row.

    We are using both MyISAM and Innodb tables

    Art

    Comment


    • #3
      You can avoid using temp tables on disk, if you not using BLOB or TEXT columns, unless your mysql will create a temp table on disk every time.

      Another possibility is to set the temp tables location to a memory filesystem. That can help a lot too.

      Comment

      Working...
      X