\n
Monday, 6 August 2018<\/em><\/p>\n Hardware information\u00a0<\/strong><\/p>\n We have captured detailed information of the infrastructure (CPU, Disk<\/em>and Memory<\/em>) used for this benchmarking, This really helps anyone doing capacity planning \/ sizing of their database infrastructure.<\/p>\n CPU details (Intel(R) Xeon(R) Gold 6140 CPU @ 2.30GHz with 72 CPUs)<\/strong><\/p>\n Storage devices used for benchmarking (we used NVME SSD)<\/strong><\/p>\n Memory<\/strong><\/p>\n MySQL (we have used Percona Server\u00a05.7.22-22 with InnoDB\/XtraDB, TokuDB and RocksDB) configuration \/ system variables\u00a0<\/strong><\/p>\n We haven’t changed any of the system variables of TokuDB, RocksDB and InnoDB for performance:<\/p>\n TokuDB system variables\u00a0<\/strong><\/p>\n RocksDB system variables\u00a0<\/strong><\/p>\n TokuDB OLTP INSERT performance benchmarking using Sysbench<\/strong><\/p>\n Building Database Infrastructure for benchmarking (Percona Server with TokuDB) with INSERT operations:<\/p>\n “sbtest1” schema structure ( TokuDB storage engine with 100M rows)<\/p>\n Benchmarking TokuDB (with 100M rows) INSERT using Sysbench (oltp_insert.lua)<\/em><\/p>\n Monitoring the benchmarking<\/p>\n Result<\/strong><\/p>\n When interpreting the benchmarking results, I look for transactions \/ queries per second (in this case, it is\u00a010048.74 per sec.) and average latency (9.95 ms.) ,<\/p>\n Benchmarking OLTP INSERT performance on RocksDB using Sysbench\u00a0<\/strong><\/p>\n Step 1 – Prepare data<\/p>\n Step 2 – “sbtest1” schema structure ( RocksDB storage engine with 100M rows)<\/p>\n Step3 – Benchmarking OLTP INSERT performance on RocksDB<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n Transactions \/ Queries \u00a0(per second) –\u00a076275.15<\/p>\n Average latency (ms) –\u00a01.31<\/p>\n Benchmarking OLTP INSERT performance on InnoDB using Sysbench\u00a0<\/strong><\/p>\n Step 1 – prepare data for benchmarking<\/p>\n Step 2 –\u00a0“sbtest1” schema structure ( InnoDB storage engine with 100M rows)<\/p>\n Step3 – Benchmarking OLTP INSERT performance on InnoDB<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n Transactions \/ Queries \u00a0(per second) –\u00a023468.40<\/p>\n Average latency (ms) –\u00a04.26<\/p>\n Graphical representation of \u00a0OLTP INSERT performance in TokuDB, RocksDB and InnoDB:\u00a0<\/em><\/strong><\/p>\n Benchmarking READ-ONLY OLTP transactions (100M records using\u00a0oltp_read_only.lua) on TokuDB:<\/strong><\/p>\n Step 2- Confirm TokuDB schema is available with 100M records:<\/p>\n Step 3 – Benchmarking TokuDB OLTP READ-ONLY transaction performance:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) –\u00a0147273.50<\/p>\n Average latency (ms) –\u00a010.86<\/p>\n Benchmarking READ-ONLY OLTP transactions on RocksDB\u00a0<\/strong><\/p>\n Step 1- Build data(100M records using\u00a0oltp_read_only.lua) for benchmarking:<\/p>\n Step 2- Confirm RocksDB schema is available with 100M records:<\/p>\n Step 3 – Benchmarking RocksDB OLTP READ-ONLY transaction performance:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) –\u00a0313936.76<\/p>\n Average latency (ms) –\u00a05.09<\/p>\n Benchmarking<\/strong>\u00a0READ-ONLY OLTP transactions on InnoDB<\/strong><\/p>\n Step 1:\u00a0Build data\u00a0(100M records using\u00a0oltp_read_only.lua) for benchmarking:<\/p>\n Step 2 –\u00a0Step 2- Confirm InnoDB schema is available with 100M records:<\/p>\n Step 3 – Benchmarking InnoDB OLTP READ-ONLY transaction performance:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) –\u00a0159401.44<\/p>\n Average latency (ms) –\u00a010.03<\/p>\n Graphical representation of \u00a0OLTP READ-ONLY transactions performance in TokuDB, RocksDB and InnoDB:\u00a0<\/em><\/strong><\/p>\n Benchmarking<\/strong>\u00a0READ-WRITE OLTP transactions on TokuDB<\/strong><\/p>\n Step 1:\u00a0Build data\u00a0(100M records using\u00a0oltp_read_write.lua) for benchmarking:<\/p>\n Step 2- Confirm TokuDB schema is available with 100M records:<\/p>\n Step3 – Benchmarking OLTP READ-WRITE performance on TokuDB:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) –\u00a015748.86<\/p>\n Average latency (ms) –\u00a0126.99<\/p>\n Benchmarking<\/strong>\u00a0READ-WRITE OLTP transactions on RocksDB<\/strong><\/p>\n Step 1:\u00a0Build data\u00a0(100M records using\u00a0oltp_read_write.lua) for benchmarking:<\/p>\n Step 2- Confirm RocksDB schema is available with 100M records:<\/p>\n Step3 – Benchmarking OLTP READ-WRITE performance on RocksDB:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) –\u00a0227600.23<\/p>\n Average latency (ms) –\u00a08.79<\/p>\n Benchmarking<\/strong>\u00a0READ-WRITE OLTP transactions on InnoDB<\/strong><\/p>\n Step 1:\u00a0Build data\u00a0(100M records using\u00a0oltp_read_write.lua) for benchmarking:<\/p>\n Step 2- Confirm InnoDB schema is available with 100M records:<\/p>\n Step3 – Benchmarking OLTP READ-WRITE performance on InnoDB:<\/p>\n Interpreting results\u00a0<\/strong><\/p>\n QPS \u00a0(Queries per second) – 53477.03<\/p>\n Average latency (ms) –\u00a037.40<\/p>\n Graphical representation of \u00a0OLTP READ-WRITE transactions performance in TokuDB, RocksDB and InnoDB:\u00a0<\/em><\/strong><\/p>\n Conclusion<\/b><\/p>\n The results of benchmarking concluded RocksDB the most ideal candidate for SSD based storage infrastructure compared to InnoDB and TokuDB, The most compelling reasons for using RocksDB on SSD are performance, storage efficiency\/compression and much smaller write amplification compared to InnoDB or TokuDB.<\/p>\n","protected":false},"excerpt":{"rendered":"Performance Benchmarking \u00a0of TokuDB, RocksDB and InnoDB on Intel(R) Xeon(R) Gold 6140 CPU<\/em><\/h3>\n
root@blr1p01-pfm-008:\/home\/t-minervadb# lscpu \r\nArchitecture: x86_64\r\nCPU op-mode(s): 32-bit, 64-bit\r\nByte Order: Little Endian\r\nCPU(s): 72\r\nOn-line CPU(s) list: 0-71\r\nThread(s) per core: 2\r\nCore(s) per socket: 18\r\nSocket(s): 2\r\nNUMA node(s): 2\r\nVendor ID: GenuineIntel\r\nCPU family: 6\r\nModel: 85\r\nModel name: Intel(R) Xeon(R) Gold 6140 CPU @ 2.30GHz\r\nStepping: 4\r\nCPU MHz: 1000.000\r\nCPU max MHz: 2301.0000\r\nCPU min MHz: 1000.0000\r\nBogoMIPS: 4601.52\r\nVirtualization: VT-x\r\nL1d cache: 32K\r\nL1i cache: 32K\r\nL2 cache: 1024K\r\nL3 cache: 25344K\r\nNUMA node0 CPU(s): 0-17,36-53\r\nNUMA node1 CPU(s): 18-35,54-71\r\nFlags: 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 pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch epb invpcid_single intel_pt spec_ctrl retpoline kaiser tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm mpx avx512f rdseed adx smap clflushopt clwb avx512cd xsaveopt xsavec xgetbv1 cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts\r\n<\/pre>\n
root@blr1p01-pfm-008:\/home\/t-minervadb# lsblk -io NAME,TYPE,SIZE,MOUNTPOINT,FSTYPE,MODEL\r\nNAME TYPE SIZE MOUNTPOINT FSTYPE MODEL\r\nsda disk 446.1G LSI2208 \r\n|-sda1 part 438.7G \/ ext4 \r\n`-sda2 part 7.5G [SWAP] swap \r\nnvme0n1 disk 2.9T \/mnt ext4 Micron_9200_MTFDHAL3T2TCU \r\nnvme1n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\nnvme2n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\nnvme3n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\n`-nvme3n1p1 part 128M \r\nnvme4n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\n`-nvme4n1p1 part 128M \r\nnvme5n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\n`-nvme5n1p1 part 128M \r\nnvme6n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\n`-nvme6n1p1 part 128M \r\nnvme7n1 disk 2.9T Micron_9200_MTFDHAL3T2TCU \r\n`-nvme7n1p1 part 128M \r\n<\/pre>\n
root@blr1p01-pfm-008:\/home\/t-minervadb# free\r\n total used free shared buff\/cache available\r\nMem: 527993080 33848440 480213336 18304 13931304 492519988\r\nSwap: 7810044 0 7810044\r\nroot@blr1p01-pfm-008:\/home\/t-minervadb# \r\n<\/pre>\n
mysql> show variables like 'toku%';\r\n+-----------------------------------------+-------------------------+\r\n| Variable_name | Value |\r\n+-----------------------------------------+-------------------------+\r\n| tokudb_alter_print_error | OFF |\r\n| tokudb_analyze_delete_fraction | 1.000000 |\r\n| tokudb_analyze_in_background | ON |\r\n| tokudb_analyze_mode | TOKUDB_ANALYZE_STANDARD |\r\n| tokudb_analyze_throttle | 0 |\r\n| tokudb_analyze_time | 5 |\r\n| tokudb_auto_analyze | 30 |\r\n| tokudb_block_size | 4194304 |\r\n| tokudb_bulk_fetch | ON |\r\n| tokudb_cache_size | 270332456960 |\r\n| tokudb_cachetable_pool_threads | 0 |\r\n| tokudb_cardinality_scale_percent | 100 |\r\n| tokudb_check_jemalloc | ON |\r\n| tokudb_checkpoint_lock | OFF |\r\n| tokudb_checkpoint_on_flush_logs | OFF |\r\n| tokudb_checkpoint_pool_threads | 0 |\r\n| tokudb_checkpointing_period | 60 |\r\n| tokudb_cleaner_iterations | 5 |\r\n| tokudb_cleaner_period | 1 |\r\n| tokudb_client_pool_threads | 0 |\r\n| tokudb_commit_sync | ON |\r\n| tokudb_compress_buffers_before_eviction | ON |\r\n| tokudb_create_index_online | ON |\r\n| tokudb_data_dir | |\r\n| tokudb_debug | 0 |\r\n| tokudb_dir_cmd | |\r\n| tokudb_dir_cmd_last_error | 0 |\r\n| tokudb_dir_cmd_last_error_string | |\r\n| tokudb_dir_per_db | ON |\r\n| tokudb_directio | OFF |\r\n| tokudb_disable_hot_alter | OFF |\r\n| tokudb_disable_prefetching | OFF |\r\n| tokudb_disable_slow_alter | OFF |\r\n| tokudb_empty_scan | rl |\r\n| tokudb_enable_fast_update | OFF |\r\n| tokudb_enable_fast_upsert | OFF |\r\n| tokudb_enable_partial_eviction | ON |\r\n| tokudb_fanout | 16 |\r\n| tokudb_fs_reserve_percent | 5 |\r\n| tokudb_fsync_log_period | 0 |\r\n| tokudb_hide_default_row_format | ON |\r\n| tokudb_killed_time | 4000 |\r\n| tokudb_last_lock_timeout | |\r\n| tokudb_load_save_space | ON |\r\n| tokudb_loader_memory_size | 100000000 |\r\n| tokudb_lock_timeout | 4000 |\r\n| tokudb_lock_timeout_debug | 1 |\r\n| tokudb_log_dir | |\r\n| tokudb_max_lock_memory | 33791557120 |\r\n| tokudb_optimize_index_fraction | 1.000000 |\r\n| tokudb_optimize_index_name | |\r\n| tokudb_optimize_throttle | 0 |\r\n| tokudb_prelock_empty | ON |\r\n| tokudb_read_block_size | 65536 |\r\n| tokudb_read_buf_size | 131072 |\r\n| tokudb_read_status_frequency | 10000 |\r\n| tokudb_row_format | tokudb_zlib |\r\n| tokudb_rpl_check_readonly | ON |\r\n| tokudb_rpl_lookup_rows | ON |\r\n| tokudb_rpl_lookup_rows_delay | 0 |\r\n| tokudb_rpl_unique_checks | ON |\r\n| tokudb_rpl_unique_checks_delay | 0 |\r\n| tokudb_strip_frm_data | OFF |\r\n| tokudb_support_xa | ON |\r\n| tokudb_tmp_dir | |\r\n| tokudb_version | 5.7.22-22 |\r\n| tokudb_write_status_frequency | 1000 |\r\n+-----------------------------------------+-------------------------+\r\n67 rows in set (0.01 sec)\r\n<\/pre>\n
mysql> show variables like 'rocks%';\r\n+-------------------------------------------------+--------------------------------------------------------------------+\r\n| Variable_name | Value |\r\n+-------------------------------------------------+--------------------------------------------------------------------+\r\n| rocksdb_access_hint_on_compaction_start | 1 |\r\n| rocksdb_advise_random_on_open | ON |\r\n| rocksdb_allow_concurrent_memtable_write | OFF |\r\n| rocksdb_allow_mmap_reads | OFF |\r\n| rocksdb_allow_mmap_writes | OFF |\r\n| rocksdb_allow_to_start_after_corruption | OFF |\r\n| rocksdb_block_cache_size | 536870912 |\r\n| rocksdb_block_restart_interval | 16 |\r\n| rocksdb_block_size | 4096 |\r\n| rocksdb_block_size_deviation | 10 |\r\n| rocksdb_bulk_load | OFF |\r\n| rocksdb_bulk_load_allow_unsorted | OFF |\r\n| rocksdb_bulk_load_size | 1000 |\r\n| rocksdb_bytes_per_sync | 0 |\r\n| rocksdb_cache_index_and_filter_blocks | ON |\r\n| rocksdb_checksums_pct | 100 |\r\n| rocksdb_collect_sst_properties | ON |\r\n| rocksdb_commit_in_the_middle | OFF |\r\n| rocksdb_compact_cf | |\r\n| rocksdb_compaction_readahead_size | 0 |\r\n| rocksdb_compaction_sequential_deletes | 0 |\r\n| rocksdb_compaction_sequential_deletes_count_sd | OFF |\r\n| rocksdb_compaction_sequential_deletes_file_size | 0 |\r\n| rocksdb_compaction_sequential_deletes_window | 0 |\r\n| rocksdb_concurrent_prepare | ON |\r\n| rocksdb_create_checkpoint | |\r\n| rocksdb_create_if_missing | ON |\r\n| rocksdb_create_missing_column_families | OFF |\r\n| rocksdb_datadir | .\/.rocksdb |\r\n| rocksdb_db_write_buffer_size | 0 |\r\n| rocksdb_deadlock_detect | OFF |\r\n| rocksdb_deadlock_detect_depth | 50 |\r\n| rocksdb_debug_optimizer_no_zero_cardinality | ON |\r\n| rocksdb_debug_ttl_ignore_pk | OFF |\r\n| rocksdb_debug_ttl_read_filter_ts | 0 |\r\n| rocksdb_debug_ttl_rec_ts | 0 |\r\n| rocksdb_debug_ttl_snapshot_ts | 0 |\r\n| rocksdb_default_cf_options | compression=kLZ4Compression;bottommost_compression=kLZ4Compression |\r\n| rocksdb_delayed_write_rate | 0 |\r\n| rocksdb_delete_obsolete_files_period_micros | 21600000000 |\r\n| rocksdb_enable_bulk_load_api | ON |\r\n| rocksdb_enable_thread_tracking | ON |\r\n| rocksdb_enable_ttl | ON |\r\n| rocksdb_enable_ttl_read_filtering | ON |\r\n| rocksdb_enable_write_thread_adaptive_yield | OFF |\r\n| rocksdb_error_if_exists | OFF |\r\n| rocksdb_flush_log_at_trx_commit | 1 |\r\n| rocksdb_force_compute_memtable_stats | ON |\r\n| rocksdb_force_compute_memtable_stats_cachetime | 60000000 |\r\n| rocksdb_force_flush_memtable_and_lzero_now | OFF |\r\n| rocksdb_force_flush_memtable_now | OFF |\r\n| rocksdb_force_index_records_in_range | 0 |\r\n| rocksdb_hash_index_allow_collision | ON |\r\n| rocksdb_ignore_unknown_options | ON |\r\n| rocksdb_index_type | kBinarySearch |\r\n| rocksdb_info_log_level | error_level |\r\n| rocksdb_is_fd_close_on_exec | ON |\r\n| rocksdb_keep_log_file_num | 1000 |\r\n| rocksdb_large_prefix | OFF |\r\n| rocksdb_lock_scanned_rows | OFF |\r\n| rocksdb_lock_wait_timeout | 1 |\r\n| rocksdb_log_file_time_to_roll | 0 |\r\n| rocksdb_manifest_preallocation_size | 4194304 |\r\n| rocksdb_manual_wal_flush | ON |\r\n| rocksdb_max_background_jobs | 2 |\r\n| rocksdb_max_latest_deadlocks | 5 |\r\n| rocksdb_max_log_file_size | 0 |\r\n| rocksdb_max_manifest_file_size | 18446744073709551615 |\r\n| rocksdb_max_open_files | 512 |\r\n| rocksdb_max_row_locks | 1048576 |\r\n| rocksdb_max_subcompactions | 1 |\r\n| rocksdb_max_total_wal_size | 0 |\r\n| rocksdb_merge_buf_size | 67108864 |\r\n| rocksdb_merge_combine_read_size | 1073741824 |\r\n| rocksdb_merge_tmp_file_removal_delay_ms | 0 |\r\n| rocksdb_new_table_reader_for_compaction_inputs | OFF |\r\n| rocksdb_no_block_cache | OFF |\r\n| rocksdb_override_cf_options | |\r\n| rocksdb_paranoid_checks | ON |\r\n| rocksdb_pause_background_work | OFF |\r\n| rocksdb_perf_context_level | 0 |\r\n| rocksdb_persistent_cache_path | |\r\n| rocksdb_persistent_cache_size_mb | 0 |\r\n| rocksdb_pin_l0_filter_and_index_blocks_in_cache | ON |\r\n| rocksdb_print_snapshot_conflict_queries | OFF |\r\n| rocksdb_rate_limiter_bytes_per_sec | 0 |\r\n| rocksdb_read_free_rpl_tables | |\r\n| rocksdb_records_in_range | 0 |\r\n| rocksdb_reset_stats | OFF |\r\n| rocksdb_rpl_skip_tx_api | OFF |\r\n| rocksdb_seconds_between_stat_computes | 3600 |\r\n| rocksdb_signal_drop_index_thread | OFF |\r\n| rocksdb_sim_cache_size | 0 |\r\n| rocksdb_skip_bloom_filter_on_read | OFF |\r\n| rocksdb_skip_fill_cache | OFF |\r\n| rocksdb_sst_mgr_rate_bytes_per_sec | 0 |\r\n| rocksdb_stats_dump_period_sec | 600 |\r\n| rocksdb_store_row_debug_checksums | OFF |\r\n| rocksdb_strict_collation_check | ON |\r\n| rocksdb_strict_collation_exceptions | |\r\n| rocksdb_table_cache_numshardbits | 6 |\r\n| rocksdb_table_stats_sampling_pct | 10 |\r\n| rocksdb_tmpdir | |\r\n| rocksdb_trace_sst_api | OFF |\r\n| rocksdb_two_write_queues | ON |\r\n| rocksdb_unsafe_for_binlog | OFF |\r\n| rocksdb_update_cf_options | |\r\n| rocksdb_use_adaptive_mutex | OFF |\r\n| rocksdb_use_direct_io_for_flush_and_compaction | OFF |\r\n| rocksdb_use_direct_reads | OFF |\r\n| rocksdb_use_fsync | OFF |\r\n| rocksdb_validate_tables | 1 |\r\n| rocksdb_verify_row_debug_checksums | OFF |\r\n| rocksdb_wal_bytes_per_sync | 0 |\r\n| rocksdb_wal_dir | |\r\n| rocksdb_wal_recovery_mode | 1 |\r\n| rocksdb_wal_size_limit_mb | 0 |\r\n| rocksdb_wal_ttl_seconds | 0 |\r\n| rocksdb_whole_key_filtering | ON |\r\n| rocksdb_write_batch_max_bytes | 0 |\r\n| rocksdb_write_disable_wal | OFF |\r\n| rocksdb_write_ignore_missing_column_families | OFF |\r\n+-------------------------------------------------+--------------------------------------------------------------------+\r\n122 rows in set (0.00 sec)\r\n\r\n<\/pre>\n
InnoDB system variables\u00a0<\/strong><\/h3>\n
mysql> show variables like 'innod%';\r\n+-------------------------------------------+------------------------+\r\n| Variable_name | Value |\r\n+-------------------------------------------+------------------------+\r\n| innodb_adaptive_flushing | ON |\r\n| innodb_adaptive_flushing_lwm | 10 |\r\n| innodb_adaptive_hash_index | ON |\r\n| innodb_adaptive_hash_index_parts | 8 |\r\n| innodb_adaptive_max_sleep_delay | 150000 |\r\n| innodb_api_bk_commit_interval | 5 |\r\n| innodb_api_disable_rowlock | OFF |\r\n| innodb_api_enable_binlog | OFF |\r\n| innodb_api_enable_mdl | OFF |\r\n| innodb_api_trx_level | 0 |\r\n| innodb_autoextend_increment | 64 |\r\n| innodb_autoinc_lock_mode | 1 |\r\n| innodb_buffer_pool_chunk_size | 134217728 |\r\n| innodb_buffer_pool_dump_at_shutdown | ON |\r\n| innodb_buffer_pool_dump_now | OFF |\r\n| innodb_buffer_pool_dump_pct | 25 |\r\n| innodb_buffer_pool_filename | ib_buffer_pool |\r\n| innodb_buffer_pool_instances | 1 |\r\n| innodb_buffer_pool_load_abort | OFF |\r\n| innodb_buffer_pool_load_at_startup | ON |\r\n| innodb_buffer_pool_load_now | OFF |\r\n| innodb_buffer_pool_size | 134217728 |\r\n| innodb_change_buffer_max_size | 25 |\r\n| innodb_change_buffering | all |\r\n| innodb_checksum_algorithm | crc32 |\r\n| innodb_checksums | ON |\r\n| innodb_cleaner_lsn_age_factor | high_checkpoint |\r\n| innodb_cmp_per_index_enabled | OFF |\r\n| innodb_commit_concurrency | 0 |\r\n| innodb_compressed_columns_threshold | 96 |\r\n| innodb_compressed_columns_zip_level | 6 |\r\n| innodb_compression_failure_threshold_pct | 5 |\r\n| innodb_compression_level | 6 |\r\n| innodb_compression_pad_pct_max | 50 |\r\n| innodb_concurrency_tickets | 5000 |\r\n| innodb_corrupt_table_action | assert |\r\n| innodb_data_file_path | ibdata1:12M:autoextend |\r\n| innodb_data_home_dir | |\r\n| innodb_deadlock_detect | ON |\r\n| innodb_default_row_format | dynamic |\r\n| innodb_disable_sort_file_cache | OFF |\r\n| innodb_doublewrite | ON |\r\n| innodb_empty_free_list_algorithm | backoff |\r\n| innodb_encrypt_online_alter_logs | OFF |\r\n| innodb_encrypt_tables | OFF |\r\n| innodb_fast_shutdown | 1 |\r\n| innodb_file_format | Barracuda |\r\n| innodb_file_format_check | ON |\r\n| innodb_file_format_max | Barracuda |\r\n| innodb_file_per_table | ON |\r\n| innodb_fill_factor | 100 |\r\n| innodb_flush_log_at_timeout | 1 |\r\n| innodb_flush_log_at_trx_commit | 1 |\r\n| innodb_flush_method | |\r\n| innodb_flush_neighbors | 1 |\r\n| innodb_flush_sync | ON |\r\n| innodb_flushing_avg_loops | 30 |\r\n| innodb_force_load_corrupted | OFF |\r\n| innodb_force_recovery | 0 |\r\n| innodb_ft_aux_table | |\r\n| innodb_ft_cache_size | 8000000 |\r\n| innodb_ft_enable_diag_print | OFF |\r\n| innodb_ft_enable_stopword | ON |\r\n| innodb_ft_ignore_stopwords | OFF |\r\n| innodb_ft_max_token_size | 84 |\r\n| innodb_ft_min_token_size | 3 |\r\n| innodb_ft_num_word_optimize | 2000 |\r\n| innodb_ft_result_cache_limit | 2000000000 |\r\n| innodb_ft_server_stopword_table | |\r\n| innodb_ft_sort_pll_degree | 2 |\r\n| innodb_ft_total_cache_size | 640000000 |\r\n| innodb_ft_user_stopword_table | |\r\n| innodb_io_capacity | 200 |\r\n| innodb_io_capacity_max | 2000 |\r\n| innodb_kill_idle_transaction | 0 |\r\n| innodb_large_prefix | ON |\r\n| innodb_lock_wait_timeout | 50 |\r\n| innodb_locks_unsafe_for_binlog | OFF |\r\n| innodb_log_buffer_size | 16777216 |\r\n| innodb_log_checksums | ON |\r\n| innodb_log_compressed_pages | ON |\r\n| innodb_log_file_size | 50331648 |\r\n| innodb_log_files_in_group | 2 |\r\n| innodb_log_group_home_dir | .\/ |\r\n| innodb_log_write_ahead_size | 8192 |\r\n| innodb_lru_scan_depth | 1024 |\r\n| innodb_max_bitmap_file_size | 104857600 |\r\n| innodb_max_changed_pages | 1000000 |\r\n| innodb_max_dirty_pages_pct | 75.000000 |\r\n| innodb_max_dirty_pages_pct_lwm | 0.000000 |\r\n| innodb_max_purge_lag | 0 |\r\n| innodb_max_purge_lag_delay | 0 |\r\n| innodb_max_undo_log_size | 1073741824 |\r\n| innodb_monitor_disable | |\r\n| innodb_monitor_enable | |\r\n| innodb_monitor_reset | |\r\n| innodb_monitor_reset_all | |\r\n| innodb_numa_interleave | OFF |\r\n| innodb_old_blocks_pct | 37 |\r\n| innodb_old_blocks_time | 1000 |\r\n| innodb_online_alter_log_max_size | 134217728 |\r\n| innodb_open_files | 431 |\r\n| innodb_optimize_fulltext_only | OFF |\r\n| innodb_page_cleaners | 1 |\r\n| innodb_page_size | 16384 |\r\n| innodb_parallel_doublewrite_path | xb_doublewrite |\r\n| innodb_print_all_deadlocks | OFF |\r\n| innodb_print_lock_wait_timeout_info | OFF |\r\n| innodb_purge_batch_size | 300 |\r\n| innodb_purge_rseg_truncate_frequency | 128 |\r\n| innodb_purge_threads | 4 |\r\n| innodb_random_read_ahead | OFF |\r\n| innodb_read_ahead_threshold | 56 |\r\n| innodb_read_io_threads | 4 |\r\n| innodb_read_only | OFF |\r\n| innodb_replication_delay | 0 |\r\n| innodb_rollback_on_timeout | OFF |\r\n| innodb_rollback_segments | 128 |\r\n| innodb_show_locks_held | 10 |\r\n| innodb_show_verbose_locks | 0 |\r\n| innodb_sort_buffer_size | 1048576 |\r\n| innodb_spin_wait_delay | 6 |\r\n| innodb_stats_auto_recalc | ON |\r\n| innodb_stats_include_delete_marked | OFF |\r\n| innodb_stats_method | nulls_equal |\r\n| innodb_stats_on_metadata | OFF |\r\n| innodb_stats_persistent | ON |\r\n| innodb_stats_persistent_sample_pages | 20 |\r\n| innodb_stats_sample_pages | 8 |\r\n| innodb_stats_transient_sample_pages | 8 |\r\n| innodb_status_output | OFF |\r\n| innodb_status_output_locks | OFF |\r\n| innodb_strict_mode | ON |\r\n| innodb_support_xa | ON |\r\n| innodb_sync_array_size | 1 |\r\n| innodb_sync_spin_loops | 30 |\r\n| innodb_table_locks | ON |\r\n| innodb_temp_data_file_path | ibtmp1:12M:autoextend |\r\n| innodb_temp_tablespace_encrypt | OFF |\r\n| innodb_thread_concurrency | 0 |\r\n| innodb_thread_sleep_delay | 10000 |\r\n| innodb_tmpdir | |\r\n| innodb_track_changed_pages | OFF |\r\n| innodb_undo_directory | .\/ |\r\n| innodb_undo_log_truncate | OFF |\r\n| innodb_undo_logs | 128 |\r\n| innodb_undo_tablespaces | 0 |\r\n| innodb_use_global_flush_log_at_trx_commit | ON |\r\n| innodb_use_native_aio | ON |\r\n| innodb_version | 5.7.22-22 |\r\n| innodb_write_io_threads | 4 |\r\n+-------------------------------------------+------------------------+\r\n151 rows in set (0.00 sec)\r\n<\/pre>\n
Benchmarking OLTP INSERT performance on TokuDB, RocksDB and InnoDB<\/span><\/h3>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD --mysql-storage-engine=tokudb prepare\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 100000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n
mysql> show table status like 'sbtest1%'\\G;\r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: TokuDB\r\n Version: 10\r\n Row_format: tokudb_zlib\r\n Rows: 100000000\r\n Avg_row_length: 189\r\n Data_length: 18900000000\r\nMax_data_length: 9223372036854775807\r\n Index_length: 860808942\r\n Data_free: 18446744065817975570\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-03 23:03:35\r\n Update_time: 2018-08-03 23:23:51\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD --mysql-storage-engine=tokudb run<\/pre>\n
mysql> show full processlist\\G;\r\n*************************** 1. row ***************************\r\n Id: 106\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 49754892, '62632931051-58961919101-49940198850-21078424594-43546312816-91483171956-63147821178-73320074434-75390450161-85244468625', '72758152721-79346997448-32739052749-09956023061-33461120469')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 2. row ***************************\r\n Id: 107\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 38299901, '73492364485-17164009439-13897782190-82384134069-56725118845-05888552123-04466761496-73013947541-76946111000-82170241506', '57825848902-56599269429-55553620227-85565361679-86108748354')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 3. row ***************************\r\n Id: 108\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: closing tables\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 50461359, '82489034494-43306780333-31830745333-81619557910-15670574031-38606658735-35015531633-82686313168-29930813640-55800112343', '98734612239-15166737116-32153746057-36526618555-01917900606')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 4. row ***************************\r\n Id: 109\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 50305368, '26004165285-71866035101-19429620467-21730816230-28360163045-85578016857-31504027785-22011080750-52188150293-29047779256', '40086488864-24563838334-16649832399-35567929449-35827527600')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n\r\n*************************** 98. row ***************************\r\n Id: 203\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 50008367, '08590860349-55330969614-92736003669-70093680275-08791372163-86879862146-65906035624-31616634007-39285699730-30091204027', '03546380555-08125979095-56416888610-57364610871-45465441885')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 99. row ***************************\r\n Id: 204\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 54541565, '62284574810-41408816172-84693515960-17097326417-15199773762-35816031089-51785557714-03836189148-75055812047-57404275889', '89419445215-23758954221-31182195029-89303506158-96423989766')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 100. row ***************************\r\n Id: 205\r\n User: root\r\n Host: localhost\r\n db: test\r\n Command: Query\r\n Time: 0\r\n State: update\r\n Info: INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 49961655, '04968809340-71773840704-69257717063-97968863839-17701720758-38065324563-11587467460-13905955489-57279753705-77707929689', '02758577051-41889982054-46749141829-07683639044-92209230468')\r\n Rows_sent: 0\r\nRows_examined: 0\r\n*************************** 101. row ***************************\r\n Id: 206\r\n User: root\r\n Host: localhost\r\n db: NULL\r\n Command: Query\r\n Time: 0\r\n State: starting\r\n Info: show full processlist\r\n Rows_sent: 0\r\nRows_examined: 0\r\n101 rows in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD --mysql-storage-engine=tokudb run\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 0\r\n write: 18088064\r\n other: 0\r\n total: 18088064\r\n transactions: 18088064 (10048.74 per sec.)\r\n queries: 18088064 (10048.74 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0299s\r\n total number of events: 18088064\r\n\r\nLatency (ms):\r\n min: 0.24\r\n avg: 9.95\r\n max: 210.80\r\n 95th percentile: 22.28\r\n sum: 179905047.86\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 180880.6400\/323.88\r\n execution time (avg\/stddev): 1799.0505\/0.01\r\n<\/pre>\n
sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD --mysql-storage-engine=rocksdb prepare<\/pre>\n
mysql> show table status like 'sbtest1'\\G;\r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: ROCKSDB\r\n Version: 10\r\n Row_format: Fixed\r\n Rows: 100000000\r\n Avg_row_length: 198\r\n Data_length: 19855730417\r\nMax_data_length: 0\r\n Index_length: 750521287\r\n Data_free: 0\r\n Auto_increment: 100000001\r\n Create_time: NULL\r\n Update_time: NULL\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.01 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD --mysql-storage-engine=rocksdb run\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 0\r\n write: 137298161\r\n other: 0\r\n total: 137298161\r\n transactions: 137298161 (76275.15 per sec.)\r\n queries: 137298161 (76275.15 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0344s\r\n total number of events: 137298161\r\n\r\nLatency (ms):\r\n min: 0.29\r\n avg: 1.31\r\n max: 66.32\r\n 95th percentile: 1.67\r\n sum: 179465859.14\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 1372981.6100\/73.07\r\n execution time (avg\/stddev): 1794.6586\/0.02\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD prepare\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nInitializing worker threads...\r\n\r\nCreating table 'sbtest1'...\r\nInserting 100000000 records into 'sbtest1'\r\nCreating a secondary index on 'sbtest1'...\r\n<\/pre>\n
mysql> show table status like 'sbtest1%'\\G; \r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: InnoDB\r\n Version: 10\r\n Row_format: Dynamic\r\n Rows: 98682155\r\n Avg_row_length: 218\r\n Data_length: 21611151360\r\nMax_data_length: 0\r\n Index_length: 0\r\n Data_free: 3145728\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-04 17:14:04\r\n Update_time: 2018-08-04 17:11:01\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_insert.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD run\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 0\r\n write: 42243914\r\n other: 0\r\n total: 42243914\r\n transactions: 42243914 (23468.40 per sec.)\r\n queries: 42243914 (23468.40 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0319s\r\n total number of events: 42243914\r\n\r\nLatency (ms):\r\n min: 0.12\r\n avg: 4.26\r\n max: 1051.64\r\n 95th percentile: 21.50\r\n sum: 179801087.85\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 422439.1400\/1171.09\r\n execution time (avg\/stddev): 1798.0109\/0.01\r\n<\/pre>\n
![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.54.12-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.54.22-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.54.34-PM-678x381.png\")
<\/p>\nBenchmarking OLTP READ-ONLY transactions performance on TokuDB, RocksDB and InnoDB<\/span><\/h3>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=tokudb --mysql-user=root --mysql-password=USEYOURPASSWORD prepare<\/pre>\n
mysql> show table status like 'sbtest1%'\\G;\r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: TokuDB\r\n Version: 10\r\n Row_format: tokudb_zlib\r\n Rows: 100000000\r\n Avg_row_length: 189\r\n Data_length: 18900000000\r\nMax_data_length: 9223372036854775807\r\n Index_length: 860426496\r\n Data_free: 18446744065835135232\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-05 12:53:50\r\n Update_time: 2018-08-05 13:13:38\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=tokudb --mysql-user=root --mysql-password=USEYOURPASSWORD run \r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 231960820\r\n write: 0\r\n other: 33137260\r\n total: 265098080\r\n transactions: 16568630 (9204.59 per sec.)\r\n queries: 265098080 (147273.50 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0348s\r\n total number of events: 16568630\r\n\r\nLatency (ms):\r\n min: 1.71\r\n avg: 10.86\r\n max: 51.11\r\n 95th percentile: 13.22\r\n sum: 179951191.99\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 165686.3000\/481.89\r\n execution time (avg\/stddev): 1799.5119\/0.01\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=rocksdb --mysql-user=root --mysql-password=USEYOURPASSWORD prepare \r\n<\/pre>\n
mysql> show table status like 'sbtest%'\\G; \r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: ROCKSDB\r\n Version: 10\r\n Row_format: Fixed\r\n Rows: 100000000\r\n Avg_row_length: 198\r\n Data_length: 19855730417\r\nMax_data_length: 0\r\n Index_length: 750521333\r\n Data_free: 0\r\n Auto_increment: 100000001\r\n Create_time: NULL\r\n Update_time: NULL\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=rocksdb --mysql-user=root --mysql-password=USEYOURPASSWORD run \r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 494461100\r\n write: 0\r\n other: 70637300\r\n total: 565098400\r\n transactions: 35318650 (19621.05 per sec.)\r\n queries: 565098400 (313936.76 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0349s\r\n total number of events: 35318650\r\n\r\nLatency (ms):\r\n min: 1.80\r\n avg: 5.09\r\n max: 323.58\r\n 95th percentile: 7.70\r\n sum: 179898262.01\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 353186.5000\/2619.22\r\n execution time (avg\/stddev): 1798.9826\/0.02\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD prepare \r\n<\/pre>\n
mysql> show table status like 'sbtest1'\\G;\r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: InnoDB\r\n Version: 10\r\n Row_format: Dynamic\r\n Rows: 98650703\r\n Avg_row_length: 224\r\n Data_length: 22126002176\r\nMax_data_length: 0\r\n Index_length: 0\r\n Data_free: 3145728\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-05 17:20:48\r\n Update_time: 2018-08-05 17:18:19\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_only.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD run\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 251061874\r\n write: 0\r\n other: 35865982\r\n total: 286927856\r\n transactions: 17932991 (9962.59 per sec.)\r\n queries: 286927856 (159401.44 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0300s\r\n total number of events: 17932991\r\n\r\nLatency (ms):\r\n min: 1.66\r\n avg: 10.03\r\n max: 1478.79\r\n 95th percentile: 33.12\r\n sum: 179947481.25\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 179329.9100\/1283.20\r\n execution time (avg\/stddev): 1799.4748\/0.01\r\n<\/pre>\n
![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.59.29-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.59.39-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-12.59.50-PM-678x381.png\")
<\/p>\nBenchmarking OLTP READ-WRITE transactions performance on TokuDB, RocksDB and InnoDB<\/span><\/h3>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=tokudb --mysql-user=root --mysql-password=USEYOURPASSWORD prepare \r\n<\/pre>\n
mysql> show table status like 'sbtest1%'\\G; \r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: TokuDB\r\n Version: 10\r\n Row_format: tokudb_zlib\r\n Rows: 100000000\r\n Avg_row_length: 189\r\n Data_length: 18900000000\r\nMax_data_length: 9223372036854775807\r\n Index_length: 860645232\r\n Data_free: 18446744065834916496\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-05 22:41:43\r\n Update_time: 2018-08-05 23:01:00\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=tokudb --mysql-user=root --mysql-password=USEYOURPASSWORD run \r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 19844342\r\n write: 5669812\r\n other: 2834906\r\n total: 28349060\r\n transactions: 1417453 (787.44 per sec.)\r\n queries: 28349060 (15748.86 per sec.)\r\n ignored errors: 0 (0.00 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0668s\r\n total number of events: 1417453\r\n\r\nLatency (ms):\r\n min: 3.90\r\n avg: 126.99\r\n max: 426.41\r\n 95th percentile: 147.61\r\n sum: 179997357.31\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 14174.5300\/7.61\r\n execution time (avg\/stddev): 1799.9736\/0.02\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=rocksdb --mysql-user=root --mysql-password=USEYOURPASSWORD prepare \r\n<\/pre>\n
mysql> show table status like 'sbtest1%'\\G; \r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: ROCKSDB\r\n Version: 10\r\n Row_format: Fixed\r\n Rows: 100000000\r\n Avg_row_length: 198\r\n Data_length: 19855694789\r\nMax_data_length: 0\r\n Index_length: 750521319\r\n Data_free: 0\r\n Auto_increment: 100000001\r\n Create_time: NULL\r\n Update_time: NULL\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified\r\n\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-storage-engine=rocksdb --mysql-user=root --mysql-password=USEYOURPASSWORD run \r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 286818014\r\n write: 81910410\r\n other: 40961372\r\n total: 409689796\r\n transactions: 20474371 (11374.39 per sec.)\r\n queries: 409689796 (227600.23 per sec.)\r\n ignored errors: 12630 (7.02 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0375s\r\n total number of events: 20474371\r\n\r\nLatency (ms):\r\n min: 2.50\r\n avg: 8.79\r\n max: 402.68\r\n 95th percentile: 12.75\r\n sum: 179935638.52\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 204743.7100\/2264.14\r\n execution time (avg\/stddev): 1799.3564\/0.01\r\n\r\n<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD prepare \r\n<\/pre>\n
mysql> show table status like 'sbtest1%'\\G; \r\n*************************** 1. row ***************************\r\n Name: sbtest1\r\n Engine: InnoDB\r\n Version: 10\r\n Row_format: Dynamic\r\n Rows: 100000000\r\n Avg_row_length: 221\r\n Data_length: 21885878272\r\nMax_data_length: 0\r\n Index_length: 0\r\n Data_free: 6291456\r\n Auto_increment: 100000001\r\n Create_time: 2018-08-06 10:24:54\r\n Update_time: 2018-08-06 10:31:53\r\n Check_time: NULL\r\n Collation: latin1_swedish_ci\r\n Checksum: NULL\r\n Create_options: \r\n Comment: \r\n1 row in set (0.00 sec)\r\n\r\nERROR: \r\nNo query specified<\/pre>\n
root@blr1p01-pfm-008:\/usr\/share\/sysbench# sysbench oltp_read_write.lua --threads=100 --time=1800 --table-size=100000000 --db-driver=mysql --mysql-db=test --mysql-socket=\/var\/run\/mysqld\/mysqld.sock --mysql-user=root --mysql-password=USEYOURPASSWORD run\r\nsysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)\r\n\r\nRunning the test with following options:\r\nNumber of threads: 100\r\nInitializing random number generator from current time\r\n\r\n\r\nInitializing worker threads...\r\n\r\nThreads started!\r\n\r\nSQL statistics:\r\n queries performed:\r\n read: 67383470\r\n write: 19251931\r\n other: 9626043\r\n total: 96261444\r\n transactions: 4812938 (2673.78 per sec.)\r\n queries: 96261444 (53477.03 per sec.)\r\n ignored errors: 167 (0.09 per sec.)\r\n reconnects: 0 (0.00 per sec.)\r\n\r\nGeneral statistics:\r\n total time: 1800.0491s\r\n total number of events: 4812938\r\n\r\nLatency (ms):\r\n min: 2.28\r\n avg: 37.40\r\n max: 1177.78\r\n 95th percentile: 71.83\r\n sum: 179981855.37\r\n\r\nThreads fairness:\r\n events (avg\/stddev): 48129.3800\/110.24\r\n execution time (avg\/stddev): 1799.8186\/0.00\r\n<\/pre>\n
![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-1.13.57-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-1.14.09-PM-678x381.png\")
<\/p>\n![\"\"](\"http:\/\/minervadb.com\/wp-content\/uploads\/2018\/08\/Screen-Shot-2018-08-07-at-1.14.19-PM-678x381.png\")
<\/p>\n