…How let’s connect actor.ibd from sakila database taken from some MySQL server. 0. Create empty InnoDB tablespace. 1. Create the table: mysql…records in SYS_TABLES and SYS_INDEXES. 2. Now let’s modify SPACE in InnoDB dictionary. MySQL must be stopped at this point. There is a…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…end up sending. In this case if I index the table I get the following record in the slow query log…a,sbtest b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where a… ? – see how many rows query sends after group by, distinct and aggregate functions are removed (A) – look …
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
…many things that can cause a “server stall” is a long-running transaction. If a transaction remains open for a…a while, use mk-query-digest to convert this into a query log, and examine the log. In MySQL…how a SELECT-only transaction can possibly have undo log records…
Post: devops webinar - follow up Q&A
November 6, 2012 By 5 Comments
…many…record and the size of tables must be equivalent. A full table scan could be very fast on a table of 1M but when the table…a column, moving the database first would make the old code break. How would you overcome this? A: When you plan to remove a table…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…in the table: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct UniqueCarrier): 29 1 row in…a list of the queries, followed by a response time table recording…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…, I learned how to distribute …table from each node correspond logically to a records in a Flexviews materialized view delta table…splitting a problem up into many small…MySQL storage nodes are supported. Amdahl’s law applies to the distributed processing. The results from the slowest node will place a…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
How many of you use the mysql command-line client? And did you know about the … make it executable. #!/bin/sh grep -A 1 ‘TRX HAS BEEN WAITING’ Now in your mysql session, set /tmp/lock_waits as your… TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 72 index `GEN_CLUST_INDEX` of table `test/t…
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
…table format for slow_log is CSV with no indexes typical operations like finding how many queries were accumulated in…performance – updating table with a lot of …records by physical position quickly without need to have indexes would be quite handy I also should tell log table implementation in MySQL…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…record 1, cache record 500,001 …. get record 1, miss, read from disk, expunge record 500,001, cache record… how many…a row in another table based on a text field ( called email address ). Seems simple enough right? My two tables…a really solid # of hits in memcached, but the # of writes in MySQL…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables…mysql -e’drop database test1′; mysql -e’create database test1′; done; tables: 1; total records…
