Announcement

Announcement Module
Collapse
No announcement yet.

Need help tuning MYSQL, please.

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

  • Need help tuning MYSQL, please.

    Hello everyone,

    I'm hoping someone from this forum to help me quick solve our server problem.

    Long story short, our old DB admin/designer leave our company last year. And since then we don't really have a DB admin.

    Which is fine, at least the server is running all good without him till few weeks ago. All the queues takes 10 to 20 times longer to get get result.

    I'm guessing the config need some kind of fine tune after a year.

    Please feel free to let us know what we need to do.
    Thank you!

    The server is still on a P4 631 with 2G ram. Linux 2.4.20-8. MySQL 3.23
    About 4G data, tables range from 300MB to 1MB.
    Read/Write ratio is 0.002
    MyISAM

    Here's the setting:
    Quote:

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /var/lib/mysql/mysql.sock
    skip-locking
    set-variable = key_buffer=384M
    set-variable = max_allowed_packet=3M
    # Modified on 2007-10-23
    #set-variable = max_allowed_packet=1M
    set-variable = table_cache=512
    set-variable = sort_buffer=2M
    set-variable = record_buffer=2M
    set-variable = thread_cache=8
    # Try number of CPU's*2 for thread_concurrency
    set-variable = thread_concurrency=8
    set-variable = myisam_sort_buffer_size=64M
    #log-bin
    server-id = 1

    log-update = /home/mysql/log/mysqllog
    log-long-format

    # Point the following paths to different dedicated disks
    #tmpdir = /tmp/
    #log-update = /path-to-dedicated-directory/hostname

    [mysqldump]
    quick
    #set-variable = max_allowed_packet=16M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [isamchk]
    set-variable = key_buffer=256M
    set-variable = sort_buffer=256M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [myisamchk]
    set-variable = key_buffer=256M
    set-variable = sort_buffer=256M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [mysqlhotcopy]
    interactive-timeout

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

  • #2
    Login to MySQL and run:


    show status;


    And paste the results here. Please make sure MySQL has been running under load for a few days first.

    Comment


    • #3
      here's my showstatus:

      Quote:


      Variable_name Value
      Aborted_clients 1
      Aborted_connects 0
      Bytes_received 29121325
      Bytes_sent 219271982
      Com_admin_commands 290
      Com_alter_table 0
      Com_analyze 0
      Com_backup_table 0
      Com_begin 0
      Com_change_db 29
      Com_change_master 0
      Com_check 0
      Com_commit 0
      Com_create_db 0
      Com_create_function 0
      Com_create_index 0
      Com_create_table 0
      Com_delete 194
      Com_drop_db 0
      Com_drop_function 0
      Com_drop_index 0
      Com_drop_table 0
      Com_flush 0
      Com_grant 0
      Com_insert 747
      Com_insert_select 0
      Com_kill 0
      Com_load 0
      Com_load_master_table 0
      Com_lock_tables 0
      Com_optimize 0
      Com_purge 0
      Com_rename_table 0
      Com_repair 0
      Com_replace 0
      Com_replace_select 0
      Com_reset 0
      Com_restore_table 0
      Com_revoke 0
      Com_rollback 0
      Com_select 68527
      Com_set_option 0
      Com_show_binlogs 0
      Com_show_create 0
      Com_show_databases 2
      Com_show_fields 0
      Com_show_grants 0
      Com_show_keys 0
      Com_show_logs 0
      Com_show_master_status 0
      Com_show_open_tables 0
      Com_show_processlist 2
      Com_show_slave_status 0
      Com_show_status 6
      Com_show_innodb_status 0
      Com_show_tables 11
      Com_show_variables 4
      Com_slave_start 0
      Com_slave_stop 0
      Com_truncate 0
      Com_unlock_tables 89
      Com_update 1305
      Connections 188
      Created_tmp_disk_tables 0
      Created_tmp_tables 835
      Created_tmp_files 0
      Delayed_insert_threads 0
      Delayed_writes 0
      Delayed_errors 0
      Flush_commands 1
      Handler_delete 119
      Handler_read_first 1493
      Handler_read_key 2675458
      Handler_read_next 64982270
      Handler_read_prev 0
      Handler_read_rnd 327571
      Handler_read_rnd_next 21850579
      Handler_update 1115
      Handler_write 75286
      Key_blocks_used 24520
      Key_read_requests 10103800
      Key_reads 24453
      Key_write_requests 4673
      Key_writes 4265
      Max_used_connections 41
      Not_flushed_key_blocks 0
      Not_flushed_delayed_rows 0
      Open_tables 84
      Open_files 157
      Open_streams 0
      Opened_tables 90
      Questions 71137
      Select_full_join 0
      Select_full_range_join 0
      Select_range 7667
      Select_range_check 0
      Select_scan 6864
      Slave_running OFF
      Slave_open_temp_tables 0
      Slow_launch_threads 0
      Slow_queries 6884
      Sort_merge_passes 0
      Sort_range 2868
      Sort_rows 327568
      Sort_scan 1872
      Table_locks_immediate 108415
      Table_locks_waited 18
      Threads_cached 0
      Threads_created 42
      Threads_connected 42
      Threads_running 1
      Uptime 49930



      Uptime is low, because the cornjob resstart mysqld every night.
      I'm thinking the other possible reason we have that many slow_queries is because our hard drive is not the DMA mode is not on after a reset.... but not 100% confirmed yet.

      Or maybe it was never on since day one...
      Quote:

      # /sbin/hdparm -d1 /dev/hdc

      /dev/hdc:
      setting using_dma to 1 (on)
      HDIO_SET_DMA failed: Operation not permitted
      using_dma = 0 (off)

      Comment


      • #4
        Your server has only run 71137 queries. It's pretty lightly loaded. However, a lot of the queries it's running are forcing full table scans because of lacking indexes. Select_range and Select_scan show that over 14,000 queries required table scans -- meaning part or all of the table had to be read to satisfy the query. This probably occured quickly when there wasn't much data, but as the tables got larger, this has taken more and more time.

        You need to look at the queries being run and figure out how to add the appropriate indexes. If you enable the MySQL slow log, it's a good place to start looking.

        Comment

        Working...
        X