…(by hinting optimizer to ignore combined index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined) where …i1 and i2 as independent columns in this case to look at more typical case: mysql [localhost] {msandbox} (test) > explain select avg(length…
Post: Filtered MySQL Replication
November 7, 2007 By 8 Comments
…big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) – there is enough … recovering from backup. There are however some cases when you really do not need events …replicate-do-table, replicate-ignore-table, replicate-wild-do-table and replicate-wild-ignore-table options which act…
Post: Why MySQL's binlog-do-db option is dangerous
May 14, 2009 By 15 Comments
… replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases… comment form.) Now you do the following: $ mysql mysql> delete from garbage.junk; mysql> use garbage; mysql> update production.users set disabled = 1 where… replicate-wild-ignore-table=garbage.%. There are still edge cases where that won’t work, but it works in more cases and has…
Comment: Database problems in MySQL/PHP Applications
November 6, 2006 By 13 Comments
…ignore-db=dummyData #binlog-ignore-db=edmunds #binlog-ignore-db=evox #binlog-ignore-db=jato #binlog-ignore-db=kbb binlog-ignore-db=mysql binlog-ignore-db=test #binlog-ignore…=’secret’; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the …
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…operation is randomly positioned, which is a worst-case scenario for spinning disks. This is also …by ratio, and told you to ignore the ratio and in fact, ignore Key_read_requests altogether. I…the results to your IO system’s capabilities. MySQL doesn’t have good instrumentation for scientifically …
Comment: New Forum Categories: Help Wanted, For Hire
March 5, 2013 By 1 Comment
…=100M #read-only # limits max_connections=1000 #old-passwords #lower_case_table_names=1 general_log = ON max_heap_table_size… = 512M innodb_open_files=2048 #Replication-skip replicate-ignore-table=mysql.gsd_runs replicate-ignore-table=mysql.gsd_monitor ======== ==> max_heap_table_size=128M
Comment: Why MySQL's binlog-do-db option is dangerous
July 24, 2012 By 15 Comments
… case in an existing setup where binlog_do_db and binlog_ignore_db do not appear as advertized in the manual. The mysql config file has: binlog_do_db=mydb binlog_ignore_db=mysql binlog_ignore_db=test But when I login using ‘mysql -u…
Post: Indexes in MySQL
June 2, 2006 By 29 Comments
… table scan is faster by 2 times. Consider more extremal case: selectivity ~95%: SELECT cnt2 / cnt1 FROM (SELECT count(*) cnt1 FROM… MySQL will use index. Execution time: SELECT COUNT(SUBNAME) FROM t2 WHERE ID1=1 – 1200 ms SELECT COUNT(SUBNAME) FROM t2 IGNORE INDEX (ID1) WHERE ID1=1 – 260 ms That is table scan is faster by 4.6 times. Why does MySQL…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… of cases where there’s a /var/lib/mysql and an /etc/my.cnf, and the server is installed in /customsoftware/mysql with…=/var/db/mysql/mysql.sock consult 28852 26909 0 19:08 pts/2 00:00:00 grep mysqld In this case, yes it… just ignore. The above process may look like a static recipe that you can just mechanically execute, and in some cases that…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix this problem for cases when… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see… in many cases can be removed for READ-COMMITED isolation mode which will start offering more performance benefits in MySQL 5.1…
