PDA

View Full Version : MySQL Server Optimization Request

TechGuy
Mon 28th Feb '05, 7:58pm
1. Dedicated server running Mysqld only -- apache is on seperate servers.

2. Dual Xeon 2.4GHz, 4GB ECC RAM, SCSI 32GB, RedHat Linux 9, MySQL 12.22 Distrib 4.0.23

3. No Innodb databases. Should I consider otherwise?

4. RPM.

5. Top:
18:52:15 up 47 days, 3:13, 2 users, load average: 0.39, 0.36, 0.26
75 processes: 72 sleeping, 3 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user 2.0% system 0.0% nice 0.0% iowait 97.0% idle
CPU1 states: 0.0% user 2.0% system 0.0% nice 0.0% iowait 97.0% idle
CPU2 states: 2.0% user 8.0% system 0.0% nice 0.0% iowait 89.0% idle
CPU3 states: 6.0% user 6.0% system 0.0% nice 0.0% iowait 87.0% idle
Mem: 3870624k av, 3859136k used, 11488k free, 0k shrd, 57128k buff
2912004k actv, 8760k in_d, 154468k in_c
Swap: 8193140k av, 37472k used, 8155668k free 3146580k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
1584 mysql 16 0 497M 488M 2096 S 10.4 12.9 0:00 3 mysqld
1582 mysql 15 0 497M 488M 2096 R 6.2 12.9 0:00 2 mysqld
29494 mysql 15 0 497M 488M 2096 S 4.1 12.9 0:04 2 mysqld
1418 mysql 15 0 497M 488M 2096 S 2.0 12.9 0:23 3 mysqld
1 root 15 0 108 76 56 S 0.0 0.0 0:48 0 init
2 root RT 0 0 0 0 SW 0.0 0.0 0:00 0 migration/0
3 root RT 0 0 0 0 SW 0.0 0.0 0:00 1 migration/1
4 root RT 0 0 0 0 SW 0.0 0.0 0:00 2 migration/2
5 root RT 0 0 0 0 SW 0.0 0.0 0:00 3 migration/3
6 root 15 0 0 0 0 SW 0.0 0.0 0:21 3 keventd
7 root 34 19 0 0 0 SWN 0.0 0.0 0:07 0 ksoftirqd_CPU0
8 root 34 19 0 0 0 SWN 0.0 0.0 0:00 1 ksoftirqd_CPU1
9 root 34 19 0 0 0 SWN 0.0 0.0 0:00 2 ksoftirqd_CPU2
10 root 34 19 0 0 0 SWN 0.0 0.0 0:00 3 ksoftirqd_CPU3
15 root 15 0 0 0 0 SW 0.0 0.0 5:47 2 bdflush
11 root 15 0 0 0 0 SW 0.0 0.0 187:28 1 kswapd
12 root 15 0 0 0 0 SW 0.0 0.0 0:32 0 kscand/DMA
13 root 16 0 0 0 0 SW 0.0 0.0 100:25 0 kscand/Normal
14 root 16 0 0 0 0 SW 0.0 0.0 437:24 0 kscand/HighMem
16 root 15 0 0 0 0 SW 0.0 0.0 1:13 3 kupdated
17 root 15 0 0 0 0 SW 0.0 0.0 0:00 0 mdrecoveryd
23 root 25 0 0 0 0 SW 0.0 0.0 0:00 0 scsi_eh_0
27 root 19 0 0 0 0 SW 0.0 0.0 0:00 0 raid1d
28 root 15 0 0 0 0 SW 0.0 0.0 18:03 2 kjournald
723 root 15 0 0 0 0 SW 0.0 0.0 11:57 0 kjournald
724 root 15 0 0 0 0 SW 0.0 0.0 0:00 3 kjournald
725 root 15 0 0 0 0 SW 0.0 0.0 8:42 3 kjournald
1022 root 15 0 332 324 248 S 0.0 0.0 10:30 2 syslogd
1026 root 15 0 172 160 120 S 0.0 0.0 0:00 0 klogd
1044 rpc 15 0 380 364 292 S 0.0 0.0 0:00 1 portmap





6. MySQL variables:

+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set | latin1 |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| convert_character_set | |
| datadir | /var/lib/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 4 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
| init_file | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | ./ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_table_locks | ON |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 402653184 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 8387584 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 500 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_insert_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 268435456 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_repair_threads | 1 |
| myisam_recover_options | OFF |
| myisam_sort_buffer_size | 67108864 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| open_files_limit | 2510 |
| pid_file | /var/lib/mysql/db.techguy.org.pid |
| log_error | |
| port | 3306 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 2093056 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| rpl_recovery_rank | 0 |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | 0 |
| table_cache | 512 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 126976 |
| tx_isolation | REPEATABLE-READ |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| version | 4.0.23-standard |
| version_comment | Official MySQL RPM |
| version_compile_os | pc-linux |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



7. MySQL Extended-Status:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 5 |
| Bytes_received | 1059740980 |
| Bytes_sent | 394550787 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 220378 |
| 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 | 8434 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 2 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 133519 |
| Com_insert_select | 597 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 2 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 10275 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 819380 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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 | 11 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 4 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 2 |
| Com_update | 498198 |
| Connections | 368133 |
| Created_tmp_disk_tables | 1282 |
| Created_tmp_tables | 22169 |
| Created_tmp_files | 37 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 154 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 88842 |
| Handler_read_first | 81848 |
| Handler_read_key | 199792744 |
| Handler_read_next | 397320658 |
| Handler_read_prev | 931379 |
| Handler_read_rnd | 86843873 |
| Handler_read_rnd_next | 389981107 |
| Handler_rollback | 0 |
| Handler_update | 1150326 |
| Handler_write | 18137355 |
| Key_blocks_used | 375052 |
| Key_read_requests | 531994736 |
| Key_reads | 422620 |
| Key_write_requests | 806857 |
| Key_writes | 288389 |
| Max_used_connections | 124 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 512 |
| Open_files | 572 |
| Open_streams | 0 |
| Opened_tables | 921 |
| Questions | 3541094 |
| Qcache_queries_in_cache | 5837 |
| Qcache_inserts | 816844 |
| Qcache_hits | 1482202 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2535 |
| Qcache_free_memory | 18884112 |
| Qcache_free_blocks | 2941 |
| Qcache_total_blocks | 14935 |
| Rpl_status | NULL |
| Select_full_join | 213 |
| Select_full_range_join | 0 |
| Select_range | 163216 |
| Select_range_check | 0 |
| Select_scan | 135875 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 129 |
| Sort_merge_passes | 17 |
| Sort_range | 240803 |
| Sort_rows | 232526448 |
| Sort_scan | 53535 |
| Table_locks_immediate | 2676078 |
| Table_locks_waited | 6311 |
| Threads_cached | 7 |
| Threads_created | 3901 |
| Threads_connected | 7 |
| Threads_running | 1 |
| Uptime | 78445 |
+--------------------------+------------+




8. Just MySQL running vBulletin and phpAdsNew

9. Cookie timeout is 1800, average 1500 max 2500 users.

10 & 11. PHP doesn't run on this server, I'll post the web servers seperately later.

12. Just upgraded to 3.0.7 last night. :)


THANKS!
eva2000
Wed 2nd Mar '05, 11:34am
change or create a /etc/my.cnf file and use below settings and restart mysql server afterwards


[mysqld]
skip-innodb
max_connections = 900
key_buffer = 96M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 3600
connect_timeout = 10
tmp_table_size = 128M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 3M
query_cache_size = 160M
query_cache_type = 1

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
TechGuy
Wed 2nd Mar '05, 11:59pm
I'll give it a try. Thanks!
f0urtyfive
Thu 3rd Mar '05, 1:31am
Hey Eva, I've been reading a book on MySQL optimizations, and they say that if you can you shoudl set key_buffer_size to so that all your indices can fit into memory... (The size of all your MYI files). What do you think about this?
I've come up with this command to figure out what to set key_buffer_size to.

du -cH /var/lib/mysql/*/*.MYI

of course if your datadir is other than /var/lib/mysql change that part.
the size should come out as the last entry (The line that sais total on it.)
TechGuy
Thu 3rd Mar '05, 1:34am
For what it's worth, that command gives me a grand total of 884MB. ;)
f0urtyfive
Fri 4th Mar '05, 10:53am
Geeze what are you storing in your DB? The most I've seen is around 100-150 MB and thats on million post boards.
eva2000
Fri 4th Mar '05, 11:53am
Hey Eva, I've been reading a book on MySQL optimizations, and they say that if you can you shoudl set key_buffer_size to so that all your indices can fit into memory... (The size of all your MYI files). What do you think about this?
I've come up with this command to figure out what to set key_buffer_size to.

du -cH /var/lib/mysql/*/*.MYI

of course if your datadir is other than /var/lib/mysql change that part.
the size should come out as the last entry (The line that sais total on it.)
In theory that's nice but 99.99999999999% of vB servers don't have enough memory to do that. For your size forums and other forums I have dealt with of same or larger size, with 4GB ram, key_buffer of 64-96MB is about right, any higher and most of the times with the amount of mysql concurrent connections used, you'll swap to disk - yes even with 4GB ram