perfome problemmer
Hejsidder med en mysql jeg overhoved ikke kan få til af perfome. og kan ikke lige se hvad der mangler
den kører kun 1DB "fylder 273gb"
dben kører innoDB.
sysinfo
server:
2 X Xenon E5520 @ 2.27Ghz
ram 20GB
OS disk flashdisk 35 gb
SAN 1TB flashdiske "Raid 5"
SAN til server 2x 1Gbit netkort. med loadbalance
OS system Open Suse
ISCSI til SAN
på 1 dag bliver der ca smidt 40gb igemmen den
der er I/Oér nok
men tror af det er setup af mysqld der har problemet
så hvis nogle kan se om den er forkert . muligvis smide et setup
der passer
smider lidt stats osse.
min my. my.cnf
[client]
port = 3306
socket = /var/run/mysql/mysql.sock
[mysqld]
port = 3306
bind-address = 0.0.0.0
socket = /var/run/mysql/mysql.sock
old_passwords=1
datadir = /home/mysql
max_connections = 2000
skip-locking
key_buffer_size = 36M
max_allowed_packet = 11G
table_open_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 256M
tmp_table_size = 580M
max_allowed_packet = 11G
table_open_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
query_cache_size = 256M
tmp_table_size = 580M
max_heap_table_size = 650M
thread_cache_size = 800
table_cache = 4000
#innodb_buffer_pool_size = 1000M
innodb_buffer_pool_size = 2000M
innodb_additional_mem_pool_size = 20M
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size = 10G
#TEST
innodb_support_xa=0
#innodb_log_file_size=500M
log = /var/log/mysql/mysqld.log
[safe_mysqld]
log-error = /var/log/mysql/mysqld.log
socket = /var/run/mysql/mysql.sock
[mysqldump]
socket = /var/run/mysql/mysql.sock
quick
max_allowed_packet = 100M
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysqld_multi.log
show status;
lidt af den . skal siges af den har pt kun være online 1 times
tid.
| Innodb_buffer_pool_pages_data | 104599 |
| Innodb_buffer_pool_pages_dirty | 5667 |
| Innodb_buffer_pool_pages_flushed | 134193 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 23401 |
| Innodb_buffer_pool_pages_total | 128000 |
| Innodb_buffer_pool_read_ahead_rnd | 12296 |
| Innodb_buffer_pool_read_ahead_seq | 5564 |
| Innodb_buffer_pool_read_requests | 3204952571 |
| Innodb_buffer_pool_reads | 475118 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 3437757 |
| Innodb_data_fsyncs | 8711 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 19398840320 |
| Innodb_data_reads | 589452 |
| Innodb_data_writes | 513651 |
| Innodb_data_written | 4691825152 |
| Innodb_dblwr_pages_written | 134193 |
| Innodb_dblwr_writes | 1830 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 180957 |
| Innodb_log_writes | 422221 |
| Innodb_os_log_fsyncs | 5047 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 294393856 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2221 |
| Innodb_pages_read | 1183880 |
| Innodb_pages_written | 134193 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 473 |
| Innodb_row_lock_time_avg | 3 |
| Innodb_row_lock_time_max | 155 |
| Innodb_row_lock_waits | 147 |
| Innodb_rows_deleted | 159 |
| Innodb_rows_inserted | 396703 |
| Innodb_rows_read | 1038819568 |
| Innodb_rows_updated | 20965 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 30199 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 425 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 22 |
| Open_streams | 0 |
| Open_table_definitions | 73 |
| Open_tables | 178 |
| Opened_files | 127 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
| Prepared_stmt_count | 4 |
| Qcache_free_blocks | 210 |
| Qcache_free_memory | 267488536 |
| Qcache_hits | 31527 |
| Qcache_inserts | 31697 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4028 |
| Qcache_queries_in_cache | 408 |
| Qcache_total_blocks | 1062 |
| Queries | 695370 |
| Questions | 2 |