…mysqlbinlog to extract the rest of the data from the binary logs, up until …. # my $DBHOST=”slush”; my %passwords = ( “user” => “pass”, “reporting” => “reporting”, “user-no-pw” => “”, “etc” => “etc” ); #… = $datetime if (($datetime < $dt_min) || !(defined $dt_min)); } # query type line if(s/^\s+([0-9…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… Table | Op | Msg_type | Msg_text | +——–+———-+———-+——————————————————————-+ | test…Data_length: 125452288 Max_data_length: 0 Index_length: 278839296 Data…_optimized.sql real 1m20.958s user 0m3.204s sys …no PRIMARY KEY defined, because in this…
Post: Intro to OLAP
July 12, 2010 By 10 Comments
… as a STAR or snowflake type schema. To go back to … server usually returns information to the user as a ‘pivot table’ or ‘pivot…to analyze multidimensional data using an OLAP tool. Data before pivoting: Data summarized in pivot… and customer_id. These are often defined as foreign keys from the …
Post: Why you don't want to shard.
August 6, 2009 By 34 Comments
…, today it is 128GB or even 256GB. Defining “working set” is always an interesting concept here,… faith that Yasufumi is making this better). What types of Sharding are there? Despite my cautions, if… first to ask “what shard number will this users data exist on?”. It’s a highly scalable …
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
… InnoDB can organize tablespaces. First is when all data, indexes and system buffers are stored in a … InnoDB tables, but they’re hidden from a user(you can see them in information_scheme database …of page’s latest log record FIL_PAGE_TYPE 2 current defined types are: FIL_PAGE_INDEX, FIL_PAGE_UNDO_LOG…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 28 Comments
…article, the key cache miss rate is defined as the number of misses per… tuned, and they have identical data, hardware, and workload. Even that doesn’…creating any buffer hit ratio the user desires by running silly queries …timing information, etc) apply to all types of ratio-based and counter-based…
Post: MySQL Limitations Part 1: Single-Threaded Replication
October 20, 2010 By 16 Comments
…as external processes to prefetch the data the replica is trying to modify…begin in the same order as defined in the relay log, and … currently impossible. These solutions represent different types of trade-offs. For example, solution… disruptive or different from the user‘s point of view. Solution 3…
Post: MySQL should have dynamic durability settings
April 8, 2008 By 4 Comments
…log_at_trx_commit variable which defines how durable your transactions are. …think about most Web applications different data (often updated in different transactions) have… update of the fact that user viewed certain item to implement … to implement couple of different types of commit. For example we …
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… isn’t available to most users? INSERT w/PRIMARY KEY defined: Number of rows inserted – …-requests=0 –oltp-dist-type=uniform –max-time=300 –mysql-user=root –mysql-socket=/var/lib/mysql/mysql.sock run Applicable my.cnf settings: innodb_buffer_pool_size=24G innodb_data…
