June 19, 2013

Post: Is your MySQL buffer pool warm? Make it sweat!

April 16, 2013 By 6 Comments

…| percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name…to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You …. This is necessary when running a large stream of queries. Benchmarks We benchmarked …

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

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… with InnoDB full-text search), that’s largely where the similarities end. NOTE 1: I …some additional data into the table, run an OPTIMIZE or two, delete some data, and see what happens: mysql> … sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> …

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

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…MySQL…delete…data_pending_readsblogpercona.commanual Innodb_data_pending_writesblogpercona.commanual Innodb_data_readblogpercona.commanual Innodb_data_readsblogpercona.commanual Innodb_data_writesblogpercona.commanual Innodb_data…

Filed Under: Insight for DBAs, MySQL

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually …Data records: 3 – Fixing index 1 Data records: 6 And then, back in mysql: mysql> use test Database changed mysql> …0 mysql> delete from test_concat_part where id = 44; Query OK, 1 row affected (0.00 sec) mysql…

Filed Under: MySQL

Post: Resyncing table on MySQL Slave

June 29, 2008 By 11 Comments

…) mysql> load data infile ‘/tmp/rep.txt’ into table rep_new; Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 mysql> rename table rep_maint to rep_old, rep… master and slave. If you need quick resync of large amount of data it is much better to use LVM snapshots or…

Filed Under: Insight for DBAs Tagged With:

Post: Data mart or data warehouse?

July 15, 2010 By 14 Comments

… with and without aggregation over multiple MySQL storage engines at various data scales. What is a data warehouse? It turns out that this… only”. Data is very likely never updated or deleted in these databases and they are expected to grow to very large sizes, usually… where they maintain a large historical data warehouse, but they also build data marts for OLAP analysis from the warehouse data. A different approach…

Filed Under: Insight for Developers, MySQL

Post: Filling the tmp partition with persistent connections

August 6, 2012 By 14 Comments

…has MySQL opened: # lsof -p 6112|grep ML mysqld 6112 root 38u REG 7,0 106594304 18 /tmp/MLjw4ecJ (deleted… you run a big UPDATE, INSERT or LOAD DATA INFILE MySQL send the rows to the Binary Log Cache … to STATEMENT just before the execution of those large queries. It’s also worth to mention that …

Filed Under: MySQL

Post: Performance impact of complex queries

January 17, 2007 By 11 Comments

… is often underestimated is impact of MySQL Performance by complex queries on large data sets(ie some large aggregate queries) and batch jobs. It… which crunches large amount of data set wipes data from your normal working set from OS cache. Operation Systems and MySQL Itself employs various… you delete things do DELETE … LIMIT 1000 and insert sleep 10; in between. By spreading load this way you make sure large portion…

Filed Under: Insight for Developers Tagged With:

Post: MySQL Server Variables - SQL layer or Storage Engine specific.

June 8, 2006 By 14 Comments

…mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM if they are large…insert to work also if tables have “holes” (deleted rows) which can help significantly. delay_key_… Buffer used to buffer Index blocks (row data is not cached). Used only for MyISAM tables…

Filed Under: Insight for DBAs Tagged With: ,

Post: Fighting MySQL Replication Lag

September 22, 2008 By 7 Comments

… transaction containing millions of simple updates instead of one large update to help MySQL replication lag it is not going to work… update/delete with LIMIT in the loop, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you’re planning to update/delete and having multiple queries to delete it (see example below) This…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts