… following result xyz abc 1.52MB 4.00MB(free space) after this I executed alter table abc engine=innodb and re-executed SELECT… TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0 limit 10; still the free space remains the same. that… not be defragmented. is there any other way to defragment innodb tables? thanks Kuldeep
Post: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0 You…
Post: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
… that InnoDB is not alone with its flashing problems. You can see there periodical stalls in throughput (0 throughput for…, I ran random read sync IO mode, now for 1-64 threads. The throughput: We are getting to the…’;l=’y';fs=’16′;fsb=’13′;bw=’100′;ff=’4‘;pc=’4889F0′;b=’s'; pid=’109242749016593233313…
Post: Testing STEC SSD MACH16 200GB SLC
April 25, 2012 By 7 Comments
…are two reasons for that. First, MySQL/InnoDB uses async writes, so this will …=rndwr –max-time=18000 –max-requests=0 –num-threads=$numthreads –rand-init=on –… read sync IO mode, now for 1-64 threads. The throughput: We are… benchmarks running MySQL on RAID10 over 4 STEC MACH16 cards. If you are…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…1) | 0 | 0 | 1 | | 10304 | root | localhost | dumptest | Field List | 15 | Waiting for table flush | | 0 | 0 | 4 | | 10305 | root | localhost | dumptest | Query | 0 | NULL | show processlist | 0 | 0 | 1…one way or another. If you’re just using Innodb tables and you’re not actively changing users, …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… that the disks are 4 5.4K disks in Software…1 1 1 1 1 Created_tmp_tables 1 1 1 1 1 Handler_mrr_init N/A 0 0 1 1 Handler_mrr_rowid_refills N/A N/A N/A 1 0…Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows_read 799239 914146 912318 914146 912318 Select_scan 1 1 1 1 1 Sort_scan 1 1 1 1 1…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… transaction transaction2 > INSERT INTO t VALUES(31); Query OK, 1 row affected (0.00 sec) After running the delete statement on the… ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as…
Post: ext4 vs xfs on SSD
March 15, 2012 By 23 Comments
… the MySQL 5.1 + InnoDB-plugin and later MySQL 5.5 (or equally Percona Server 5.1 and 5.5), InnoDB uses “asynchronous” IO in Linux. Let’s test “async” mode in sysbench, and now we can get: ext4 4 threads… in 4 do sysbench –test=fileio –file-total-size=${size}G –file-test-mode=rndwr –max-time=3600 –max-requests=0 –num…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…0 0 0 0 0 0 Created_tmp_tables 1 1 1 1 1 1 Handler_mrr_init N/A 112 3 1133 1 1…Innodb_pages_read 329115 355323 143808 358308 144798 144881 Innodb_rows_read 4127318 6718351 6611675 6707882 5479445 5527245 Select_scan 1 1 1 1 1 1 Sort_scan 1 1 1 1 1 1…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity = 1 and l_quantity
