…used innodb_log_file_size=4G (innodb_log_files_in_group=2) for Percona Server. That gave 8GB in total size for log files (MySQL…query_cache_size=0 skip-name-resolve table_cache=10000 [mysql] socket=/tmp/mysql.sock (post edited by…
Post: Replaying database load with Percona Playback
October 25, 2012 By 9 Comments
…mysql-host=127.0.0.1 –mysql-user=root –mysql-schema=my_db –query-log-file=slow.log Executed 429467 queries…In the previous report we see that 324 queries returned different row numbers from 429467 queries in…updates using real data and real queries…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by…GROUP BY 1,2,3 ORDER BY NULL ) The other important optimization combines results from multiple queries together. This query…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… to consider dropping indexes in). Let’s keep using views so this dynamically updates as our schema changes over time: mysql> create view all… ‘sakila%’ group by t.table_schema, t.table_name, i.index_name; Now I can query this view to see my indexes: mysql> select… ‘sakila%’ group by t.table_schema, t.table_name, i.index_name; Now we can can see index sizing information in the all…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…updates even in the case of split brain? This is a dangerous setting in multi-master setup, but should simplify things in…in set (0.00 sec) percona2 mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
…In quite a lot of cases I’ve seen such filtering being broken by developer forgetting this restriction and running one time update query…MySQL 5.0 way to break such replication is also using multi-table update…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
…use…group commit would trigger in MySQL 4.0 and large number of transactions in total so serializing them would make MySQL…queries in “SHOW PROCESSLIST” stuck in “end” stage or have “commit” queries in…in case there are away to many updates outside of transactions in auto_commit mode. Wrap them in…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…by query where clause when it was not resolved by index HT: That is already implemented in…one of little known new features in MySQL 5.1 Q15: How …Group commit still busted in 5.0/5.1? HT: Yes. However, work has been done on it by Inaam. Q19: INNODB uses… portion of the questions. UPDATE: Second Part …
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
…by default mysql_query uses mysql_store_result C library call and buffers all result set in…using temporary” in EXPLAIN) this was not issue for given case as GROUP BY in…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…Using where | +—-+————-+——–+——-+—————+——+———+——+——+————-+ 1 row in set (0.00 sec) So as you see there are really different types of ranges in MySQL. IN…IN can’t: Lets say we want to show people in appropriate age group sorting by…
