GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Innodb Performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Innodb Performance

    Hey everyone, hope all is well.

    I have a beefy server that from time to time, gets inundated with Queries taking over 30seconds to complete (normally <1s to complete).

    Through my cacti graphs, I see average of 170,000 spin rounds, and today reached 5Million at one point (per second).

    Now what additonal information will be useful for me to provide in order to get some help =]

    For now, I will list the /etc/my.cnf , variables, server specs and status.

    Server specs are dual Quad Core Nehahlem processors with HT enabled. 24GB memory, Red Hat Enterprise Linux Server release 5.3 (Tikanga) Linux 2.6.18-128.1.10.el5 #1 SMP Wed Apr 29 13:53:08 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux




    /etc/my.cnf


    [mysqld] datadir=/data socket=/data/mysql.sock # Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package). old_passwords=1 server-id=106#server-id=10141644log-slave-updates log-bin=/logs/mysql-binrelay-log=/logs/relay-binlog-slow-queries=/logs/slow-queries.logexpire_logs_days=7 binlog_cache_size=1M wait_timeout=60 # temporary query log# log=/logs/full-query-log.log#master-host = 10.4.16.218#master-port = 3306 #master-user = replicant #master-password = replicantslave-skip-errors=1062,1452,1253,1267#tmpdir=/var/tmpfs tmpdir=/data key_buffer = 3000M table_cache = 10000 sort_buffer_size = 6M read_buffer_size = 4M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 8M thread_cache_size = 256 query_cache_size = 256M thread_concurrency = 8concurrent_insert=2tmp_table_size=128Mmax_heap_ta ble_size=128Mlong_query_time=3#tmpdir=/data/#log_queries_not_using_indexes=1default-character-set=latin1collation_server=latin1_general_cimax_al lowed_packet = 40Mmax_connections = 1000innodb_file_per_table#innodb_log_file_size=512 M#innodb_buffer_pool_size=2G#innodb_log_buffer_siz e=32M#innodb_thread_concurrency=8innodb_log_file_s ize=1Ginnodb_buffer_pool_size=8Ginnodb_log_buffer_ size=8Minnodb_thread_concurrency=36innodb_open_fil es=1000# we should change to below as to high is not good / does not do anything#innodb_flush_method=O_DIRECTinnodb_lock_w ait_timeout = 70innodb_flush_log_at_trx_commit=0[mysql.server]user=mysql#basedir=/var/lib[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[myisamchk]key_buffer = 256Msort_buffer_size = 256Mread_buffer = 2Mwrite_buffer = 2M



    variables


    Variable_name Valueauto_increment_increment 1auto_increment_offset 1automatic_sp_privileges ONback_log 50basedir /binlog_cache_size 1048576bulk_insert_buffer_size 8388608character_set_client latin1character_set_connection latin1character_set_database latin1character_set_filesystem binarycharacter_set_results latin1character_set_server latin1character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection latin1_general_cicollation_database latin1_general_cicollation_server latin1_general_cicompletion_type 0concurrent_insert 2connect_timeout 10datadir /data/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4keep_files_on_create OFFengine_condition_pushdown OFFexpire_logs_days 7flush OFFflush_time 0ft_boolean_syntax + -><()~*:""&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 1024have_archive YEShave_bdb NOhave_blackhole_engine YEShave_compress YEShave_community_features YEShave_profiling YEShave_crypt YEShave_csv YEShave_dynamic_loading YEShave_example_engine NOhave_federated_engine YEShave_geometry YEShave_innodb YEShave_isam NOhave_merge_engine YEShave_ndbcluster DISABLEDhave_openssl DISABLEDhave_ssl DISABLEDhave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YEShostname lana-db3m1ainit_connect init_file init_slave innodb_additional_mem_pool_size 1048576innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 4294967296innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir innodb_adaptive_hash_index ONinnodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_io_threads 4innodb_file_per_table ONinnodb_flush_log_at_trx_commit 0innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 70innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir innodb_log_archive OFFinnodb_log_buffer_size 8388608innodb_log_file_size 1073741824innodb_log_files_in_group 2innodb_log_group_home_dir ./innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_rollback_on_timeout OFFinnodb_support_xa ONinnodb_sync_spin_loops 20innodb_table_locks ONinnodb_thread_concurrency 36innodb_thread_sleep_delay 10000innodb_use_legacy_cardinality_algorithm ONinteractive_timeout 28800join_buffer_size 131072key_buffer_size 3145728000key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlarge_page_size 0large_pages OFFlc_time_names en_USlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin ONlog_bin_trust_function_creators OFFlog_error log_queries_not_using_indexes OFFlog_slave_updates ONlog_slow_queries ONlog_warnings 1long_query_time 3low_priority_updates OFFlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 41943040max_binlog_cache_size 18446744073709547520max_binlog_size 1073741824max_connect_errors 10max_connections 1000max_delayed_threads 20max_error_count 64max_heap_table_size 134217728max_insert_delayed_threads 20max_join_size 18446744073709551615max_length_for_sort_data 1024max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 18446744073709551615max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 0max_write_lock_count 18446744073709551615multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 9223372036853727232myisam_recover_options OFFmyisam_repair_threads 1myisam_sort_buffer_size 8388608myisam_stats_method nulls_unequalndb_autoincrement_prefetch_sz 1ndb_force_send ONndb_use_exact_count ONndb_use_transactions ONndb_cache_check_time 0ndb_connectstring net_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold_passwords ONopen_files_limit 21010optimizer_prune_level 1optimizer_search_depth 62pid_file /data/lana-db3m1a.pidplugin_dir port 3306preload_buffer_size 32768profiling OFFprofiling_history_size 15protocol_version 10query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 268435456query_cache_type ONquery_cache_wlock_invalidate OFFquery_prealloc_size 8192range_alloc_block_size 4096read_buffer_size 4194304read_only OFFread_rnd_buffer_size 16777216relay_log /logs/relay-binrelay_log_index relay_log_info_file relay-log.inforelay_log_purge ONrelay_log_space_limit 0rpl_recovery_rank 0secure_auth OFFsecure_file_priv server_id 106skip_external_locking ONskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_load_tmpdir /data/slave_net_timeout 3600slave_skip_errors 1062,1253,1267,1452slave_transaction_retries 10slow_launch_time 2socket /data/mysql.socksort_buffer_size 6291456sql_big_selects ONsql_mode sql_notes ONsql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAMsync_binlog 0sync_frm ONsystem_time_zone ESTtable_cache 10000table_lock_wait_timeout 50table_type MyISAMthread_cache_size 256thread_stack 262144time_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 134217728tmpdir /datatransaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READupdatable_views_with_limit YESversion 5.0.83-community-logversion_comment MySQL Community Edition (GPL)version_compile_machine x86_64version_compile_os unknown-linux-gnuwait_timeout 60


    global status


    Variable_name ValueAborted_clients 336Aborted_connects 1Binlog_cache_disk_use 0Binlog_cache_use 1312873Bytes_received 1169252460976Bytes_sent 7272683457845Com_admin_commands 312046576Com_alter_db 0Com_alter_table 1899Com_analyze 0Com_backup_table 0Com_begin 728656Com_call_procedure 14Com_change_db 4124360253Com_change_master 0Com_check 0Com_checksum 0Com_commit 728651Com_create_db 0Com_create_function 0Com_create_index 0Com_create_table 441887Com_create_user 0Com_dealloc_sql 0Com_delete 35676191Com_delete_multi 161068Com_do 6074960Com_drop_db 0Com_drop_function 0Com_drop_index 0Com_drop_table 10Com_drop_user 0Com_execute_sql 0Com_flush 2Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 194773574Com_insert_select 6723928Com_kill 3Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 60829Com_optimize 26732Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 0Com_replace 111144797Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 5Com_savepoint 0Com_select 1033565819Com_set_option 540642959Com_show_binlog_events 0Com_show_binlogs 23901Com_show_charsets 1078Com_show_collations 1078Com_show_column_types 0Com_show_create_db 0Com_show_create_table 612Com_show_databases 1080Com_show_errors 0Com_show_fields 2792Com_show_grants 2840Com_show_innodb_status 25291Com_show_keys 377Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 2Com_show_privileges 0Com_show_processlist 451839Com_show_slave_hosts 0Com_show_slave_status 23800Com_show_status 576260Com_show_storage_engines 1Com_show_tables 11806Com_show_triggers 0Com_show_variables 126824Com_show_warnings 12Com_slave_start 0Com_slave_stop 0Com_stmt_close 42500Com_stmt_execute 42500Com_stmt_fetch 0Com_stmt_prepare 42500Com_stmt_reset 3008Com_stmt_send_long_data 0Com_truncate 1Com_unlock_tables 60829Com_update 315044644Com_update_multi 12618529Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 377290889Created_tmp_disk_tables 1751286Created_tmp_files 4788Created_tmp_tables 98467670Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Handler_commit 553780791Handler_delete 40076793Handler_discover 0Handler_prepare 550409386Handler_read_first 196241092Handler_read_key 111689540591Handler_read_next 486793458770Handler_read_prev 4130967Handler_read_rnd 12614625273Handler_read_rnd_next 2460361913745Handler_rollback 105434949Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 7929786548Handler_write 144252022792Innodb_buffer_pool_pages_data 246724Innodb_buffer_pool_pages_dirty 1529Innodb_buffer_pool_pages_flushed 39777966Innodb_buffer_pool_pages_free 1Innodb_buffer_pool_pages_misc 15419Innodb_buffer_pool_pages_total 262144Innodb_buffer_pool_read_ahead_rnd 117867739Innodb_buffer_pool_read_ahead_seq 2967813Innodb_buffer_pool_read_requests 2466719156656Innodb_buffer_pool_reads 2735311046Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 1666436600Innodb_data_fsyncs 10702492Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 0Innodb_data_pending_writes 0Innodb_data_read 105373694955520Innodb_data_reads 4270800806Innodb_data_writes 36661904Innodb_data_written 1406913750016Innodb_dblwr_pages_written 39777966Innodb_dblwr_writes 686956Innodb_log_waits 0Innodb_log_write_requests 243864251Innodb_log_writes 3834773Innodb_os_log_fsyncs 4177719Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 103293077504Innodb_page_size 16384Innodb_pages_created 1597085Innodb_pages_read 6431525038Innodb_pages_written 39777966Innodb_row_lock_current_waits 0Innodb_row_lock_time 1020025605Innodb_row_lock_time_avg 110Innodb_row_lock_time_max 71936Innodb_row_lock_waits 9227174Innodb_rows_deleted 72297391Innodb_rows_inserted 73356981Innodb_rows_read 2765931802595Innodb_rows_updated 74496989Key_blocks_not_flushed 0Key_blocks_unused 896308Key_blocks_used 1838267Key_read_requests 115685330Key_reads 87329Key_write_requests 1995742Key_writes 1256391Last_query_cost 0.000000Max_used_connections 221Ndb_cluster_node_id 0Ndb_config_from_host Ndb_config_from_port 0Ndb_number_of_data_nodes 0Not_flushed_delayed_rows 0Open_files 8202Open_streams 0Open_tables 8017Opened_tables 61614Prepared_stmt_count 0Qcache_free_blocks 12510Qcache_free_memory 247980304Qcache_hits 13409828Qcache_inserts 2463254Qcache_lowmem_prunes 183996Qcache_not_cached 2191930Qcache_queries_in_cache 14613Qcache_total_blocks 42038Queries 9729567079Questions 9637939650Rpl_status NULLSelect_full_join 12098519Select_full_range_join 4296Select_range 104429270Select_range_check 0Select_scan 183690038Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 110448Sort_merge_passes 414912Sort_range 70633918Sort_rows 40111321261Sort_scan 70952027Ssl_accept_renegotiates 0Ssl_accepts 0Ssl_callback_cache_hits 0Ssl_cipher Ssl_cipher_list Ssl_client_connects 0Ssl_connect_renegotiates 0Ssl_ctx_verify_depth 0Ssl_ctx_verify_mode 0Ssl_default_timeout 0Ssl_finished_accepts 0Ssl_finished_connects 0Ssl_session_cache_hits 0Ssl_session_cache_misses 0Ssl_session_cache_mode NONESsl_session_cache_overflows 0Ssl_session_cache_size 0Ssl_session_cache_timeouts 0Ssl_sessions_reused 0Ssl_used_session_cache_entries 0Ssl_verify_depth 0Ssl_verify_mode 0Ssl_version Table_locks_immediate 7979472Table_locks_waited 17046Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 237Threads_connected 20Threads_created 1752Threads_running 6Uptime 3576291Uptime_since_flush_status 11291


    /proc/meminfo


    MemTotal: 24546100 kBMemFree: 406664 kBBuffers: 202632 kBCached: 12887608 kBSwapCached: 0 kBActive: 21878348 kBInactive: 1794060 kBHighTotal: 0 kBHighFree: 0 kBLowTotal: 24546100 kBLowFree: 406664 kBSwapTotal: 4192956 kBSwapFree: 4192668 kBDirty: 28256 kBWriteback: 24 kBAnonPages: 10438848 kBMapped: 44560 kBSlab: 274884 kBPageTables: 28604 kBNFS_Unstable: 0 kBBounce: 0 kBCommitLimit: 16466004 kBCommitted_AS: 13406952 kBVmallocTotal: 34359738367 kBVmallocUsed: 4624 kBVmallocChunk: 34359733459 kBHugePages_Total: 0HugePages_Free: 0HugePages_Rsvd: 0Hugepagesize: 2048 kB


    /proc/cpuinfo


    processor : 0vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 0cpu cores : 4apicid : 0fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4536.89clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 1vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 0cpu cores : 4apicid : 16fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4549.28clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 2vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 1cpu cores : 4apicid : 2fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 3vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 1cpu cores : 4apicid : 18fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.46clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 4vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 2cpu cores : 4apicid : 4fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 5vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 2cpu cores : 4apicid : 20fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.45clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 6vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 3cpu cores : 4apicid : 6fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.45clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 7vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 3cpu cores : 4apicid : 22fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 8vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 0cpu cores : 4apicid : 1fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 9vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 0cpu cores : 4apicid : 17fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.53clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 10vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 1cpu cores : 4apicid : 3fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 11vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 1cpu cores : 4apicid : 19fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.46clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 12vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 2cpu cores : 4apicid : 5fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4503.65clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 13vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 2cpu cores : 4apicid : 21fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 14vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 3cpu cores : 4apicid : 7fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 15vendor_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 3cpu cores : 4apicid : 23fpu : yesfpu_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant_tsc nonstop_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr popcnt lahf_lmbogomips : 4533.52clflush size : 64cache_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]




    Thanks,

    kosta

  • #2
    Are those queries really optimized?

    Why such a small buffer pool?

    I would disable query cache.

    Comment


    • #3
      Thanks. We just bumped up the innodb_files_open to about 1000 and doubled the innodb buffer pool to 8gb (was 4gb before).


      Queries are in a mixed state as we have a mixture of old application and a new one. The new one is being tuned on a weekly basis and we always send more queries for them to optimize.

      However this seems to only happen when we get overwhelmed with traffic.

      I noticed that normally we have about 10mbps inbound traffic to mysql and at that time it hit 100mbps.


      Also why do you recommend disabling the query cache? I know its only about 50% utilized but does something stick out from the info above? What will be pros/cons be against this? We have started to use memcache for some data as well.

      kosta

      Comment

      Working...
      X