New Install Optimising MYSQL

My server died so got a new one and have installed Virtualmin GPL via the file on Centos 5.5. Everything is up to date according to Virtualmin and yum, running pretty much default settings (servers been online less than 24 hours).

I’m using virtualserver backups to restore my site (about 100) which get around 40,000 unique visitors a day. All domains (nameservers) are pointing at this new server, but not all are restored yet (going to be getting quite a bit of sites not found until I’ve finished).

Even when I’m not restoring servers I’m seeing very high CPU usage relative to the old server, this is causing the server to run sluggishly.

CPU load averages : above 10 most of the time and gets as high as 20. When I’ve seen CPU usage this high my last server would crash. Had this server crash once today restoring a large backup.

Looking through processes I see one like this pretty much every time:

/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/ --skip-external-locking --socket=/var/lib/mysql/mysql.sock

CPU 28.8 %

The CPU usage for MYSQL is consistently around 30%. I don’t recal it being that high with other servers, same sites.

I’m trying to optimise the MYSQL configuration, but it’s very confusing.

What I’ve changed from defaults so far is:

Key buffer size : 64mb
Sort buffer size : 4mb
Tables to cache : 256
Maximum number of connections : 500

Made no difference.

The server is a

Intel® Xeon® CPU E5506 @ 2.13GHz , 4 cores

It’s not a budget server.

Appreciate there is no fit all MYSQL configuration, when I’ve optimised in the past it’s been a case of Google searches and guess work and I’m never confident it’s configured well!

Most of my sites run WordPress so lots of database connections, though 40,000 visitors a day on a dedicated server isn’t that much.

Looking for suggestions on configurations to teat?

Below is all my Mysql settings:

auto_increment_increment 1

auto_increment_offset 	1

automatic_sp_privileges 	ON
back_log 	50
basedir 	/usr/
bdb_cache_size 	8384512

bdb_home 	/var/lib/mysql/
bdb_log_buffer_size 	262144

bdb_max_lock 	10000

bdb_shared_data 	OFF

bdb_tmpdir 	/tmp/
binlog_cache_size 	32768
bulk_insert_buffer_size 	8388608

character_set_client 	latin1

character_set_connection 	latin1

character_set_database 	latin1

character_set_filesystem 	binary

character_set_results 	latin1

character_set_server 	latin1

character_set_system 	utf8

character_sets_dir 	/usr/share/mysql/charsets/

collation_connection 	latin1_swedish_ci

collation_database 	latin1_swedish_ci

collation_server 	latin1_swedish_ci

completion_type 	0

concurrent_insert 	1
connect_timeout 	10
datadir 	/var/lib/mysql/
date_format 	%Y-%m-%d
datetime_format 	%Y-%m-%d %H:%i:%s
default_week_format 	0

delay_key_write 	ON
delayed_insert_limit 	100
delayed_insert_timeout 	300
delayed_queue_size 	1000
div_precision_increment 	4
keep_files_on_create 	OFF

engine_condition_pushdown 	OFF
expire_logs_days 	0

flush 	OFF
flush_time 	0

ft_boolean_syntax 	+ -><()~*:""&|
ft_max_word_len 	84
ft_min_word_len 	4
ft_query_expansion_limit 	20
ft_stopword_file 	(built-in)
group_concat_max_len 	1024

have_archive 	NO

have_bdb 	YES

have_blackhole_engine 	NO

have_compress 	YES

have_crypt 	YES

have_csv 	NO

have_dynamic_loading 	YES

have_example_engine 	NO

have_federated_engine 	NO

have_geometry 	YES

have_innodb 	YES

have_isam 	NO

have_merge_engine 	YES

have_ndbcluster 	NO

have_openssl 	DISABLED

have_ssl 	DISABLED

have_query_cache 	YES

have_raid 	NO

have_rtree_keys 	YES

have_symlink 	YES

hostname 	Ess#####.localdomain



innodb_additional_mem_pool_size 	1048576
innodb_autoextend_increment 	8
innodb_buffer_pool_awe_mem_mb 	0
innodb_buffer_pool_size 	8388608
innodb_checksums 	ON
innodb_commit_concurrency 	0
innodb_concurrency_tickets 	500

innodb_data_file_path 	ibdata1:10M:autoextend
innodb_adaptive_hash_index 	ON
innodb_doublewrite 	ON
innodb_fast_shutdown 	1
innodb_file_io_threads 	4
innodb_file_per_table 	OFF
innodb_flush_log_at_trx_commit 	1
innodb_force_recovery 	0
innodb_lock_wait_timeout 	50
innodb_locks_unsafe_for_binlog 	OFF

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_max_dirty_pages_pct 	90
innodb_max_purge_lag 	0
innodb_mirrored_log_groups 	1
innodb_open_files 	300
innodb_rollback_on_timeout 	OFF
innodb_support_xa 	ON
innodb_sync_spin_loops 	20
innodb_table_locks 	ON
innodb_thread_concurrency 	8
innodb_thread_sleep_delay 	10000
interactive_timeout 	28800
join_buffer_size 	131072
key_buffer_size 	67108864
key_cache_age_threshold 	300
key_cache_block_size 	1024
key_cache_division_limit 	100
language 	/usr/share/mysql/english/

large_files_support 	ON

large_page_size 	0

large_pages 	OFF

lc_time_names 	en_US

license 	GPL

local_infile 	ON

locked_in_memory 	OFF
log 	OFF

log_bin 	OFF

log_bin_trust_function_creators 	OFF


log_queries_not_using_indexes 	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 	1048576
max_binlog_cache_size 	18446744073709547520
max_binlog_size 	1073741824
max_connect_errors 	10
max_connections 	500
max_delayed_threads 	20
max_error_count 	64
max_heap_table_size 	16777216

max_insert_delayed_threads 	20
max_join_size 	18446744073709551615
max_length_for_sort_data 	1024
max_prepared_stmt_count 	16382
max_relay_log_size 	0
max_seeks_for_key 	18446744073709551615
max_sort_length 	1024
max_sp_recursion_depth 	0
max_tmp_tables 	32
max_user_connections 	0
max_write_lock_count 	18446744073709551615
multi_range_count 	256
myisam_data_pointer_size 	6
myisam_max_sort_file_size 	9223372036853727232

myisam_recover_options 	OFF
myisam_repair_threads 	1
myisam_sort_buffer_size 	8388608
myisam_stats_method 	nulls_unequal
net_buffer_length 	16384
net_read_timeout 	30
net_retry_count 	10
net_write_timeout 	60
new 	OFF

old_passwords 	ON
open_files_limit 	2500
optimizer_prune_level 	1
optimizer_search_depth 	62

pid_file 	/var/run/mysqld/
port 	3306
preload_buffer_size 	32768

profiling 	OFF
profiling_history_size 	15

protocol_version 	10
query_alloc_block_size 	8192
query_cache_limit 	1048576
query_cache_min_res_unit 	4096
query_cache_size 	0
query_cache_type 	ON
query_cache_wlock_invalidate 	OFF
query_prealloc_size 	8192
range_alloc_block_size 	4096
read_buffer_size 	131072
read_only 	OFF
read_rnd_buffer_size 	262144



relay_log_purge 	ON
relay_log_space_limit 	0

rpl_recovery_rank 	0

secure_auth 	OFF


server_id 	0

skip_external_locking 	ON

skip_networking 	OFF

skip_show_database 	OFF
slave_compressed_protocol 	OFF

slave_load_tmpdir 	/tmp/
slave_net_timeout 	3600

slave_skip_errors 	OFF
slave_transaction_retries 	10
slow_launch_time 	2
socket 	/var/lib/mysql/mysql.sock
sort_buffer_size 	4194304

sql_big_selects 	ON


sql_notes 	ON

sql_warnings 	OFF






storage_engine 	MyISAM

sync_binlog 	0

sync_frm 	ON

system_time_zone 	PST
table_cache 	256
table_lock_wait_timeout 	50

table_type 	MyISAM
thread_cache_size 	0
thread_stack 	262144
time_format 	%H:%i:%s

time_zone 	SYSTEM
timed_mutexes 	OFF
tmp_table_size 	33554432
tmpdir 	/tmp/
transaction_alloc_block_size 	8192
transaction_prealloc_size 	4096

tx_isolation 	REPEATABLE-READ
updatable_views_with_limit 	YES

version 	5.0.77

version_bdb 	Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009)

version_comment 	Source distribution

version_compile_machine 	x86_64

version_compile_os 	redhat-linux-gnu
wait_timeout 	28800




Well, one of the things you’ll want to figure out is what’s not working correctly, or why it’s using more resources.

MySQL can work pretty well out of the box; but if your desire is to change some settings, you’d really want to have a solid understanding of why you’re changing it to avoid making things worse :slight_smile:

I think the first thing I’d do install install the “mytop” program, which will give you some insight into what’s going on. You should see all the queries, along with who’s running them – which can lead to a better understanding of what’s not working right.