… “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned…_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately 8 million of them having role_id = 1, and 266… more time to execute: mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’\G 266 rows in…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
…Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance …and filled the table with 400 million rows. Here is the chart of rows inserted per minute (click on the … mean that we were IO-bound the whole time waiting on fsync operations. But we didn’t …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…time, I …row *************************** Name: fact Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql…million rows / 117 bytes per row / 4096 bytes per read), plus about 150 million random I/Os for the first dimension table, plus 150 million…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0 # Bytes_sent: 213…” MySQL does not count it as access to two tables. In case of “real” access to the data it will: # Time: 120911… 2 rows while scanning 10 million, while we can’t really optimize this query in a simple way because scanning all that rows…
Post: Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
… an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes… that changes are no longer hitting your database. You need time to think. Take a database backup if you can. Identify… example ONLY works when binlog_format=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in…
Post: How to estimate query completion time in MySQL
April 22, 2008 By 16 Comments
… tuning (I’ll write more about that later) I convinced MySQL to perform the query as a table scan of the… this post is that the fact table has about 150 million rows and the query was taking over 10 minutes to complete… | So the server was reading roughly 43K rows per second, and there were 150 million rows in the table. A little math later…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
…in short this table was some 25 million rows of application audit data with an…data is always available at all times (firmly ruling out off device archive),…row in set (2.04 sec) mysql> select id from compressed_row order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row…
Post: read_buffer_size can break your replication
June 6, 2012 By 12 Comments
…million rows with LOAD DATA INFILE: MasterA (test) > LOAD DATA INFILE ‘/tmp/data’ INTO TABLE t; Query OK, 4510080 rows affected (26.89 sec) After some time the …mysql-bin.000002′ at 74416925, the last event read from ‘./mysql-bin.000004′ at 171, the last byte read from ‘./mysql…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 3
October 19, 2009 By 25 Comments
… bottlenecks in MySQL/Innodb when I had enough memory but I increased the threads from 64 to 128. As time permits… ( 1.4 memcached ), mysql was 5.1 XtraDB. Each environment had 2 tables each with 2 million rows. The data was identical. memcached and Tyrant stored a comma delimited string to represent the row.  Mysql was…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… of time if you have millions of queries cache for given table. Instead we could track versions for tables (increment each time table… unsure about though as MySQL Query Cache is most useful for complex expensive queries, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries…
