MySQL Forums
Forum List  »  Performance

Re: mysql 5.5.9 dreadfully slow
Posted by: nick rulez
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

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
4129
February 20, 2011 11:33AM
1751
February 21, 2011 01:39PM
Re: mysql 5.5.9 dreadfully slow
2979
February 21, 2011 04:21PM
2600
February 21, 2011 05:51PM
2951
February 21, 2011 07:44PM


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.