MySQL Forums :: InnoDB :: poor performance on good hardware


Advanced Search

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 2844 Alexander Kosykh 12/02/2009 08:36AM
Re: poor performance on good hardware 1760 Nanda kishore Toomula 12/02/2009 10:13PM
Re: poor performance on good hardware 1483 Nanda kishore Toomula 12/03/2009 02:17AM
Re: poor performance on good hardware 1452 Nanda kishore Toomula 12/03/2009 02:18AM
Re: poor performance on good hardware 1860 Alexander Kosykh 12/08/2009 03:42AM
Re: poor performance on good hardware 1508 Rick James 12/08/2009 08:30PM
Re: poor performance on good hardware 2368 Alexander Kosykh 12/09/2009 05:42AM
Re: poor performance on good hardware 1849 Rick James 12/09/2009 08:28PM
Re: poor performance on good hardware 1603 Alexander Kosykh 12/10/2009 11:10AM
Re: poor performance on good hardware 1641 Rick James 12/10/2009 10:39PM
Re: poor performance on good hardware 1588 Adam Erickson 12/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.