Need help tuning MYSQL, please.

[gemino]

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:

[B]Quote:[/B]

# 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

[MarkRose]

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.

[gemino]

here’s my showstatus:

[B]Quote:[/B]

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…

[B]Quote:[/B]

# /sbin/hdparm -d1 /dev/hdc /dev/hdc: setting using_dma to 1 (on) HDIO_SET_DMA failed: Operation not permitted using_dma = 0 (off)

[MarkRose]

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.