…/dist/mysql-5.5.30-linux2.6-x86_ | /mnt/nfs/dist/mysql-5.6.10-linux-glibc2. | | MAX_ALLOWED_PACKET | 1048576 | 4194304 | | JOIN… now allowing MySQL to handle larger queries. Makes sense as amount of memory available is much larger these days. join_buffer_size… are mostly for good and make sense. In some cases I think MySQL 5.6 does not go far enough in those…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… the amazing MySQL manual, especially the option and variable …_idblogpercona.commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_files_…_updatesblogpercona.commanual lower_case_file_systemblogpercona.commanual lower_case_table_namesblogpercona.commanual …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero… sec) In this case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access to the…
Post: MySQL Limitations Part 4: One thread per connection
October 27, 2010 By 14 Comments
…’s seriously limiting MySQL in core use cases (links: part 1, 2, 3). This post is about the way MySQL handles connections, allocating… some cases), but rather a focus on shortcomings in the main use cases for the server. There are a lot of things MySQL…, NotABug. I’m thinking of the lack of sort-merge joins or intra-query parallelism, for example. It would be lovely…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…. Now, Valgrind does not need much introduction, especially to the MySQL server developers. The Valgrind Memcheck tool, which is synonymous with…_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql… in mutex_get_waiters/mutex_set_waiters above and similar cases. And then Helgrind would be even more useful for daily…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… that long-running transaction is doing. Of course, in some cases it’s actually easy to figure out what the long… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb….trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1…
Post: Percona MySQL Webinar: Really Large Queries: Advanced Optimization Techniques, Feb. 27
February 18, 2013 By 2 Comments
…. We will go through case studies (each case study is made from multiple real-world cases). In these cases we were often able to… barely fits your monitor screen such as a 7 table join. In this webinar, we will discuss filling the gap between… optimizing large queries • The use of denormalization • The effect of join order Interested? Register here.
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… DEFAULT CHARSET=latin1 | +——-+—————————————————————————————————–+ 1 row in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set… select * from compressed join search_set using (val) where compressed.cnt >= search_set.cnt; mysql> select * from compressed join search_set using (val… sec) mysql> delete from compressed where val=16; Query OK, 1 row affected (0.00 sec) mysql> select * from compressed join search_set…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
… and so you can run MySQL 5.0 database with MySQL 5.1 with no problems in most cases. When storage type changes, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL generally supports… JOIN evaluation in 5.0 (which could both cause different result set or make query not runnable in MySQL 5.0) In case…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… for worse. I am speaking about MySQL upgrade – version change but in a lot of cases this will happen when settings are… to the old one by using hints such as STRAIGHT_JOIN, FORCE INDEX, BIG_RESULT/SMALL_RESULT. Check whatever stats are…. In some cases the faster plan may be wrong plan from optimizer standpoint and updating stats may cause old MySQL Version to…
