June 19, 2013

Post: Talking MySQL to Sphinx

April 19, 2009 By 4 Comments

…TABLE `sptest` ( `id` bigint(20) unsigned NOT NULL, `site_id` int(10) unsigned NOT NULL, `forum_id` int(10) unsigned NOT NULL, `author_id` int(10) unsigned NOT…not contain any data: Sphinx select max(author_id) as a ,forum_id as f from sptest where num_links=1… be able to scan through 100M+ rows on…

Filed Under: Insight for Developers Tagged With:

Post: When is MIN(DATE) != MIN(DATE) ?

September 4, 2012 By 5 Comments

…SELECT MIN(t.update_time) FROM (SELECT uid, MIN(update_time) AS “update_time” FROM foo WHERE update_time IS NOT…through…select * from foo order by update_date limit 5; +——-+———————+ | i | update_date | +——-+———————+ | 1 | 0024-06-21 10:35:55 | | 2 | 0024-06-21 10…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By 17 Comments

…1 Changed: 1 Warnings: 0 node3 mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+ | i | j | +—+——-+ | 1…

Filed Under: MySQL, XtraDB Cluster

Post: Timezone and pt-table-checksum

September 25, 2012 By 1 Comment

…through… Node2 > EDT > select * from foo.bar; +—-+————-+———————+ | id | data | date_created | +—-+————-+———————+ | 1 | node1, …will not find those rows even though pt-table-…10, 16)), 0) AS crc FROM `foo`.`bar` FORCE INDEX (`PRIMARY`) WHERE (1=1)…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Updated msl (microslow) patch, installation walk-through!

April 20, 2008 By 17 Comments

…anyone who has not yet come across … sql/sql_select.cc patching …10 minutes, depending on the hardware, you will be sitting idle and watching tons of text scrolling through… system. Locate where the original mysqld … full_join, tmp_table, tmp_table_on_disk, filesort,…to only those from every (1/log_slow_…

Filed Under: Percona Software Tagged With:

Post: How To Test Your Upgrades - pt-upgrade

December 29, 2011 By 7 Comments

… through…table with –temp-database and –temp-table is recommended, this ensures separation of the test table from your production databases and tables…not test against production servers, 1…1 1 # max 1 1 # avg 1 1 # pct_95 1 1 # stddev 0 0 # median 1 1 # Fingerprint # select * from categories where…10…

Filed Under: Insight for DBAs

Comment: Long PRIMARY KEY for Innodb tables

April 10, 2009 By 8 Comments

…1) select id from view.MediumTextView order by id limit 10 2) select id from view.MediumTextView force index (primary) limit 10 3) select id from view.MediumTextView where…table), and parse out the ids. But if it uses the secondary index, it’s much fewer data pages to parse through…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM…where it talks about creating one of these stopword tables, it simply mentions the table…through…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…table… DROP TABLE IF EXISTS `test`.`_t_new`; Dropped new table OK. Let’s go through…SELECT `id`, `guest_language`, `guest_country`, `score` FROM `test`.`t` FORCE INDEX(`guest_language`) WHERE…

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Modeling MySQL Capacity by Measuring Resource Consumptions

March 8, 2011 By 1 Comment

… per select. Wait time is often IO (which you can see separately through number …% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% no In… to 10-100 ms. The queries which are in 100us range are ones where…1 IO which was not queued (less than 10 ms) or needed more than that. Going from…

Filed Under: Insight for DBAs, MySQL
« Older Posts
Newer Posts »