Mysql with bad performance and high cpu load tuning - cs-cart linux

Question

I have a cs-cart installation based in Mysql (MyIsam) with about 3,000 items and a database size of 700MB and index size of 247MB. In the last month for a reason i don't know the performances falled down and are very inconstant. I optimized the DB as I usually do and tried to change some Mysql variables according with some suggestion I found over the internet.

The dedicated server is quite old Cpu dual core pentium E2180 6GB RAM 160GB SATA

and this is the screenshot of the top command

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_logdir 



bdb_max_lock 

10000 


bdb_shared_data 

OFF 


bdb_tmpdir 

/tmp/ 


binlog_cache_size 

32768 


bulk_insert_buffer_size 

8388608 


character_set_client 

utf8 


character_set_connection 

utf8 


character_set_database 

latin1 


character_set_filesystem 

binary 


character_set_results 

utf8 


character_set_server 

latin1 


character_set_system 

utf8 


character_sets_dir 

/usr/share/mysql/charsets/ 


collation_connection 

utf8_general_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_community_features 

YES 


have_profiling 

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 

ltfs676.aus.us.siteprotect.com 


init_connect 



init_file 



init_slave 



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_data_home_dir 



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_flush_method 



innodb_force_recovery 

0 


innodb_lock_wait_timeout 

50 


innodb_locks_unsafe_for_binlog 

OFF 


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_max_dirty_pages_pct 

90 



Variable_name  


Value  


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 


innodb_use_legacy_cardinality_algorithm 

ON 


interactive_timeout 

28800 


join_buffer_size 

131072 


key_buffer_size 

367001600 


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 

OFF 


locked_in_memory 

OFF 


log 

OFF 


log_bin 

OFF 


log_bin_trust_function_creators 

OFF 


log_error 

/var/log/mysqld.log 


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 

4294963200 


max_binlog_size 

1073741824 


max_connect_errors 

10 


max_connections 

100 


max_delayed_threads 

20 


max_error_count 

64 


max_heap_table_size 

16777216 


max_insert_delayed_threads 

20 


max_join_size 

4294967295 


max_length_for_sort_data 

1024 


max_prepared_stmt_count 

16382 


max_relay_log_size 

0 


max_seeks_for_key 

4294967295 


max_sort_length 

1024 


max_sp_recursion_depth 

0 


max_tmp_tables 

32 


max_user_connections 

50 


max_write_lock_count 

4294967295 


multi_range_count 

256 


myisam_data_pointer_size 

6 


myisam_max_sort_file_size 

2146435072 


myisam_mmap_size 

4294967295 


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 
OFF 


open_files_limit 
20000 


optimizer_prune_level 
1 


optimizer_search_depth 
62 


pid_file 
/var/run/mysqld/mysqld.pid 
plugin_dir 



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 
relay_log_index 
relay_log_info_file 
relay-log.info 


relay_log_purge 
ON 


relay_log_space_limit 

0 


rpl_recovery_rank 

0 


secure_auth 

OFF 


secure_file_priv 



server_id 
0 


skip_external_locking 
ON 



Variable_name  
Value  


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 
2097144 


sql_big_selects 
ON 


sql_mode 

sql_notes 
ON 


sql_warnings 
OFF 

ssl_ca 
ssl_capath 
ssl_cert 
ssl_cipher 
ssl_key 



storage_engine 
MyISAM 


sync_binlog 
0 


sync_frm 
ON 


system_time_zone 
CDT 


table_cache 
64 


table_lock_wait_timeout 
50 


table_type 
MyISAM 


thread_cache_size 
0 


thread_stack 
196608 


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.95 


version_bdb 
Sleepycat Software: Berkeley DB 4.1.24: (December ... 


version_comment 
Source distribution 


version_compile_machine 
i386 


version_compile_os 
redhat-linux-gnu 

wait_timeout 
28800 

Can anybody please help me tuning the system?

Answer 1

According to the status dump, your innodb_buffer_pool_size is the default 8mb (ie - tiny compared to the size of the database) and the MyIsam key_buffer_size is 350mb. Your machine also has 3GB of ram or so. A good chunk of it isn't being used - or is used by the OS for cache (which is where database may be - but it's not being actively controlled by Mysql, hence is slower).

I'd imagine that all the tables in the main database are set up as MyIsam tables. My first action if I were to go to optimise the server would be to increase the innodb_buffer_pool_size to around 1GB (a little more than the database size) and then convert all the databases to InnoDB. This brings the entire database into memory, under control of the database server.

Just changing those when you don't know what you are doing though, would not be be a good idea - but may give you enough thoughts to properly investigate Mysql server optimisations.