Results 1 to 9 of 9
  1. #1

    Any suggestion for MySQL sucks?

    Everytime a fast search engine spider indexing my web sites on hosting server, Apache will reach maxclients, mysql sucks, any php page that use mysql database could not be serviced like WordPress pages. Can you guys give me some tips for mysql tuning?

    Here is my MySQL variables:

    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /usr/local/mysql-5.0.51-linux-i686/ |
    | binlog_cache_size | 32768 |
    | bulk_insert_buffer_size | 8388608 |
    | 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 | /usr/local/mysql-5.0.51-linux-i686/share/mysql/charsets/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_unicode_ci |
    | collation_server | utf8_unicode_ci |
    | completion_type | 0 |
    | concurrent_insert | 1 |
    | connect_timeout | 10 |
    | datadir | /usr/local/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 |
    | 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 | NO |
    | 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 | NO |
    | have_ssl | NO |
    | have_query_cache | YES |
    | have_raid | NO |
    | have_rtree_keys | YES |
    | have_symlink | YES |
    | hostname | cph.72pines.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_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 |
    | 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 | 100 |
    | join_buffer_size | 1044480 |
    | key_buffer_size | 268435456 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | language | /usr/local/mysql-5.0.51-linux-i686/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_error | |
    | 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 | 16776192 |
    | max_binlog_cache_size | 4294967295 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 10000 |
    | 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 | 5 |
    | max_write_lock_count | 4294967295 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 2147483647 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 67108864 |
    | 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 | 50000 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /usr/local/mysql/data/cph.pid |
    | port | 0 |
    | 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 | 16777216 |
    | 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 | 4190208 |
    | 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 | ON |
    | 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 | 1048568 |
    | 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 | CST |
    | table_cache | 256 |
    | table_lock_wait_timeout | 50 |
    | table_type | MyISAM |
    | thread_cache_size | 8 |
    | thread_stack | 196608 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | tmp_table_size | 16777216 |
    | tmpdir | /tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | updatable_views_with_limit | YES |
    | version | 5.0.51 |
    | version_comment | Source distribution |
    | version_compile_machine | i686 |
    | version_compile_os | pc-linux-gnu |
    | wait_timeout | 100 |

  2. #2
    Join Date
    Jan 2006
    Location
    Athens, Greece
    Posts
    1,479
    It's always Apache, PHP, MySQL...

    Are you using WordPress?

  3. #3
    Hundreds of WordPress sites on the server.

  4. #4
    Join Date
    Dec 2006
    Posts
    477
    http://www.hackmysql.com/mysqlreport

    Download the tool and then click on the "guide to understanding it" link.

  5. #5
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Another tool to look at is the MySQL Tuning Primer:
    http://forge.mysql.com/projects/view.php?id=44
    Daved @ Lightwave Networking, LLC.
    AS1426 https:/www.lightwave.net
    Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
    Xen PV VPS Hosting

  6. #6
    I followed the tweak guide, it seems very OK but the spiders, everytime the spiders come, MySQL's connections will increase to hundreds, I have no idea about this

  7. #7
    Join Date
    Jun 2002
    Posts
    1,376
    How do you have WordPress configured? A few thoughts from my experience:

    - WordPress doesn't get much benefit from the query cache. Some smaller tables (e.g., the categories) do, but most of the more intense queries have NOW()'s in them, making the query cache worthless. Or, at least, they did when I used them.

    - WordPress *does* benefit from APC (Alternative PHP Cache) or the like.

    - The biggest speedups come from using something like WP Super Cache, to generate an HTML 'cache' of created pages. Very few of my pageviews even hit the database at all now, they're just served out of cache.

    My site is very different from yours, I'm sure, but I'd look at the whole setup, not just MySQL.

  8. #8
    Thanks foq, my server is a hosting machine, I can't control my user's WP
    May be there are some wrong in my system, but it's hard to find out.

  9. #9
    Join Date
    Aug 2003
    Location
    Gods Own Country
    Posts
    869
    Get some good mysql expert to check your box and optimize it.
    Blessen Cherian
    Follow me on twitter.com/blessenonly
    Over a decade plus in the Hosting Industry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •