性能差和cpu负载调优的Mysql - cs-cart linux

Question

我有一个基于Mysql (MyIsam)的cs-购物车安装，大约有3,000个条目，数据库大小为700 of，索引大小为247 of。在过去的一个月里，由于某种原因，我不知道演出失败了，而且非常不稳定。我像往常一样对DB进行了优化，并试图根据我在互联网上发现的一些建议更改一些Mysql变量。

专用服务器是相当老的Cpu双核pentium E2180 6GB内存160 6GB。

这是顶层命令的屏幕截图

​

​

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 

有人能帮我调一下系统吗？

Answer 1

根据状态转储，您的innodb_buffer_pool_size是默认的8 8mb(即与数据库大小相比很小)，MyIsam key_buffer_size是350 8mb。你的机器也有3GB左右的内存。其中很大一部分没有被使用--或者被操作系统用于缓存(这可能是数据库所在的地方--但它没有被Mysql主动控制，因此速度更慢)。

我可以想象主数据库中的所有表都被设置为MyIsam表。如果要优化服务器，我的第一个操作是将innodb_buffer_pool_size提高到大约1GB (略高于数据库大小)，然后将所有数据库转换为InnoDB。这使整个数据库进入内存，在数据库服务器的控制下。

当你不知道你在做什么的时候，仅仅改变这些并不是一个好主意--但是可能会给你足够的想法来正确地研究Mysql服务器的优化。

Answer 2

你使用什么CS-购物车版本？试着升级到4.3.3 1。CS-Cart团队在优化核心和加快页面加载速度(特别是带有过滤器的页面)方面做了很好的工作。