Need help tuning MYSQL, please.

MySQL & MariaDB Other MySQL® Questions
1

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. :wink:
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

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.

3

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)

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.