…, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite… enough space on this SSD to run this experiment that long. So there we see the strong side of TokuDB: it…, $ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… close as possible to Dimitri’s environment. To put a long story short, the results are fairly consistent with our original… cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside… transactions become obvious. In the following test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… the ‘Insert’ part. 1. With a standard numeric key id, set as a Primary Key, it will probably be quicker, or the same… …latest writes etc ), it will probably be quicker in the long term to read from a partitioned table, as, depending on… the partition, not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across partitions, will be…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a… node we can have all sorts modifications floating around as long as they don’t touch the same row. Row locks… data durability isn’t as crucial. This is fine as long as you weigh both options carefully. But in Galera you…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… random primary key BTREE updates which can be very expensive. Today I would like to share practical example when you may use long primary key…, as primary key BTREE contains all table data in leafs anyway. Top level pages can get a bit larger for long primary keys but usually it is not major. Non Sequential primary key This just comes as an effect of choosing url as the key…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
…. Clustering. I love Innodb’s ability to cluster data by primary key it is so helpful in many cases, but in other… inconvenient. Having independent CLUSTER=key option would be great, it also would help to avoid penalty of very long primary key – you can simply select not to cluster by it instead of creating fake primary key and promoting real…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If…: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row… may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys Latin1 (I guess any…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… is well known it is important to load data in primary key order into Innodb tables for optimal performance, but effect does… bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool… we loaded data in parallel and our rows were relatively long. In most cases I would expect load speed to be…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
… but for MyISAM can also help you to size your key buffer compared to operating system cache. I also use it… simply unused indexes among them) or may be there is long primary key with Innodb tables. Of course it also could be perfectly…
Comment: Duplicate indexes and redundant indexes
August 13, 2008 By 38 Comments
[...] to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may be there is long primary key with Innodb tables. Of [...]
