…10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5….that, the size of the table without secondary indexes is 56G while the size of the table with secondary …you are concerned with write performance is using partitioning to reduce the size of the B+tree, having…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
… 55GB buffer pool, we would expect the primary table to simply fit. But with all 4 indexes… would hit a memory wall. Given that Memory sizes are perhaps 10 times bigger than they were…/~mdcallag/mysql-patch/mytools/annotate/head%3A/bench/ibench/iibench.py) is that it measures query performance too,…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…_size=6M & mrr_buffer_size=6M MariaDB 5.5 Hash Join Disabled w/ join_buffer_size=4M & mrr_buffer_size=4M Created_tmp_disk_tables… for the best possible performance gain. This is not entirely visible in the manual either for MariaDB or MySQL, but you need to appropriately increase read_rnd_buffer_size/mrr_buffer_size because these have an impact on MRR performance, and…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… performing the benchmark on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M MariaDB 5.5 MariaDB 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables… MRR range scans had to be performed. As in the table above you can with default buffer size of 256K, MariaDB 5.5…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `… your transactions affecting the concurrency and the performance you can disable them in two different ways…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its …/mysql/data innodb_buffer_pool_size = 2000M innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size…min 54.29 sec) Please help to improve performance and suggest if any config changes are …
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
…MySQL RDS, InnoDB engine. Our requirement is to insert data in same table and read from same table. insert into … SAME_TABLE…in same table. For 22,518,912 rows it is taking 14min ( avg_row_size is 136 …further? Is there anyway I can improve the performance? We can use OUTFILE method as suggested by…
Comment: MySQL Blob Compression performance benefits
March 15, 2012 By 17 Comments
… the compress function with my exiting table. i found that the rows column that have size 127B becomes 167B. Why this so. I am also confuse when we have to use compress/uncompress function of mysql and in which kind of application as space never matter but it really increases the performance??
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance_schema_table_handles_lostblogpercona.commanual Performance_schema_table_instances_lostblogpercona.commanual Performance…
