…called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number …cache efficiency. First looking at number of your selects – Com_select and see how many of them are …by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits . These are some numbers you can…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
… this application I can then compare using MySQL to using MySQL + Memcached, and then to other solutions like… key B.) Update data from that row and replace the stored contents on disk C.) Use the… allocated to memcached to be successful. This application selects a random # between 1 and 2 Million and…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
… there is considerable effort needed to select rows for modification – spread it out and have separate select and update queries. In such… in MySQL 5.0 (even if there are no spam posts) which will load slave significantly. You can replace it with: SELECT id… if application allows it. In MySQL 5.1 with row level replication you will not have selection process running on SLAVE but…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 22, 2007 By 15 Comments
….00 sec) mysql> select * from c; +—-+——+ | id | fk | +—-+——+ | 1 | 1 | | 2 | 2 | +—-+——+ 2 rows in set (0.00 sec) mysql> replace into p values (1,10); Query OK, 2 rows affected (0.00 sec) mysql> select * from c; +—-+——+ | id…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to …the auto_increment value is configurable and you can select from different algorithms using the innodb_autoinc_lock_mode….query like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead of a table…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands can be used to either replace the… indexed appropriately for your queries. Using INSERT .. SELECT for summary tables The INSERT .. SELECT approach works best when there is some sort…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as… new queries in the end you can do SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time … procedure to “normalize” query by removing comments and replacing constants with some placeholders as this would allow…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…_cluster_address=gcomm://10.116.39.76 # replace this with the IP of your first…244-33-92) requested state transfer from ‘*any*’. Selected 1 (ip-10-112-39-98)(SYNCED) …mysql –mysql-engine-trx=yes –mysql-table-engine=innodb –mysql-host=127.0.0.1 –mysql-port=3307 –mysql-user=sbtest –mysql-password…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… `i` (`i`), KEY `j` (`j`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j… curious, it did not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j… using longer strings slows down things significantly and so I replaced all numbers with their sha1() hashes which still made eq…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…it in our database: mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE `salaries` …sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where emp_no=10008; +——–+——–+————+————+ | emp_no …
