Results 1 to 9 of 9
Thread: Any suggestion for MySQL sucks?
Hybrid View
-
01-21-2008, 12:55 PM #1Newbie
- Join Date
- Aug 2007
- Posts
- 9
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 |
-
01-21-2008, 04:02 PM #2Web Hosting Master
- Join Date
- Jan 2006
- Location
- Athens, Greece
- Posts
- 1,481
It's always Apache, PHP, MySQL...
Are you using WordPress?
-
01-21-2008, 10:17 PM #3Newbie
- Join Date
- Aug 2007
- Posts
- 9
Hundreds of WordPress sites on the server.
-
01-22-2008, 05:02 PM #4Web Hosting Evangelist
- Join Date
- Dec 2006
- Posts
- 480
http://www.hackmysql.com/mysqlreport
Download the tool and then click on the "guide to understanding it" link.
-
01-22-2008, 06:42 PM #5Web Hosting Master
- Join Date
- Apr 2003
- Location
- San Jose, CA.
- Posts
- 1,624
Another tool to look at is the MySQL Tuning Primer:
http://forge.mysql.com/projects/view.php?id=44Daved @ Lightwave Networking, LLC.
AS1426 https:/www.lightwave.net
Primary Bandwidth: EGIHosting (NLayer, NTT, HE, Cogent)
Xen PV VPS Hosting
-
01-24-2008, 10:21 PM #6Newbie
- Join Date
- Aug 2007
- Posts
- 9
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
-
01-24-2008, 11:34 PM #7Web Hosting Master
- Join Date
- Jun 2002
- Posts
- 1,378
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.
-
01-25-2008, 01:26 AM #8Newbie
- Join Date
- Aug 2007
- Posts
- 9
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.
-
01-25-2008, 01:47 AM #9Web Hosting Master
- Join Date
- Aug 2003
- Location
- Gods Own Country
- Posts
- 892
Get some good mysql expert to check your box and optimize it.
Blessen Cherian
Follow me on twitter.com/blessenonly
Two decade in Web Hosting Industry