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

[B]Quote:[/B]
+-----------------------------------------+----------------- ------------------------------------------------------------ --------------+ | 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_sort_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 |<br /

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 .

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/

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

[B]M.A.G wrote on Wed, 02 June 2010 03:53[/B]
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:max: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_trx_commit=0innodb_support_xa=falseinnodb_checksums=0innodb_doublewrite=0innodb_max_dirty_pages_pct=15innodb_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:max: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_trx_commit=0innodb_support_xa=falseinnodb_checksums=0innodb_doublewrite=0innodb_max_dirty_pages_pct=15innodb_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.

key_buffer = 400M

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

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 )

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