From: Hakan Kuecuekyilmaz Date: May 19 2009 8:14pm Subject: Weekly Falcon/InnoDB DBT2 Overview 2009-05-18 List-Archive: http://lists.mysql.com/falcon/747 Message-Id: <1242764088.5754.193.camel@lu0011> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Boundary_(ID_iwk4reSkOanlBoMXKV/bJg)" --Boundary_(ID_iwk4reSkOanlBoMXKV/bJg) Content-type: text/plain; charset=UTF-8 Content-transfer-encoding: 8BIT Hello benchmark fans, News for this week: * OProfile works on caneland now and shows huge percentage of CPU time spent in SyncObjects. I attached an OProfile sample from caneland. * caneland did not run 100 warehouses properly due to OS issues. caneland.mysql.com[1] numbers can be found here: https://intranet.mysql.com/~hakank/caneland/weekly_dbt2_overview/weekly_dbt2_overview.php lu0009.mysql.com[2] numbers can be found here: https://intranet.mysql.com/~hakank/lu0009/weekly_dbt2_overview/weekly_dbt2_overview.php nehalem-1.mysql.com[3] numbers can be found here: https://intranet.mysql.com/~hakank/nehalem-1/weekly_dbt2_overview/weekly_dbt2_overview.php walldorf@home[4] numbers can be found here: https://intranet.mysql.com/~hakank/walldorf/weekly_dbt2_overview/weekly_dbt2_overview.php Brief overview **************** *** caneland *** **************** -w10 -c8 -c16 -c32 -c64 -c99 F/I 42.53% 28.72% 27.77% 28.91% 28.61% -w20 -c8 -c16 -c32 -c64 -c128 -c192 F/I 45.64% 32.75% 33.49% 31.03% 27.92% 29.85% -w100 -c8 -c16 -c32 -c64 -c128 -c192 -c256 -c499 F/I 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% ************** *** lu0009 *** ************** -w10 -c8 -c16 -c32 -c64 -c99 F/I 94.05% 62.08% 46.57% 46.25% 45.54% -w20 -c8 -c16 -c32 -c64 -c128 -c192 F/I 71.89% 62.97% 57.32% 51.92% 54.00% 52.69% -w100 -c8 -c16 -c32 -c64 -c128 -c192 -c256 -c499 F/I 81.33% 74.50% 61.31% 62.33% 58.64% 57.01% 52.77% 59.15% ***************** *** nehalem-1 *** ***************** -w10 -c8 -c16 -c32 -c64 -c99 F/I 60.23% 56.25% 46.00% 46.86% 52.77% -w20 -c8 -c16 -c32 -c64 -c128 -c192 F/I 36.26% 41.07% 38.64% 39.21% 52.34% 55.98% -w100 -c8 -c16 -c32 -c64 -c128 -c192 -c256 -c499 F/I 75.54% 89.64% 91.06% 93.78% 94.47% 93.57% 91.80% 91.77% **************** *** walldorf *** **************** -w10 -c8 -c16 -c32 -c64 -c99 F/I 70.41% 64.10% 63.54% 62.59% 62.84% -w20 -c8 -c16 -c32 -c64 -c128 -c192 F/I 61.68% 56.57% 56.68% 60.25% 58.43% 61.80% -w100 -c8 -c16 -c32 -c64 -c128 -c192 -c256 -c499 F/I 94.12% 115.04% 121.29% 133.77% 137.09% 142.90% 145.12% 142.68% Todo: - Put temporary results directory of DBT2 to local disk instead of storing the data NFS.  - Remove results from crashed runs in mean value calculation. - Add more information from INFORMATION_SCHEMA tables like Falcon's system memory usage or summary of active/pending_commit/pending_completion. Also talk to Ann to get more insight to Falcon metrics. - On walldorf put falcon_serial_log_dir to SATA disk to see whether the battery backed up RAID controller is the source of huge performance gain we saw lately. - Get a Solaris SPARC and Intel/AMD machine for DBT2. Change set used: revno: 2702 revision-id: john.embretsen@stripped parent: john.embretsen@stripped parent: alik@stripped committer: John H. Embretsen branch nick: mysql-6.0-falcon-to-merge Setup: datadir is on RAID10 and logdir is on separate disk (for InnoDB). Configuration is: MYSQLD_CONFIG_BASE="--no-defaults \ --socket=/tmp/mysql.sock" MYSQLD_CONFIG_GENERAL="$MYSQLD_CONFIG_BASE \ --basedir=$FALCON \ --datadir=$DATA_DIR \ --tmpdir=/tmp \ --language=${FALCON}/sql/share/english \ --key_buffer_size=8M \ --skip-grant-tables \ --sort_buffer_size=2M \ --read_buffer_size=1M \ --table_open_cache=5120 \ --tmp_table_size=67108864 \ --thread_cache=1024 \ --query_cache_size=0 \ --query_cache_type=0 \ --max_allowed_packet=512M \ --max_connections=707 \ --log-error" MYSQLD_CONFIG_FALCON="$MYSQLD_CONFIG_GENERAL \ --skip_innodb \ --default_storage_engine=Falcon \ --falcon_io_threads=10 \ --falcon_record_memory_max=650M \ --falcon_page_cache_size=1650M \ --falcon_page_size=8k \ --falcon_serial_log_dir=$DATA_DIR \ --falcon_serial_log_buffers=100 \ --falcon_consistent_read=off" MYSQLD_CONFIG_INNODB="$MYSQLD_CONFIG_GENERAL \ --skip_falcon \ --default_storage_engine=InnoDB \ --innodb_data_home_dir=$DATA_DIR \ --innodb_data_file_path=ibdata1:10M:autoextend \ --innodb_log_group_home_dir=$LOG_DIR \ --innodb_buffer_pool_size=2300M \ --innodb_additional_mem_pool_size=20M \ --innodb_log_file_size=650M \ --innodb_log_buffer_size=16M \ --innodb_lock_wait_timeout=50 \ --innodb_file_io_threads=10 \ --innodb_flush_log_at_trx_commit=1 \ --innodb_flush_method=O_DIRECT \ --innodb_log_files_in_group=6 \ --innodb_support_xa=0 \ --innodb_doublewrite=0 \ --innodb-status-file=0 \ --skip-innodb-adaptive-hash-index \ --innodb_max_dirty_pages_pct=70 \ --innodb_thread_concurrency=0" [1] caneland.mysql.com Fedora release 10 Quad HexaCore @ 2.9GHz. Intel Xeon with 16MB L2 cache, 32GB RAM, hardware RAID 10 on 4 x 15k RPM SAS disks. [2] lu0009.mysql.com Ubuntu 8.10 Quad DualCore @ 3.4Ghz. Intel Xeon (with hyperthreading) with 16MB L2 cache, 8GB RAM, hardware RAID 10 on 4 x 15k RPM SAS disks. This machine has unofficial Intel CPUs.  [3] nehalem-1.mysql.com Ubuntu 8.10 Dual DualCore @ 2.4Ghz. Intel Xeon (with SMT) with 16MB L2 cache, 16GB RAM, RAID10 on 4 x SATA disk. [4] walldorf@home RHEL 5 Dual DualCore @ 2Ghz. Intel Xeon with 4MB L2 cache, 8GB RAM, hardware RAID 10 on 4 x 15k RPM SAS disks. Best regards, Hakan -- Hakan Küçükyılmaz, Senior Software Engineer DBTG/MySQL +49 160 98953296 Sun Microsystems GmbH Sonnenallee 1, DE-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfang Engels, Dr. Roland Boemer Vorsitz d. Aufs.rat.: Martin Haering HRB MUC 161028 49.011, 8.376 --Boundary_(ID_iwk4reSkOanlBoMXKV/bJg) Content-type: text/x-log; CHARSET=US-ASCII; name=FALCON_OPROFILE_2009-05-16_064_12.log Content-transfer-encoding: 7BIT Content-disposition: attachment; filename=FALCON_OPROFILE_2009-05-16_064_12.log CPU: CPU with timer interrupt, speed 0 MHz (estimated) Profiling through timer interrupt samples % symbol name 4117326 32.6903 SyncObject::unlock(Sync*, LockType) 1594967 12.6635 SyncObject::lock(Sync*, LockType, int) 308691 2.4509 MemMgr::blockSize(void*) 255344 2.0274 Sync::lock(LockType) 223355 1.7734 Record::addRef() 209241 1.6613 Cache::fetchPage(Dbb*, int, PageType, LockType) 199320 1.5825 MemMgr::alloc(unsigned long) 198260 1.5741 Record::getEncodedRecord() 169254 1.3438 MYSQLparse(void*) 167091 1.3266 Bdb::release() 165704 1.3156 MemFreeBlock::findNextLargest(int) 140368 1.1145 SyncObject::grantLocks() 117571 0.9335 RecordLeaf::fetch(int) 116806 0.9274 MemMgr::releaseBlock(MemBlock*) 114534 0.9094 Table::fetch(int) 112723 0.8950 MemMgrPoolAllocate(MemMgr*, unsigned long) 102881 0.8168 Bdb::incrementUseCount() 92045 0.7308 StorageInterface::decodeRecord(unsigned char*) 91987 0.7303 Bitmap::nextSet(int) 89456 0.7103 Bitmap::setSafe(int) 89310 0.7091 Bdb::addRef(LockType) 88798 0.7050 RecordScavenge::canBeRetired(Record*) 81920 0.6504 Record::poke() 73170 0.5809 filesort(THD*, TABLE*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*) 67480 0.5358 EncodedDataStream::decode() 66192 0.5255 Bdb::decrementUseCount() --Boundary_(ID_iwk4reSkOanlBoMXKV/bJg)--