Re: mysql 5.5.9 dreadfully slow
Posted by: nick rulez
Date: February 21, 2011 04:21PM
Date: February 21, 2011 04:21PM
Hi Rick. Thanks for your reply.
I post both output variables. If you prefer I've uploaded an excel file at this url:
http://myfreefilehosting.com/f/d2d23e4dd8_0.03MB
Maybe this is more readable to compare results.
MYSQL 5.1.36
MYSQL 5.5.9
My notebook has 4GB of RAM.
OS Windows 7 Home Premium.
Please note that I've tried mysql 5.5.9 even on a virtual machine under windows xp professional and the performance is equally very poor.
I post both output variables. If you prefer I've uploaded an excel file at this url:
http://myfreefilehosting.com/f/d2d23e4dd8_0.03MB
Maybe this is more readable to compare results.
MYSQL 5.1.36
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.1.36-community-log MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'sync%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sync_binlog | 0 | | sync_frm | ON | +---------------+-------+ 2 rows in set (0.00 sec) mysql> show variables like 'innodb%'; +-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_size | 268435456 | | 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_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_stats_on_metadata | ON | | innodb_support_xa | ON | | innodb_sync_spin_loops | 20 | | innodb_table_locks | ON | | innodb_thread_concurrency | 8 | | innodb_thread_sleep_delay | 10000 | | innodb_use_legacy_cardinality_algorithm | ON | +-----------------------------------------+------------------------+ 35 rows in set (0.04 sec) mysql> show variables like '%buffer%'; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 268435456 | | innodb_log_buffer_size | 1048576 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 8192 | | preload_buffer_size | 32768 | | read_buffer_size | 262144 | | read_rnd_buffer_size | 524288 | | sort_buffer_size | 524288 | | sql_buffer_result | OFF | +-------------------------+-----------+ 12 rows in set (0.04 sec) mysql> show variables like '%log%'; +---------------------------------+-------------------------------------------------------+ | Variable_name | Value | +---------------------------------+-------------------------------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_format | MIXED | | expire_logs_days | 0 | | general_log | ON | | general_log_file | c:/wamp/mysql_nick.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | .\ | | innodb_mirrored_log_groups | 1 | | log | ON | | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | log_error | c:\wamp\logs\mysql.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | max_binlog_cache_size | 4294963200 | | max_binlog_size | 1073741824 | | max_relay_log_size | 0 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_space_limit | 0 | | slow_query_log | OFF | | slow_query_log_file | c:\wamp\bin\mysql\mysql5.1.36\data\PB-Nicola-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sql_log_update | ON | | sync_binlog | 0 | +---------------------------------+-------------------------------------------------------+ 37 rows in set (0.00 sec) mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> show variables like 'Qc%'; Empty set (0.00 sec)
MYSQL 5.5.9
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.9 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'sync%'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | sync_binlog | 0 | | sync_frm | ON | | sync_master_info | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | +---------------------+-------+ 5 rows in set (0.10 sec) mysql> show variables like 'innodb%'; +---------------------------------+------------------------+ | Variable_name | Value | +---------------------------------+------------------------+ | innodb_adaptive_flushing | ON | | innodb_adaptive_hash_index | ON | | innodb_additional_mem_pool_size | 3145728 | | innodb_autoextend_increment | 8 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 108003328 | | innodb_change_buffering | all | | 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_format | Antelope | | innodb_file_format_check | ON | | innodb_file_format_max | Antelope | | innodb_file_per_table | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_io_capacity | 200 | | innodb_lock_wait_timeout | 50 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 2097152 | | innodb_log_file_size | 54525952 | | 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_purge_batch_size | 20 | | innodb_purge_threads | 0 | | innodb_read_ahead_threshold | 56 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_rollback_on_timeout | OFF | | innodb_spin_wait_delay | 6 | | innodb_stats_on_metadata | ON | | innodb_stats_sample_pages | 8 | | innodb_strict_mode | OFF | | innodb_support_xa | ON | | innodb_sync_spin_loops | 30 | | innodb_table_locks | ON | | innodb_thread_concurrency | 10 | | innodb_thread_sleep_delay | 10000 | | innodb_use_native_aio | ON | | innodb_use_sys_malloc | ON | | innodb_version | 1.1.5 | | innodb_write_io_threads | 4 | +---------------------------------+------------------------+ 54 rows in set (0.06 sec) mysql> show variables like '%buffer%'; +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 108003328 | | innodb_change_buffering | all | | innodb_log_buffer_size | 2097152 | | join_buffer_size | 131072 | | key_buffer_size | 55574528 | | myisam_sort_buffer_size | 69206016 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 65536 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 262144 | | sql_buffer_result | OFF | +------------------------------+-----------+ 14 rows in set (0.03 sec) mysql> show variables like '%log%'; +-----------------------------------------+---------------------------------------------------------------+ | Variable_name | Value | +-----------------------------------------+---------------------------------------------------------------+ | back_log | 50 | | binlog_cache_size | 32768 | | binlog_direct_non_transactional_updates | OFF | | binlog_format | STATEMENT | | binlog_stmt_cache_size | 32768 | | expire_logs_days | 0 | | general_log | OFF | | general_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\PB-Nicola.log | | innodb_flush_log_at_trx_commit | 1 | | innodb_locks_unsafe_for_binlog | OFF | | innodb_log_buffer_size | 2097152 | | innodb_log_file_size | 54525952 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | .\ | | innodb_mirrored_log_groups | 1 | | log | OFF | | log_bin | OFF | | log_bin_trust_function_creators | OFF | | log_error | C:\ProgramData\MySQL\MySQL Server 5.5\Data\PB-Nicola.err | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_relay_log_size | 0 | | relay_log | | | relay_log_index | | | relay_log_info_file | relay-log.info | | relay_log_purge | ON | | relay_log_recovery | OFF | | relay_log_space_limit | 0 | | slow_query_log | OFF | | slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.5\Data\PB-Nicola-slow.log | | sql_log_bin | ON | | sql_log_off | OFF | | sync_binlog | 0 | | sync_relay_log | 0 | | sync_relay_log_info | 0 | +-----------------------------------------+---------------------------------------------------------------+ 41 rows in set (0.00 sec) mysql> show variables like 'query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+---------+ 5 rows in set (0.00 sec) mysql> show variables like 'Qc%'; Empty set (0.00 sec)
My notebook has 4GB of RAM.
OS Windows 7 Home Premium.
Please note that I've tried mysql 5.5.9 even on a virtual machine under windows xp professional and the performance is equally very poor.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.