The Planet Forums > Need mysql optimization!

clusterimmortal

Jul 19 2004, 09:35 PM

I'm on a dual xeon 2.4 with 2gb ram and 2 scsi 80gb hds and ht disabled!

Please, ANY help would be great!

Just running a mysql server and here's my loads at peak time

00:22:01 up 9:09, 1 user, load average: 32.27, 38.71, 30.05

Tue Jul 20 00:31:41 CDT 2004

00:32:25 up 9:19, 1 user, load average: 51.95, 41.51, 33.91
274 processes: 242 sleeping, 32 running, 0 zombie, 0 stopped
total 75.0% 0.0% 25.0% 0.0% 0.0% 0.0% 0.0%
cpu00 76.1% 0.0% 23.8% 0.0% 0.0% 0.0% 0.0%
cpu01 73.8% 0.0% 26.1% 0.0% 0.0% 0.0% 0.0%
Mem: 2061576k av, 287456k used, 1774120k free, 0k shrd, 42516k buff
189848k active, 44380k inactive
Swap: 2048276k av, 0k used, 2048276k free 145520k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
3990 mysql 16 0 31672 30M 1348 R 19.6 1.5 0:08 0 mysqld
4027 mysql 15 0 37024 36M 1348 R 15.6 1.7 0:11 0 mysqld
4059 mysql 15 0 35592 34M 1348 R 15.6 1.7 0:07 0 mysqld
3952 mysql 15 0 31672 30M 1348 R 13.9 1.5 0:08 0 mysqld
3994 mysql 16 0 31672 30M 1348 R 13.9 1.5 0:09 0 mysqld
3936 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:07 0 mysqld
3943 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:09 0 mysqld
4008 mysql 15 0 31672 30M 1348 R 13.3 1.5 0:08 0 mysqld
4025 mysql 15 0 32948 32M 1348 R 13.3 1.5 0:07 0 mysqld
4096 mysql 15 0 35592 34M 1348 R 13.0 1.7 0:04 0 mysqld
3970 mysql 15 0 31672 30M 1348 R 12.5 1.5 0:10 0 mysqld
4051 mysql 15 0 35592 34M 1348 R 12.2 1.7 0:07 0 mysqld
4172 mysql 18 0 35592 34M 1348 R 12.2 1.7 0:00 0 mysqld
3974 mysql 15 0 31672 30M 1348 R 5.6 1.5 0:08 0 mysqld
4122 mysql 16 0 35592 34M 1348 R 5.6 1.7 0:00 0 mysqld
4175 mysql 18 0 35592 34M 1348 R 3.7 1.7 0:00 1 mysqld
4171 mysql 17 0 35592 34M 1348 R 2.2 1.7 0:00 0 mysqld
4067 mysql 16 0 35592 34M 1348 R 0.8 1.7 0:06 0 mysqld
3972 mysql 15 0 31672 30M 1348 R 0.2 1.5 0:07 0 mysqld
4109 mysql 15 0 35592 34M 1348 R 0.2 1.7 0:05 0 mysqld
3996 mysql 15 0 31672 30M 1348 R 0.0 1.5 0:09 0 mysqld
4041 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:06 0 mysqld
4056 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:06 0 mysqld
4092 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:02 0 mysqld
4116 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4163 mysql 15 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4181 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4182 mysql 17 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4183 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4184 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 0 mysqld
4185 mysql 20 0 35592 34M 1348 R 0.0 1.7 0:00 1 mysqld

Http processes currently running = 11
Mysql processes currently running = 222

Netstat information summary
3 FIN_WAIT2
19 LISTEN
218 ESTABLISHED

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 460 |
| Aborted_connects | 0 |
| Bytes_received | 1228740 |
| Bytes_sent | 9936311 |
| Com_admin_commands | 3252 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 4121 |
| 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 | 13 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 344 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 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_savepoint | 0 |
| Com_select | 12366 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 2 |
| Com_show_grants | 0 |
| Com_show_keys | 1 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 1696 |
| Connections | 474 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 12 |
| Handler_read_first | 1 |
| Handler_read_key | 2577 |
| Handler_read_next | 0 |
| Handler_read_prev | 5623714 |
| Handler_read_rnd | 37007 |
| Handler_read_rnd_next | 524266213 |
| Handler_rollback | 0 |
| Handler_update | 1621 |
| Handler_write | 344 |
| Key_blocks_used | 502 |
| Key_read_requests | 234824 |
| Key_reads | 498 |
| Key_write_requests | 364 |
| Key_writes | 340 |
| Max_used_connections | 217 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 369 | 72% of table_cache in use
| Open_files | 379 |
| Open_streams | 0 |
| Opened_tables | 375 |
| Questions | 19566 |
| Qcache_queries_in_cache | 328 |
| Qcache_inserts | 10388 |
| Qcache_hits | 987 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1975 |
| Qcache_free_memory | 33117768 |
| Qcache_free_blocks | 38 |
| Qcache_total_blocks | 624 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 9806 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 202 | (execution time > 10 secs)
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 37007 |
| Sort_scan | 2756 |
| Table_locks_immediate | 7583 |
| Table_locks_waited | 6812 |
| Threads_cached | 0 |
| Threads_created | 218 |
| Threads_connected | 218 |
| Threads_running | 115 |
| Uptime | 654 | 10 mins 54 secs
+--------------------------+-----------+

Key Reads/Key Read Requests = 0.002121 (Cache hit = 99.997879%)
Key Writes/Key Write Requests = 0.934066
Connections/second = 0.725 (/hour = 2609.174)
KB received/second = 1.833 (/hour = 6600.000)
KB sent/second = 14.836 (/hour = 53411.009)
Temporary Tables Created/second = 0.000 (/hour = 0.000)
Opened Tables/second = 0.573 (/hour = 2064.220)
Slow Queries/second = 0.309 (/hour = 1111.927)
% of slow queries = 1.032%
Queries/second = 29.917 (/hour = 107702.752)

[/CODE]

clusterimmortal

Jul 19 2004, 09:38 PM

Here's my.cnf

CODE

[mysqld]

max_connections = 768

interactive_timeout=100

key_buffer = 384M

myisam_sort_buffer_size = 128M

join_buffer_size = 2M

read_buffer_size = 2M

sort_buffer_size = 2M

table_cache = 512

record_buffer = 2M

thread_cache_size = 128

wait_timeout = 100

tmp_table_size= 8M

connect_timeout = 10

max_allowed_packet = 1M

max_connect_errors = 10

query_cache_limit = 1M

query_cache_size = 32M

query_cache_type = 1

skip-innodb

skip-locking

thread_cache_size=128

thread_concurrency=4

[mysqld_safe]

open_files_limit = 8192

[mysqldump]

quick

max_allowed_packet = 16M

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

[myisamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 16M

write_buffer = 16M

Stat Script :

[CODE]

art

Jul 20 2004, 06:26 AM

Qcache_free_memory | 33117768 |
Your can set lower value in query_cache_size = 32M

Table_locks_waited | 6812 |
I think this is a main problem. Too much queries wait while tables are locked.

Slow_queries | 202 |
You can set slow query log and find exactly who is the pig. Then try to optimize the base, like indexing some fields, or change his types, or rewrite queries or algorithm in scripts.

And I think you set too much values by hand. Try to use default my.cnf and see

My my.cnf for example

CODE

[client]

port = 3306

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

[mysqld]

port = 3306

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

skip-locking

set-variable = key_buffer=128M

set-variable = table_cache=128

set-variable = thread_cache=16

set-variable = thread_concurrency=4

datadir = /var/lib/mysql

skip-innodb

set-variable = max_connections=300

[mysql.server]

user = mysql

basedir = /var/lib

[safe_mysqld]

err-log = /var/log/mysqld.log

pid-file = /var/run/mysqld/mysqld.pid

and one little trick: set up noatime attr on database dir
cd /var/lib
chattr -R +A mysql

RexAdmin

Jul 20 2004, 04:13 PM

- Upgrade to latest MySQL 4.0.XX
- Try to enable INNODB and restart MySQL
- Optimize database's
- Locate what script is consuming most of MySQL
...

clusterimmortal

Jul 21 2004, 12:33 PM

Thank you for the replys, just optimized my databases and the load now is :

15:32:25 up 2 days, 19 min, 1 user, load average: 1.84, 0.77, 0.33

Powerlord

Jul 23 2004, 10:42 PM

QUOTE

Originally posted by RexAdmin
- Upgrade to latest MySQL 4.0.XX
- Try to enable INNODB and restart MySQL
- Optimize database's
- Locate what script is consuming most of MySQL
...

Last time I checked, INNODB takes more resources than MyISAM, not less...

RexAdmin

Jul 26 2004, 09:16 AM

QUOTE

Originally posted by Powerlord
Last time I checked, INNODB takes more resources than MyISAM, not less...

INNODB will allow you to use tables which are under lock/write status.

So, you will not have accumulating process = high load on server, that waits for table to be unlocked.

Yes, more resources (especailly in starting/stoping) but on the long run you will have DB which is faster.