GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Huge CPU load + slow browsing after converting to InnoDB+Percona

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Huge CPU load + slow browsing after converting to InnoDB+Percona

    Hello Everyone ,
    I have a vBulletin database with 205 tables , 6GB size, 110K dailys visits website with daily 400K pageviews , I've doing this steps to convert to Percona .
    1.I stop my MySQL server .
    2.I got a backup for my databases (Full mysql dir + SQL files)
    3.I remove mysql from my server using yum remove mysql....
    4.I Install Percona using rpm -iv on there RPMS .
    5.I start converting my tables , table by table .
    6.I start the Percona again .

    What i notice after starting the percona that my DB dir size goes to 29K and most of files their is empty ,and after when i check the DB size from PhpMyAdmin , i find it increased from 6GB to 7.5GB without any reason !

    29K ./MYDB
    996K ./mysql
    7.5G .

    root@server [/backup/working-mysql]# ls -lhS
    total 7.5G
    -rw-rw---- 1 mysql mysql 7.5G Jun 1 18:41 ibdata1
    -rw-rw---- 1 mysql mysql 5.0M Jun 1 18:41 ib_logfile0
    -rw-rw---- 1 mysql mysql 5.0M Jun 1 18:23 ib_logfile1


    After that i start getting a huge vBulletin database errors ! like
    MySQL Error : Lock wait timeout exceeded; try restarting transaction
    and many more .

    Information about my server :
    CPU : Quad Xeon Quad Core E7320 2.13GHz
    RAM: 8GB
    Harddives: Dual 320GB SCIS
    OS: RHEL5 64 Bit
    WebServer: Apache 2.2.5
    PHP: PHP 5.2.13
    MySQL: 5.1.45-51 Percona SQL Server (GPL), XtraDB 10.2

    mysqladmin var ext stat ver proc
    Quote:

    +-----------------------------------------+----------------- ------------------------------------------------------------ --------------+
    | Variable_name | Value |
    +-----------------------------------------+----------------- ------------------------------------------------------------ --------------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | autocommit | ON |
    | automatic_sp_privileges | ON |
    | back_log | 20 |
    | basedir | / |
    | big_tables | OFF |
    | binlog_cache_size | 32768 |
    | binlog_direct_non_transactional_updates | OFF |
    | binlog_format | STATEMENT |
    | bulk_insert_buffer_size | 8388608 |
    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | latin1_swedish_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci |
    | completion_type | 0 |
    | concurrent_insert | 2 |
    | connect_timeout | 160 |
    | datadir | /backup/working-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 |
    | engine_condition_pushdown | ON |
    | error_count | 0 |
    | event_scheduler | OFF |
    | expire_logs_days | 0 |
    | flush | OFF |
    | flush_time | 0 |
    | foreign_key_checks | ON |
    | ft_boolean_syntax | + -><()~*:""&| |
    | ft_max_word_len | 84 |
    | ft_min_word_len | 4 |
    | ft_query_expansion_limit | 20 |
    | ft_stopword_file | (built-in) |
    | general_log | OFF |
    | general_log_file | /backup/working-mysql/server.log |
    | group_concat_max_len | 1024 |
    | have_community_features | YES |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | DISABLED |
    | have_dynamic_loading | YES |
    | have_geometry | YES |
    | have_innodb | YES |
    | have_ndbcluster | NO |
    | have_openssl | DISABLED |
    | have_partitioning | YES |
    | have_query_cache | YES |
    | have_rtree_keys | YES |
    | have_ssl | DISABLED |
    | have_symlink | YES |
    | hostname | server.XXXXXXe.com |
    | identity | 0 |
    | ignore_builtin_innodb | OFF |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_adaptive_checkpoint | estimate |
    | innodb_adaptive_flushing | OFF |
    | innodb_adaptive_hash_index | ON |
    | innodb_additional_mem_pool_size | 8388608 |
    | innodb_autoextend_increment | 8 |
    | innodb_autoinc_lock_mode | 1 |
    | innodb_buffer_pool_size | 134217728 |
    | innodb_change_buffering | inserts |
    | innodb_checkpoint_age_target | 0 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:10M:autoextend |
    | innodb_data_home_dir | |
    | innodb_dict_size_limit | 0 |
    | innodb_doublewrite | ON |
    | innodb_enable_unsafe_group_commit | 0 |
    | innodb_expand_import | 0 |
    | innodb_extra_rsegments | 0 |
    | innodb_extra_undoslots | OFF |
    | innodb_fast_checksum | OFF |
    | innodb_fast_recovery | ON |
    | innodb_fast_shutdown | 1 |
    | innodb_file_format | Antelope |
    | innodb_file_format_check | Barracuda |
    | innodb_file_per_table | OFF |
    | innodb_flush_log_at_trx_commit | 1 |
    | innodb_flush_method | |
    | innodb_flush_neighbor_pages | 1 |
    | innodb_force_recovery | 0 |
    | innodb_ibuf_accel_rate | 100 |
    | innodb_ibuf_active_contract | 1 |
    | innodb_ibuf_max_size | 67092480 |
    | innodb_io_capacity | 200 |
    | innodb_lock_wait_timeout | 50 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_buffer_size | 8388608 |
    | innodb_log_file_size | 5242880 |
    | innodb_log_files_in_group | 2 |
    | innodb_log_group_home_dir | ./ |
    | innodb_max_dirty_pages_pct | 75 |
    | innodb_max_purge_lag | 0 |
    | innodb_mirrored_log_groups | 1 |
    | innodb_old_blocks_pct | 37 |
    | innodb_old_blocks_time | 0 |
    | innodb_open_files | 300 |
    | innodb_overwrite_relay_log_info | OFF |
    | innodb_page_size | 16384 |
    | innodb_pass_corrupt_table | 0 |
    | innodb_read_ahead | linear |
    | innodb_read_ahead_threshold | 56 |
    | innodb_read_io_threads | 4 |
    | innodb_recovery_stats | OFF |
    | innodb_relax_table_creation | 0 |
    | innodb_replication_delay | 0 |
    | innodb_rollback_on_timeout | OFF |
    | innodb_show_locks_held | 10 |
    | innodb_show_verbose_locks | 0 |
    | innodb_spin_wait_delay | 6 |
    | innodb_stats_auto_update | 1 |
    | innodb_stats_method | nulls_equal |
    | innodb_stats_on_metadata | ON |
    | innodb_stats_sample_pages | 8 |
    | innodb_stats_update_need_lock | 1 |
    | innodb_strict_mode | OFF |
    | innodb_support_xa | ON |
    | innodb_sync_spin_loops | 30 |
    | innodb_table_locks | ON |
    | innodb_thread_concurrency | 0 |
    | innodb_thread_concurrency_timer_based | OFF |
    | innodb_thread_sleep_delay | 10000 |
    | innodb_use_purge_thread | 1 |
    | innodb_use_sys_malloc | ON |
    | innodb_version | 1.0.6-10.2 |
    | innodb_write_io_threads | 4 |
    | insert_id | 0 |
    | interactive_timeout | 28800 |
    | join_buffer_size | 2097152 |
    | keep_files_on_create | OFF |
    | key_buffer_size | 419430400 |
    | 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 |
    | last_insert_id | 0 |
    | 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_bin_trust_routine_creators | OFF |
    | log_error | /backup/working-mysql/server.XXXXXXe.com.err |
    | log_output | FILE |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_filter | |
    | log_slow_queries | OFF |
    | log_slow_rate_limit | 1 |
    | log_slow_slave_statements | OFF |
    | log_slow_sp_statements | ON |
    | log_slow_timestamp_every | OFF |
    | log_slow_verbosity | microtime |
    | log_warnings | 1 |
    | long_query_time | 2.000000 |
    | low_priority_updates | ON |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 134217728 |
    | max_binlog_cache_size | 18446744073709547520 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 700 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 134217728 |
    | max_insert_delayed_threads | 20 |
    | max_join_size | 18446744073709551615 |
    | max_length_for_sort_data | 1024 |
    | max_prepared_stmt_count | 16382 |
    | max_relay_log_size | 0 |
    | max_seeks_for_key | 18446744073709551615 |
    | max_sort_length | 1024 |
    | max_sp_recursion_depth | 0 |
    | max_tmp_tables | 32 |
    | max_user_connections | 0 |
    | max_write_lock_count | 8 |
    | min_examined_row_limit | 0 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 9223372036853727232 |
    | myisam_mmap_size | 18446744073709551615 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 67108864 |
    | myisam_stats_method | nulls_unequal |
    | myisam_use_mmap | OFF |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30 |
    | net_retry_count | 10 |
    | net_write_timeout | 60 |
    | new | OFF |
    | old | OFF |
    | old_alter_table | OFF |
    | old_passwords | OFF |
    | open_files_limit | 20710 |
    | optimizer_fix | OFF |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_so rt_union=o n,index_merge_intersection=on |
    | pid_file | /backup/working-mysql/server.XXXXXXe.com.pid |
    | plugin_dir | /usr/lib64/mysql/plugin |
    | port | 3306 |
    | preload_buffer_size | 32768 |
    | profiling | OFF |
    | profiling_history_size | 15 |
    | profiling_server | OFF |
    | profiling_use_getrusage | OFF |
    | protocol_version | 10 |
    | pseudo_thread_id | 0 |
    | query_alloc_block_size | 65536 |
    | query_cache_limit | 16777216 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 67108864 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 131072 |
    | rand_seed1 | |
    | rand_seed2 | |
    | range_alloc_block_size | 4096 |
    | read_buffer_size | 4194304 |
    | read_only | OFF |
    | read_rnd_buffer_size | 524288 |
    | relay_log | |
    | relay_log_index | |
    | relay_log_info_file | relay-log.info |
    | relay_log_purge | ON |
    | relay_log_space_limit | 0 |
    | report_host | |
    | report_password | |
    | report_port | 3306 |
    | report_user | |
    | rpl_recovery_rank | 0 |
    | secure_auth | OFF |
    | secure_file_priv | |
    | server_id | 0 |
    | skip_external_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slave_compressed_protocol | OFF |
    | slave_exec_mode | STRICT |
    | slave_load_tmpdir | /tmp |
    | slave_net_timeout | 3600 |
    | slave_skip_errors | OFF |
    | slave_transaction_retries | 10 |
    | slow_launch_time | 2 |
    | slow_query_log | OFF |
    | slow_query_log_file | /backup/working-mysql/server-slow.log |
    | socket | /var/lib/mysql/mysql.sock |
    | sort_buffer_size | 4194304 |
    | sql_auto_is_null | ON |
    | sql_big_selects | ON |
    | sql_big_tables | OFF |
    | sql_buffer_result | OFF |
    | sql_log_bin | ON |
    | sql_log_off | OFF |
    | sql_log_update | ON |
    | sql_low_priority_updates | ON |
    | sql_max_join_size | 18446744073709551615 |
    | sql_mode | |
    | sql_notes | ON |
    | sql_quote_show_create | ON |
    | sql_safe_updates | OFF |
    | sql_select_limit | 18446744073709551615 |
    | sql_slave_skip_counter | |
    | 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_definition_cache | 512 |
    | table_lock_wait_timeout | 30 |
    | table_open_cache | 10000 |
    | table_type | MyISAM |
    | thread_cache_size | 1024 |
    | thread_handling | one-thread-per-connection |
    | thread_stack | 262144 |
    | thread_statistics | OFF |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | timestamp | 1275434926 |
    | tmp_table_size | 134217728 |
    | tmpdir | /tmp |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | unique_checks | ON |
    | updatable_views_with_limit | YES |
    | use_global_long_query_time | OFF |
    | userstat_running | OFF |
    | version | 5.1.45-51 |
    | version_comment | Percona SQL Server (GPL), XtraDB 10.2 |
    | version_compile_machine | x86_64 |
    | version_compile_os | unknown-linux-gnu |
    | wait_timeout | 240 |
    | warning_count | 0 |
    +-----------------------------------------+----------------- ------------------------------------------------------------ --------------+
    +---------------------------------------+---------------+
    | Variable_name | Value |
    +---------------------------------------+---------------+
    | Aborted_clients | 7 |
    | Aborted_connects | 2 |
    | Binlog_cache_disk_use | 0 |
    | Binlog_cache_use | 0 |
    | Bytes_received | 2488753415 |
    | Bytes_sent | 197933973858 |
    | Com_admin_commands | 1 |
    | Com_assign_to_keycache | 0 |
    | Com_alter_db | 0 |
    | Com_alter_db_upgrade | 0 |
    | Com_alter_event | 0 |
    | Com_alter_function | 0 |
    | Com_alter_procedure | 0 |
    | Com_alter_server | 0 |
    | Com_alter_table | 1 |
    | Com_alter_tablespace | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_binlog | 0 |
    | Com_call_procedure | 0 |
    | Com_change_db | 765348 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_checksum | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_event | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_procedure | 0 |
    | Com_create_server | 0 |
    | Com_create_table | 46 |
    | Com_create_trigger | 0 |
    | Com_create_udf | 0 |
    | Com_create_user | 0 |
    | Com_create_view | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 25231 |
    | Com_delete_multi | 0 |
    | Com_do | 0 |
    | Com_drop_db | 0 |
    | Com_drop_event | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_procedure | 0 |
    | Com_drop_server | 0 |
    | Com_drop_table | 46 |
    | Com_drop_trigger | 0 |
    | Com_drop_user | 0 |
    | Com_drop_view | 0 |
    | Com_empty_query | 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 | 463647 |
    | Com_insert_select | 2219 |
    | Com_install_plugin | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 6717 |
    | Com_optimize | 1 |
    | Com_preload_keys | 0 |
    | Com_prepare_sql | 0 |
    | Com_purge | 0 |
    | Com_purge_before_date | 0 |
    | Com_release_savepoint | 0 |
    | Com_rename_table | 0 |
    | Com_rename_user | 0 |
    | Com_repair | 0 |
    | Com_replace | 18311 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_revoke_all | 0 |
    | Com_rollback | 0 |
    | Com_rollback_to_savepoint | 0 |
    | Com_savepoint | 0 |
    | Com_select | 2914228 |
    | Com_set_option | 722055 |
    | Com_show_authors | 0 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 2 |
    | Com_show_charsets | 0 |
    | Com_show_client_statistics | 0 |
    | Com_show_collations | 0 |
    | Com_show_column_types | 0 |
    | Com_show_contributors | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_event | 0 |
    | Com_show_create_func | 0 |
    | Com_show_create_proc | 0 |
    | Com_show_create_table | 11 |
    | Com_show_create_trigger | 0 |
    | Com_show_databases | 7 |
    | Com_show_engine_logs | 0 |
    | Com_show_engine_mutex | 0 |
    | Com_show_engine_status | 0 |
    | Com_show_events | 0 |
    | Com_show_errors | 0 |
    | Com_show_fields | 1943 |
    | Com_show_function_status | 0 |
    | Com_show_grants | 2 |
    | Com_show_index_statistics | 0 |
    | Com_show_keys | 11 |
    | Com_show_master_status | 5 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_patches | 0 |
    | Com_show_plugins | 0 |
    | Com_show_privileges | 0 |
    | Com_show_procedure_status | 0 |
    | Com_show_processlist | 1 |
    | Com_show_profile | 0 |
    | Com_show_profiles | 0 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 5 |
    | Com_show_status | 52 |
    | Com_show_storage_engines | 0 |
    | Com_show_table_statistics | 0 |
    | Com_show_table_status | 21 |
    | Com_show_tables | 465 |
    | Com_show_thread_statistics | 0 |
    | Com_show_temporary_tables | 0 |
    | Com_show_triggers | 11 |
    | Com_show_user_statistics | 0 |
    | Com_show_variables | 76 |
    | 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_reprepare | 0 |
    | Com_stmt_reset | 0 |
    | Com_stmt_send_long_data | 0 |
    | Com_truncate | 46 |
    | Com_uninstall_plugin | 0 |
    | Com_unlock_tables | 6717 |
    | Com_update | 585272 |
    | Com_update_multi | 46 |
    | 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 | 559405 |
    | Created_tmp_disk_tables | 13158 |
    | Created_tmp_files | 5581 |
    | Created_tmp_tables | 62211 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 3030918 |
    | Handler_delete | 116121 |
    | Handler_discover | 0 |
    | Handler_prepare | 0 |
    | Handler_read_first | 185170 |
    | Handler_read_key | 81030861 |
    | Handler_read_next | 1168985003 |
    | Handler_read_prev | 152037432 |
    | Handler_read_rnd | 5153136 |
    | Handler_read_rnd_next | 14252254457 |
    | Handler_rollback | 653 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 78237547 |
    | Handler_write | 2406145 |
    | Innodb_buffer_pool_pages_data | 8150 |
    | Innodb_buffer_pool_pages_dirty | 20 |
    | Innodb_buffer_pool_pages_flushed | 8497944 |
    | Innodb_buffer_pool_pages_free | 0 |
    | Innodb_buffer_pool_pages_misc | 41 |
    | Innodb_buffer_pool_pages_total | 8191 |
    | Innodb_buffer_pool_read_ahead | 14252554 |
    | Innodb_buffer_pool_read_ahead_evicted | 43323186 |
    | Innodb_buffer_pool_read_requests | 3216599409 |
    | Innodb_buffer_pool_reads | 328438136 |
    | Innodb_buffer_pool_wait_free | 50493 |
    | Innodb_buffer_pool_write_requests | 161281195 |
    | Innodb_data_fsyncs | 825552 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 5615445037056 |
    | Innodb_data_reads | 328758060 |
    | Innodb_data_writes | 1839301 |
    | Innodb_data_written | 296516217344 |
    | Innodb_dblwr_pages_written | 8497944 |
    | Innodb_dblwr_writes | 89494 |
    | Innodb_dict_tables | 207 |
    | Innodb_have_atomic_builtins | ON |
    | Innodb_log_waits | 4 |
    | Innodb_log_write_requests | 34702979 |
    | Innodb_log_writes | 638341 |
    | Innodb_os_log_fsyncs | 649456 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 18051298816 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 141925 |
    | Innodb_pages_read | 342739825 |
    | Innodb_pages_written | 8497944 |
    | Innodb_row_lock_current_waits | 0 |
    | Innodb_row_lock_time | 71560268 |
    | Innodb_row_lock_time_avg | 24968 |
    | Innodb_row_lock_time_max | 51892 |
    | Innodb_row_lock_waits | 2866 |
    | Innodb_rows_deleted | 56467 |
    | Innodb_rows_inserted | 508462 |
    | Innodb_rows_read | 8967103104 |
    | Innodb_rows_updated | 77929552 |
    | Key_blocks_not_flushed | 0 |
    | Key_blocks_unused | 333258 |
    | Key_blocks_used | 37 |
    | Key_read_requests | 255785 |
    | Key_reads | 6 |
    | Key_write_requests | 33504 |
    | Key_writes | 18037 |
    | Last_query_cost | 0.000000 |
    | Max_used_connections | 231 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 65 |
    | Open_streams | 0 |
    | Open_table_definitions | 226 |
    | Open_tables | 1088 |
    | Opened_files | 61832 |
    | Opened_table_definitions | 322 |
    | Opened_tables | 1418 |
    | Prepared_stmt_count | 0 |
    | Qcache_free_blocks | 5310 |
    | Qcache_free_memory | 39708664 |
    | Qcache_hits | 2400983 |
    | Qcache_inserts | 2775661 |
    | Qcache_lowmem_prunes | 8228 |
    | Qcache_not_cached | 131821 |
    | Qcache_queries_in_cache | 4289 |
    | Qcache_total_blocks | 14488 |
    | Queries | 8472930 |
    | Questions | 8472930 |
    | Rpl_status | NULL |
    | Select_full_join | 10 |
    | Select_full_range_join | 34 |
    | Select_range | 734856 |
    | Select_range_check | 0 |
    | Select_scan | 429572 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 0 |
    | Slave_running | OFF |
    | Slow_launch_threads | 0 |
    | Slow_queries | 12445 |
    | Sort_merge_passes | 15018 |

  • #2
    It takes more then 3 minutes to post my thread ! , you can just try to copy my thread content and post it as new thread or new post .

    Comment


    • #3
      Your problem has nothing to do with Percona's software, it is caused by converting away from MyISAM. You didn't test this change beforehand, and you are inexperienced with configuring and administering InnoDB -- and that is the real root cause. You have a lot to learn in a very short time. You should start with Peter's blog post about what to tune in InnoDB after a default installation

      http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune- in-mysql-server-after-installation/
      http://www.mysqlperformanceblog.com/2007/11/01/innodb-perfor mance-optimization-basics/

      Comment


      • #4
        Hi xaprb ,
        Thanks for your reply , but i wonder if you read my my.cnf file ?

        Comment


        • #5
          M.A.G wrote on Wed, 02 June 2010 03:53

          Hi xaprb ,
          Thanks for your reply , but i wonder if you read my my.cnf file ?


          Yes, and that is where your problem lies.
          If you look closely you will see that you placed all your new InnoDB settings in the [mysqld_safe] group which means that mysqld will not read them.

          [mysqld]safe-show-databasedatadir = /backup/working-mysqlback_log = 20#skip-innodbskip-csvskip-blackholeskip-mrg_myisamskip-archivemax_connections = 700key_buffer = 400Mmyisam_sort_buffer_size = 64Mjoin_buffer_size = 2Mread_buffer_size = 4Msort_buffer_size = 4Mtable_cache = 10000table_definition_cache = 512long_query_time = 2thread_cache_size = 1024wait_timeout = 240connect_timeout = 160tmp_table_size = 128Mmax_heap_table_size = 128Mmax_allowed_packet = 128Mnet_buffer_length = 16384max_connect_errors = 10concurrent_insert = 4thread_concurrency = 8table_lock_wait_timeout = 30read_rnd_buffer_size = 524288bulk_insert_buffer_size = 8Mquery_cache_limit = 16Mquery_cache_size = 64Mquery_cache_type = 1query_prealloc_size = 131072query_alloc_block_size = 65536range_alloc_block_size = 4096transaction_alloc_block_size = 8192transaction_prealloc_size = 4096default-storage-engine = MyISAMmax_write_lock_count = 8low_priority_updates=1############[mysqld_safe]############nice= -10pid-file=/backup/working-mysql/mysql.pidopen_files_limit = 8192#innodb_file_per_table = 1innodb_open_files = 8048innodb_data_file_path=ibdata4:2G:autoextend:ma x:4G#innodb_buffer_pool_size = 512Minnodb_buffer_pool_size = 4Ginnodb_additional_mem_pool_size = 512Minnodb_log_file_size = 256Minnodb_log_buffer_size = 16Minnodb_thread_concurrency = 8innodb_concurrency_tickets = 500innodb_lock_wait_timeout=200innodb_flush_method = O_DIRECTinnodb_autoinc_lock_mode= 2innodb_commit_concurrency=4innodb_flush_log_at_tr x_commit=0innodb_support_xa=falseinnodb_checksums= 0innodb_doublewrite=0innodb_max_dirty_pages_pct=15 innodb_io_capacity = 10000innodb_adaptive_checkpoint = 1innodb_write_io_threads = 8innodb_read_io_threads = 8[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M


          Change your configuration to this:

          [mysqld]safe-show-databasedatadir = /backup/working-mysqlback_log = 20#skip-innodbskip-csvskip-blackholeskip-mrg_myisamskip-archivemax_connections = 700key_buffer = 400Mmyisam_sort_buffer_size = 64Mjoin_buffer_size = 2Mread_buffer_size = 4Msort_buffer_size = 4Mtable_cache = 10000table_definition_cache = 512long_query_time = 2thread_cache_size = 1024wait_timeout = 240connect_timeout = 160tmp_table_size = 128Mmax_heap_table_size = 128Mmax_allowed_packet = 128Mnet_buffer_length = 16384max_connect_errors = 10concurrent_insert = 4thread_concurrency = 8table_lock_wait_timeout = 30read_rnd_buffer_size = 524288bulk_insert_buffer_size = 8Mquery_cache_limit = 16Mquery_cache_size = 64Mquery_cache_type = 1query_prealloc_size = 131072query_alloc_block_size = 65536range_alloc_block_size = 4096transaction_alloc_block_size = 8192transaction_prealloc_size = 4096default-storage-engine = MyISAMmax_write_lock_count = 8low_priority_updates=1#innodb_file_per_table = 1innodb_open_files = 8048innodb_data_file_path=ibdata4:2G:autoextend:ma x:4G#innodb_buffer_pool_size = 512Minnodb_buffer_pool_size = 4Ginnodb_additional_mem_pool_size = 512Minnodb_log_file_size = 256Minnodb_log_buffer_size = 16Minnodb_thread_concurrency = 8innodb_concurrency_tickets = 500innodb_lock_wait_timeout=200innodb_flush_method = O_DIRECTinnodb_autoinc_lock_mode= 2innodb_commit_concurrency=4innodb_flush_log_at_tr x_commit=0innodb_support_xa=falseinnodb_checksums= 0innodb_doublewrite=0innodb_max_dirty_pages_pct=15 innodb_io_capacity = 10000innodb_adaptive_checkpoint = 1innodb_write_io_threads = 8innodb_read_io_threads = 8[mysqld_safe]nice= -10pid-file=/backup/working-mysql/mysql.pidopen_files_limit = 8192[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer = 64Msort_buffer = 64Mread_buffer = 16Mwrite_buffer = 16M


          BTW: Your setting:
          innodb_log_file_size = 256M
          feels very big, even though you have a very high performance site you rarely need above 64M and 8MB for the log_buffer.
          And your setting:
          innodb_flush_log_at_trx_commit=0
          I do recommend = 2 instead, it's safer and the difference is usually not noticeable.

          Comment


          • #6
            key_buffer = 400M

            that also feels very big for an InnoDB-only database.

            Comment


            • #7
              I didn't read your my.cnf file -- you pasted WAY too much information for me to spend time reading it all. Instead, I scrolled down with the Page Down key, and saw the errors from your error log. That was enough to show me InnoDB is badly misconfigured )

              Comment


              • #8
                Thanks for everyone who help , special thanks for sterin71 .
                I'v edited my my.cnf and i will watch their for results.
                I will update this thread soon .
                Best Regads,
                M.A.G

                Comment

                • Working...
                  X