MySQL Forums
Forum List  »  InnoDB

poor performance on good hardware
Posted by: Alexander Kosykh
Date: December 02, 2009 08:36AM

hi.

I've mysql 5.1.33 multithreaded server with innodb on FreeBSD 7.2-RELEASE amd64. Hardware is two quad core xeon proccesor, 16G memory and RAID5.
There were problems begin sometime ago.
For example my table for radacct have <5million rows. Then I was tryed to make optimization it take >5 hours!
I've searching across the forum, find some recommendations made them but no results :(
I change innodb_buffer_pool_size from 4G to 12G and innodb_flush_method from default (fsync()) to O_DIRECT try again but no changes.

There is my innodb variables
mysql> show variables like "%innodb%";
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    |
| ignore_builtin_innodb           | OFF                    |
| innodb_adaptive_hash_index      | ON                     |
| innodb_additional_mem_pool_size | 1048576                |
| innodb_autoextend_increment     | 8                      |
| innodb_autoinc_lock_mode        | 1                      |
| innodb_buffer_pool_size         | 4294967296             |
| 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                  |
+---------------------------------+------------------------+
mysql status
mysqladmin -u root -p extended-status -i 100 -r
Enter password:
+-----------------------------------+-------------+
| Variable_name                     | Value       |
+-----------------------------------+-------------+
| Aborted_clients                   | 263         |
| Aborted_connects                  | 1           |
| Binlog_cache_disk_use             | 0           |
| Binlog_cache_use                  | 0           |
| Bytes_received                    | 70376042    |
| Bytes_sent                        | 1898355071  |
| Com_admin_commands                | 37445       |
| 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                         | 47278       |
| Com_binlog                        | 0           |
| Com_call_procedure                | 21052       |
| Com_change_db                     | 1896        |
| Com_change_master                 | 0           |
| Com_check                         | 0           |
| Com_checksum                      | 0           |
| Com_commit                        | 47254       |
| 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                  | 0           |
| Com_create_trigger                | 0           |
| Com_create_udf                    | 0           |
| Com_create_user                   | 0           |
| Com_create_view                   | 0           |
| Com_dealloc_sql                   | 0           |
| Com_delete                        | 26          |
| 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                    | 0           |
| 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                        | 108015      |
| Com_insert_select                 | 0           |
| Com_install_plugin                | 0           |
| Com_kill                          | 5           |
| Com_load                          | 0           |
| Com_load_master_data              | 0           |
| Com_load_master_table             | 0           |
| Com_lock_tables                   | 0           |
| Com_optimize                      | 2           |
| 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                       | 0           |
| Com_replace_select                | 0           |
| Com_reset                         | 0           |
| Com_restore_table                 | 0           |
| Com_revoke                        | 0           |
| Com_revoke_all                    | 0           |
| Com_rollback                      | 19          |
| Com_rollback_to_savepoint         | 0           |
| Com_savepoint                     | 0           |
| Com_select                        | 105638      |
| Com_set_option                    | 2291        |
| Com_show_authors                  | 0           |
| Com_show_binlog_events            | 0           |
| Com_show_binlogs                  | 0           |
| Com_show_charsets                 | 4           |
| Com_show_collations               | 4           |
| 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                | 6           |
| Com_show_engine_logs              | 0           |
| Com_show_engine_mutex             | 0           |
| Com_show_engine_status            | 182         |
| Com_show_events                   | 0           |
| Com_show_errors                   | 0           |
| Com_show_fields                   | 5           |
| Com_show_function_status          | 0           |
| Com_show_grants                   | 0           |
| Com_show_keys                     | 39          |
| Com_show_master_status            | 0           |
| Com_show_new_master               | 0           |
| Com_show_open_tables              | 0           |
| Com_show_plugins                  | 0           |
| Com_show_privileges               | 0           |
| Com_show_procedure_status         | 0           |
| Com_show_processlist              | 20343       |
| Com_show_profile                  | 0           |
| Com_show_profiles                 | 0           |
| Com_show_slave_hosts              | 0           |
| Com_show_slave_status             | 648         |
| Com_show_status                   | 651         |
| Com_show_storage_engines          | 3           |
| Com_show_table_status             | 29          |
| Com_show_tables                   | 74          |
| Com_show_triggers                 | 0           |
| Com_show_variables                | 655         |
| Com_show_warnings                 | 0           |
| Com_slave_start                   | 0           |
| Com_slave_stop                    | 0           |
| Com_stmt_close                    | 14196       |
| Com_stmt_execute                  | 14214       |
| Com_stmt_fetch                    | 0           |
| Com_stmt_prepare                  | 14214       |
| Com_stmt_reprepare                | 0           |
| Com_stmt_reset                    | 0           |
| Com_stmt_send_long_data           | 0           |
| Com_truncate                      | 0           |
| Com_uninstall_plugin              | 0           |
| Com_unlock_tables                 | 0           |
| Com_update                        | 140107      |
| Com_update_multi                  | 0           |
| 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                       | 0           |
| Connections                       | 2972        |
| Created_tmp_disk_tables           | 14          |
| Created_tmp_files                 | 5           |
| Created_tmp_tables                | 16291       |
| Delayed_errors                    | 0           |
| Delayed_insert_threads            | 0           |
| Delayed_writes                    | 0           |
| Flush_commands                    | 1           |
| Handler_commit                    | 400516      |
| Handler_delete                    | 3627725     |
| Handler_discover                  | 0           |
| Handler_prepare                   | 0           |
| Handler_read_first                | 2116        |
| Handler_read_key                  | 785841      |
| Handler_read_next                 | 199319072   |
| Handler_read_prev                 | 1821834     |
| Handler_read_rnd                  | 27323       |
| Handler_read_rnd_next             | 206669337   |
| Handler_rollback                  | 30          |
| Handler_savepoint                 | 0           |
| Handler_savepoint_rollback        | 0           |
| Handler_update                    | 152234      |
| Handler_write                     | 2255424     |
| Innodb_buffer_pool_pages_data     | 781672      |
| Innodb_buffer_pool_pages_dirty    | 5953        |
| Innodb_buffer_pool_pages_flushed  | 2116738     |
| Innodb_buffer_pool_pages_free     | 0           |
| Innodb_buffer_pool_pages_misc     | 4760        |
| Innodb_buffer_pool_pages_total    | 786432      |
| Innodb_buffer_pool_read_ahead_rnd | 4387        |
| Innodb_buffer_pool_read_ahead_seq | 13739       |
| Innodb_buffer_pool_read_requests  | 331223970   |
| Innodb_buffer_pool_reads          | 496136      |
| Innodb_buffer_pool_wait_free      | 0           |
| Innodb_buffer_pool_write_requests | 80399441    |
| Innodb_data_fsyncs                | 117978      |
| Innodb_data_pending_fsyncs        | 0           |
| Innodb_data_pending_reads         | 2           |
| Innodb_data_pending_writes        | 1           |
| Innodb_data_read                  | 23129378816 |
| Innodb_data_reads                 | 533226      |
| Innodb_data_writes                | 779569      |
| Innodb_data_written               | 72038687744 |
| Innodb_dblwr_pages_written        | 2116866     |
| Innodb_dblwr_writes               | 21775       |
| Innodb_log_waits                  | 0           |
| Innodb_log_write_requests         | 6094726     |
| Innodb_log_writes                 | 71432       |
| Innodb_os_log_fsyncs              | 73405       |
| Innodb_os_log_pending_fsyncs      | 0           |
| Innodb_os_log_pending_writes      | 0           |
| Innodb_os_log_written             | 2674337792  |
| Innodb_page_size                  | 16384       |
| Innodb_pages_created              | 75665       |
| Innodb_pages_read                 | 1411524     |
| Innodb_pages_written              | 2116738     |
| Innodb_row_lock_current_waits     | 0           |
| Innodb_row_lock_time              | 51587       |
| Innodb_row_lock_time_avg          | 8597        |
| Innodb_row_lock_time_max          | 51264       |
| Innodb_row_lock_waits             | 6           |
| Innodb_rows_deleted               | 3627724     |
| Innodb_rows_inserted              | 1849688     |
| Innodb_rows_read                  | 406319028   |
| Innodb_rows_updated               | 140982      |
| Key_blocks_not_flushed            | 0           |
| Key_blocks_unused                 | 319658      |
| Key_blocks_used                   | 8           |
| Key_read_requests                 | 174         |
| Key_reads                         | 8           |
| Key_write_requests                | 0           |
| Key_writes                        | 0           |
| Last_query_cost                   | 0           |
| Max_used_connections              | 130         |
| Not_flushed_delayed_rows          | 0           |
| Open_files                        | 35          |
| Open_streams                      | 0           |
| Open_table_definitions            | 145         |
| Open_tables                       | 176         |
| Opened_files                      | 629         |
| Opened_table_definitions          | 152         |
| Opened_tables                     | 193         |
| Prepared_stmt_count               | 0           |
| Qcache_free_blocks                | 404         |
| Qcache_free_memory                | 14052640    |
| Qcache_hits                       | 75062       |
| Qcache_inserts                    | 75972       |
| Qcache_lowmem_prunes              | 0           |
| Qcache_not_cached                 | 30061       |
| Qcache_queries_in_cache           | 18406       |
| Qcache_total_blocks               | 37326       |
| Queries                           | 602433      |
| Questions                         | 552887      |
| Rpl_status                        | 0           |
| Select_full_join                  | 0           |
| Select_full_range_join            | 0           |
| Select_range                      | 271         |
| Select_range_check                | 0           |
| Select_scan                       | 18214       |
| Slave_open_temp_tables            | 0           |
| Slave_retried_transactions        | 0           |
| Slave_running                     | 0           |
| Slow_launch_threads               | 0           |
| Slow_queries                      | 79          |
| Sort_merge_passes                 | 0           |
| Sort_range                        | 0           |
| Sort_rows                         | 256         |
| Sort_scan                         | 329         |
| Table_locks_immediate             | 437141      |
| Table_locks_waited                | -43200      |
| Tc_log_max_pages_used             | 0           |
| Tc_log_page_size                  | -4          |
| Tc_log_page_waits                 | -429496729601|
| Threads_cached                    | 1           |
| Threads_connected                 | -4294970490 |
| Threads_created                   | -34378805383|
| Threads_running                   | 4           |
| Uptime                            | 7417        |
| Uptime_since_flush_status         | 11017       |
+-----------------------------------+-------------+

could you help me to find problem?

UPDATE: One more. I've read that it better to use innodb_file_per_table if there are a small number of tables in DB. How much table is this? I have about 150 tables in my DB. If it suitable for my case, how to set this parameter on exist DB?



Edited 1 time(s). Last edit at 12/02/2009 09:03AM by Alexander Kosykh.

Options: ReplyQuote


Subject
Views
Written By
Posted
poor performance on good hardware
2879
December 02, 2009 08:36AM
1516
December 08, 2009 08:30PM
1868
December 09, 2009 08:28PM
1650
December 10, 2009 10:39PM
1596
December 11, 2009 10:43AM


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.