June 18, 2013

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

… (within a single connection) anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a time. (Please provide specific… would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first…

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

Post: MySQL File System Fragmentation Benchmarks

March 21, 2008 By 28 Comments

… 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e’create database test1′; done; tables: 1; total… overhead for this server configuration. Going to 10000 tables however insert speed dropped 20 times. This could be because ext3 does not… see insert speed starts slower but degrades less, even though drop from 1000 to 10000 tables is dramatic as well. The read speed…

Filed Under: Benchmarks Tagged With: ,

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

February 26, 2013 By 1 Comment

…, which discusses improving bulk insert performance. At the very bottom, the page claims that you can speed up bulk loading into an… FTS_DOC_ID column, however, MySQL apparently has no problem with either of the following statements: mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, ‘john smith’); mysql> select * from dir…

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

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… MyISAM list. What happens if we take the MyISAM stopwords, insert them into a table, and configure that table to be…) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO…, we will take a look at performance. How does the speed of InnoDB FTS compare to its MyISAM counterpart, and what…

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

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… optimizations by optimizer knowing index is UNIQUE the drawback is insert buffer will not be able to be used, which can… can slow down your inserts and make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in… on vendor, will the index have any impact on the speed of the query? A: If you extend the index from…

Filed Under: Insight for DBAs, MySQL

Comment: Predicting how long data load would take

December 1, 2007 By 14 Comments

… down of the insertion speed completely. It will happen sooner or later. My question is that is this type of insertion speed drammtic slow… test on MySql 5.0 and my laptop has 2.6 GHZ CPU and 2GB Memory. I found the insertion speed slow down at about 1GB table size. I use JDBC to insert 50,000 records with one insertion. the insertion time…

Filed Under: Insight for DBAs Tagged With: ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT… affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 . . . mysql> INSERT INTO t(c) SELECT RAND() FROM t; Query OK, 2097152… Quoting the fast index creation chapter in the MySQL manual: “… you can generally speed the overall process of creating and loading an…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: MySQL Query Cache

July 27, 2006 By 70 Comments

… “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number … queries being cached this might reduce update speed a bit. Fragmentation over time – Over time …of course inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts …

Filed Under: Insight for DBAs Tagged With:

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

June 8, 2006 By 14 Comments

Bulk insert buffer can speed things significantly for bulk inserts by avoiding random IO for updating index. It is still … LOAD DATA INFILE and INSERT INTO … SELECT if target is empty MyISAM table. In such case the speed is very close. LOAD… Skipped: 0 Warnings: 0 mysql> truncate table two; Query OK, 0 rows affected (0.20 sec) mysql> insert into two select * from one…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL performance on EC2/EBS versus RDS

August 4, 2011 By 11 Comments

… high concurrency from your MySQL database. When you do, the limitations on the core counts and effective CPU speeds can be a… and logical design, you can make certain workloads, such as insert-heavy, work better in the Amazon cloud platform. But this… balance. It requires tricks, such as partitioning so all the inserts go into one partition, whose indexes fit in memory. The…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL
« Older Posts