June 20, 2013

Comment: Userstats patches with information schema support

August 7, 2008 By 16 Comments

…table name mysql> use information_schema; Database changed mysql> select count(*) from table_statistics\G ERROR 1064 (42000): You have an error in your SQL…

Filed Under: Percona Software Tagged With: ,

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…server…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…

Filed Under: Insight for DBAs, MySQL

Post: Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

…database agnostic. Keep in mind, at each level of partitioning only a list of servers…group by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_tmp#74082863` * IN list … BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS…

Filed Under: MySQL

Post: Shard-Query adds parallelism to queries

November 15, 2010 By 18 Comments

… Then I hit upon the idea of taking SQL constructs like IN and BETWEEN and making …advantages as demonstrated in this blog post. Many database servers can add this parallelism natively, but most of…tests the most basic aggregation (count(*)) on a range of records. This table is partitioned by month …

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

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…OS WAIT ARRAY INFO: reservation count 13569, signal count 11421 –Thread 1152170336…tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql…262144 Free buffers 0 Database pages 258053 Modified … 88021936, state: waiting for server activity Number of rows …

Filed Under: Insight for DBAs Tagged With:

Post: Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By 50 Comments

…server v5.14.2, based on kernel v1.32.2 LucidDB was 0.9.1 The table I loaded data is: CREATE TABLE… how to do that, so I generated big SQL file which contained load statements. Load of each… tried again both databases: -Q1: Count flights per day from 2000 to 2008 years SELECT DayOfWeek, count(*) AS c …

Filed Under: Benchmarks, MySQL Tagged With: , , ,

Post: Debugging problems with row based replication

May 6, 2010 By 12 Comments

…table on a MySQL master database and populate it with some data. CREATE TABLE…count(*) from t1 as empty_table_on_the_slave; +———-+ | count(*) | +———-+ |    …semi-valid SQL statements.  …server id 1  end_log_pos 340678        Table_map: `repl`.`t1` mapped to number 15 #100506 12:42:56 server…

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

Post: Sphinx search performance optimization: multi-threaded search

January 16, 2013 By 10 Comments

… require any changes to your application or database whatsoever, it is only a matter … such as ranking, ordering or even COUNT with GROUP BY and if data …server with the following basic configuration (many irrelevant details omitted): source src1 { type = mysql sql_query = SELECT id, text FROM table…

Filed Under: MySQL Tagged With: , , ,

Post: Advanced index analysis with mk-index-usage

November 11, 2010 By 2 Comments

…index usage into tables in a database, so you can query them with SQL. I logged all…my laptop to the MySQL Performance Blog server — another 30 seconds. Now I’m set …GROUP BY query_id, db, tbl HAVING COUNT(*) > 1 ) AS qv USING(query_id, db, tbl); …

Filed Under: Insight for DBAs, MySQL

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

… to reduce their database size on disk …table.  This data collection does add some overhead to your running server…table_schema as TABLE_SCHEMA, t.table_name as TABLE_NAME, i.index_name as INDEX_NAME, i.NON_UNIQUE as NON_UNIQUE, count… some clever SQL to generate the precise ALTER TABLE statements…

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