论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2008-08-14 17:24 |只看该作者 |倒序浏览

机器4G内存，查询innodb表的时候数度奇慢，大家帮看看啊？
下面是系统变量和并发一个用户时候的系统状态，并发一个用户cpu使用就到 50%
mysql> show variables;
+---------------------------------+---------------------------------------------+
| Variable_name                | Value                                     |
+---------------------------------+---------------------------------------------+
| auto_increment_increment       | 1                                        |
| auto_increment_offset          | 1                                        |
| automatic_sp_privileges       | ON                                        |
| back_log                      | 200                                        |
| basedir                      | /system/mysql/                            |
| binlog_cache_size             | 32768                                     |
| bulk_insert_buffer_size       | 2097152                                  |
| character_set_client          | utf8                                     |
| character_set_connection       | utf8                                     |
| character_set_database       | utf8                                     |
| character_set_filesystem       | binary                                     |
| character_set_results          | utf8                                     |
| character_set_server          | utf8                                     |
| character_set_system          | utf8                                     |
| character_sets_dir             | /system/mysql/share/mysql/charsets/       |
| collation_connection          | utf8_general_ci                            |
| collation_database             | utf8_general_ci                            |
| collation_server             | utf8_general_ci                            |
| completion_type                | 0                                        |
| concurrent_insert             | 1                                        |
| connect_timeout                | 5                                        |
| datadir                      | /mysql/data/                               |
| 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                                        |
| 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                      | NO                                        |
| have_blackhole_engine          | NO                                        |
| have_compress                | YES                                        |
| have_crypt                   | YES                                        |
| have_csv                      | NO                                        |
| have_dynamic_loading          | NO                                        |
| 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                   | NO                                        |
| have_ssl                      | NO                                        |
| have_query_cache             | YES                                        |
| have_raid                      | NO                                        |
| have_rtree_keys                | YES                                        |
| have_symlink                   | YES                                        |
| hostname                      | localhost.localdomain                      |
| init_connect                   |                                           |
| init_file                      |                                           |
| init_slave                   |                                           |
| innodb_additional_mem_pool_size | 8388608                                  |
| innodb_autoextend_increment    | 8                                        |
| innodb_buffer_pool_awe_mem_mb | 0                                        |
| innodb_buffer_pool_size       | 536870912                                  |
| innodb_checksums             | ON                                        |
| innodb_commit_concurrency    | 0                                        |
| innodb_concurrency_tickets    | 500                                        |
| innodb_data_file_path          | ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend |
| innodb_data_home_dir          | /mysql/data                               |
| innodb_doublewrite             | ON                                        |
| innodb_fast_shutdown          | 1                                        |
| innodb_file_io_threads       | 4                                        |
| innodb_file_per_table          | ON                                        |
| innodb_flush_log_at_trx_commit  | 2                                        |
| innodb_flush_method          | fdatasync                                  |
| innodb_force_recovery          | 0                                        |
| innodb_lock_wait_timeout       | 30                                        |
| innodb_locks_unsafe_for_binlog  | OFF                                        |
| innodb_log_arch_dir          |                                           |
| innodb_log_archive             | OFF                                        |
| innodb_log_buffer_size       | 8388608                                  |
| innodb_log_file_size          | 4194304                                  |
| innodb_log_files_in_group    | 2                                        |
| innodb_log_group_home_dir    | /mysql/log                               |
| 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                | 1073741824                               |
| key_cache_age_threshold       | 300                                        |
| key_cache_block_size          | 1024                                     |
| key_cache_division_limit       | 100                                        |
| language                      | /system/mysql/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                            | ON                                        |
| log_bin                      | ON                                        |
| log_bin_trust_function_creators | OFF                                        |
| log_error                      | /mysql/log/billingcenter01.err             |
| log_queries_not_using_indexes | OFF                                        |
| log_slave_updates             | OFF                                        |
| log_slow_queries             | ON                                        |
| log_warnings                   | 1                                        |
| long_query_time                | 1                                        |
| low_priority_updates          | OFF                                        |
| lower_case_file_system       | OFF                                        |
| lower_case_table_names       | 0                                        |
| max_allowed_packet             | 16776192                                  |
| max_binlog_cache_size          | 4294967295                               |
| 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                | 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          | 0                                        |
| max_write_lock_count          | 4294967295                               |
| multi_range_count             | 256                                        |
| myisam_data_pointer_size       | 6                                        |
| myisam_max_sort_file_size    | 2147483647                               |
| myisam_recover_options       | backup                                     |
| myisam_repair_threads          | 1                                        |
| myisam_sort_buffer_size       | 16777216                                  |
| myisam_stats_method          | nulls_unequal                            |
| net_buffer_length             | 8192                                     |
| net_read_timeout             | 30                                        |
| net_retry_count                | 10                                        |
| net_write_timeout             | 60                                        |
| new                            | OFF                                        |
| old_passwords                | OFF                                        |
| open_files_limit             | 2500                                     |
| optimizer_prune_level          | 1                                        |
| optimizer_search_depth       | 62                                        |
| pid_file                      | /mysql/data/billingcenter01.pid          |
| 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             | 67108864                                  |
| query_cache_type             | ON                                        |
| query_cache_wlock_invalidate | OFF                                        |
| query_prealloc_size          | 8192                                     |
| range_alloc_block_size       | 2048                                     |
| read_buffer_size             | 1044480                                  |
| read_only                      | OFF                                        |
| read_rnd_buffer_size          | 1044480                                  |
| relay_log_purge                | ON                                        |
| relay_log_space_limit          | 0                                        |
| rpl_recovery_rank             | 0                                        |
| secure_auth                   | OFF                                        |
| secure_file_priv             |                                           |
| server_id                      | 1                                        |
| 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                         | /tmp/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                | InnoDB                                     |
| sync_binlog                   | 0                                        |
| sync_frm                      | ON                                        |
| system_time_zone             | CST                                        |
| table_cache                   | 800                                        |
| table_lock_wait_timeout       | 50                                        |
| table_type                   | InnoDB                                     |
| thread_cache_size             | 80                                        |
| 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.45-log                               |
| version_comment                | Source distribution                      |
| version_compile_machine       | i686                                     |
| version_compile_os             | pc-linux-gnu                               |
| wait_timeout                   | 28800                                     |
+---------------------------------+---------------------------------------------+
225 rows in set (0.00 sec)

文库|博客

smz0102

稍有积蓄

论坛徽章:: 0

2楼 [报告]

发表于 2008-08-14 17:25 |只看该作者

mysql> show status;
+-----------------------------------+-----------+
| Variable_name                   | Value    |
+-----------------------------------+-----------+
| Aborted_clients                | 222    |
| Aborted_connects                | 0       |
| Binlog_cache_disk_use          | 0       |
| Binlog_cache_use                | 0       |
| Bytes_received                   | 166    |
| Bytes_sent                      | 17870    |
| Com_admin_commands             | 0       |
| Com_alter_db                   | 0       |
| Com_alter_table                | 0       |
| Com_analyze                      | 0       |
| Com_backup_table                | 0       |
| Com_begin                      | 0       |
| Com_call_procedure             | 0       |
| Com_change_db                   | 0       |
| Com_change_master                | 0       |
| Com_check                      | 0       |
| Com_checksum                   | 0       |
| Com_commit                      | 0       |
| Com_create_db                   | 0       |
| Com_create_function             | 0       |
| Com_create_index                | 0       |
| Com_create_table                | 0       |
| Com_create_user                | 0       |
| Com_dealloc_sql                | 0       |
| Com_delete                      | 0       |
| Com_delete_multi                | 0       |
| Com_do                         | 0       |
| Com_drop_db                      | 0       |
| Com_drop_function                | 0       |
| Com_drop_index                   | 0       |
| Com_drop_table                   | 0       |
| Com_drop_user                   | 0       |
| Com_execute_sql                | 0       |
| Com_flush                      | 0       |
| Com_grant                      | 0       |
| Com_ha_close                   | 0       |
| Com_ha_open                      | 0       |
| Com_ha_read                      | 0       |
| Com_help                         | 0       |
| Com_insert                      | 0       |
| Com_insert_select                | 0       |
| Com_kill                         | 0       |
| Com_load                         | 0       |
| Com_load_master_data             | 0       |
| Com_load_master_table          | 0       |
| Com_lock_tables                | 0       |
| Com_optimize                   | 0       |
| Com_preload_keys                | 0       |
| Com_prepare_sql                | 0       |
| Com_purge                      | 0       |
| Com_purge_before_date          | 0       |
| Com_rename_table                | 0       |
| Com_repair                      | 0       |
| Com_replace                      | 0       |
| Com_replace_select             | 0       |
| Com_reset                      | 0       |
| Com_restore_table                | 0       |
| Com_revoke                      | 0       |
| Com_revoke_all                   | 0       |
| Com_rollback                   | 0       |
| Com_savepoint                   | 0       |
| Com_select                      | 1       |
| Com_set_option                   | 0       |
| Com_show_binlog_events          | 0       |
| Com_show_binlogs                | 0       |
| Com_show_charsets                | 0       |
| Com_show_collations             | 0       |
| Com_show_column_types          | 0       |
| Com_show_create_db             | 0       |
| Com_show_create_table          | 0       |
| Com_show_databases             | 0       |
| Com_show_errors                | 0       |
| Com_show_fields                | 0       |
| Com_show_grants                | 0       |
| Com_show_innodb_status          | 0       |
| Com_show_keys                   | 0       |
| Com_show_logs                   | 0       |
| Com_show_master_status          | 0       |
| Com_show_ndb_status             | 0       |
| Com_show_new_master             | 0       |
| Com_show_open_tables             | 0       |
| Com_show_privileges             | 0       |
| Com_show_processlist             | 0       |
| Com_show_slave_hosts             | 0       |
| Com_show_slave_status          | 0       |
| Com_show_status                | 3       |
| Com_show_storage_engines       | 0       |
| Com_show_tables                | 0       |
| Com_show_triggers                | 0       |
| Com_show_variables             | 1       |
| Com_show_warnings                | 0       |
| Com_slave_start                | 0       |
| Com_slave_stop                   | 0       |
| Com_stmt_close                   | 0       |
| Com_stmt_execute                | 0       |
| Com_stmt_fetch                   | 0       |
| Com_stmt_prepare                | 0       |
| Com_stmt_reset                   | 0       |
| Com_stmt_send_long_data          | 0       |
| Com_truncate                   | 0       |
| Com_unlock_tables                | 0       |
| Com_update                      | 0       |
| Com_update_multi                | 0       |
| Com_xa_commit                   | 0       |
| Com_xa_end                      | 0       |
| Com_xa_prepare                   | 0       |
| Com_xa_recover                   | 0       |
| Com_xa_rollback                | 0       |
| Com_xa_start                   | 0       |
| Compression                      | OFF    |
| Connections                      | 3643    |
| Created_tmp_disk_tables          | 0       |
| Created_tmp_files                | 5       |
| Created_tmp_tables             | 4       |
| Delayed_errors                   | 0       |
| Delayed_insert_threads          | 0       |
| Delayed_writes                   | 0       |
| Flush_commands                   | 1       |
| Handler_commit                   | 0       |
| Handler_delete                   | 0       |
| Handler_discover                | 0       |
| Handler_prepare                | 0       |
| Handler_read_first             | 0       |
| Handler_read_key                | 0       |
| Handler_read_next                | 0       |
| Handler_read_prev                | 0       |
| Handler_read_rnd                | 0       |
| Handler_read_rnd_next          | 680    |
| Handler_rollback                | 0       |
| Handler_savepoint                | 0       |
| Handler_savepoint_rollback       | 0       |
| Handler_update                   | 0       |
| Handler_write                   | 809    |
| Innodb_buffer_pool_pages_data    | 4188    |
| Innodb_buffer_pool_pages_dirty | 0       |
| Innodb_buffer_pool_pages_flushed  | 5       |
| Innodb_buffer_pool_pages_free    | 28087    |
| Innodb_buffer_pool_pages_latched  | 2       |
| Innodb_buffer_pool_pages_misc    | 493    |
| Innodb_buffer_pool_pages_total | 32768    |
| Innodb_buffer_pool_read_ahead_rnd | 3       |
| Innodb_buffer_pool_read_ahead_seq | 59       |
| Innodb_buffer_pool_read_requests  | 744930687 |
| Innodb_buffer_pool_reads       | 334    |
| Innodb_buffer_pool_wait_free    | 0       |
| Innodb_buffer_pool_write_requests | 5       |
| Innodb_data_fsyncs             | 23       |
| Innodb_data_pending_fsyncs       | 0       |
| Innodb_data_pending_reads       | 0       |
| Innodb_data_pending_writes       | 0       |
| Innodb_data_read                | 70799360  |
| Innodb_data_reads                | 429    |
| Innodb_data_writes             | 23       |
| Innodb_data_written             | 170496 |
| Innodb_dblwr_pages_written       | 5       |
| Innodb_dblwr_writes             | 5       |
| Innodb_log_waits                | 0       |
| Innodb_log_write_requests       | 0       |
| Innodb_log_writes                | 6       |
| Innodb_os_log_fsyncs             | 13       |
| Innodb_os_log_pending_fsyncs    | 0       |
| Innodb_os_log_pending_writes    | 0       |
| Innodb_os_log_written          | 3072    |
| Innodb_page_size                | 16384    |
| Innodb_pages_created             | 0       |
| Innodb_pages_read                | 4188    |
| Innodb_pages_written             | 5       |
| Innodb_row_lock_current_waits    | 0       |
| Innodb_row_lock_time             | 0       |
| Innodb_row_lock_time_avg       | 0       |
| Innodb_row_lock_time_max       | 0       |
| Innodb_row_lock_waits          | 0       |
| Innodb_rows_deleted             | 0       |
| Innodb_rows_inserted             | 0       |
| Innodb_rows_read                | 249630596 |
| Innodb_rows_updated             | 0       |
| Key_blocks_not_flushed          | 0       |
| Key_blocks_unused                | 927838 |
| Key_blocks_used                | 3       |
| Key_read_requests                | 6       |
| Key_reads                      | 3       |
| Key_write_requests             | 0       |
| Key_writes                      | 0       |
| Last_query_cost                | 10.499000 |
| Max_used_connections             | 113    |
| Not_flushed_delayed_rows       | 0       |
| Open_files                      | 16       |
| Open_streams                   | 0       |
| Open_tables                      | 18       |
| Opened_tables                   | 0       |
| Prepared_stmt_count             | 0       |
| Qcache_free_blocks             | 1       |
| Qcache_free_memory             | 65854216  |
| Qcache_hits                      | 7804    |
| Qcache_inserts                   | 989    |
| Qcache_lowmem_prunes             | 0       |
| Qcache_not_cached                | 157    |
| Qcache_queries_in_cache          | 989    |
| Qcache_total_blocks             | 1981    |
| Questions                      | 28590    |
| Rpl_status                      | NULL    |
| Select_full_join                | 0       |
| Select_full_range_join          | 0       |
| Select_range                   | 0       |
| Select_range_check             | 0       |
| Select_scan                      | 4       |
| Slave_open_temp_tables          | 0       |
| Slave_retried_transactions       | 0       |
| Slave_running                   | OFF    |
| Slow_launch_threads             | 0       |
| Slow_queries                   | 0       |
| Sort_merge_passes                | 0       |
| Sort_range                      | 0       |
| Sort_rows                      | 0       |
| Sort_scan                      | 0       |
| Table_locks_immediate          | 1028    |
| Table_locks_waited             | 0       |
| Tc_log_max_pages_used          | 0       |
| Tc_log_page_size                | 0       |
| Tc_log_page_waits                | 0       |
| Threads_cached                   | 76       |
| Threads_connected                | 5       |
| Threads_created                | 113    |
| Threads_running                | 2       |
| Uptime                         | 2867    |
| Uptime_since_flush_status       | 2867    |
+-----------------------------------+-----------+

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

ohno

白手起家

论坛徽章:: 0

3楼 [报告]

发表于 2008-08-14 19:05 |只看该作者

老实说， mysql本来就不是什么大型数据库服务器。

靠系统本身的设置来提高查询速度是有限的。

很大关系到你的查询语句方面。
=====================================
下面有个文章是设置mysql查询缓存设置的视频教程。

提高MySql查询性能 - 打开和设置查询缓存入门视频教程

不过我还是建议你结合你的sql查询语句来提高查询速度。