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 / |