…), PARTITION p1000000 VALUES LESS THAN(1000001) ); I used MySQL 5.1.41 for my tests increasing buffer … in this case and the pattern is a bit different – the slowdown becomes drastic as we go …if we remove index on column C – the “UPDATE” part of INSERT ON DUPLICATE KEY UPDATE completes in 22 seconds …
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…they are advertised. Lets start with a bit of background – in Innodb row level locks…: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample for update; +———-+…column j was populated by md5(rand()) values) : mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…on 32 bit we …show_collations’, ’176788′ ‘Com_show_column_types’, ’0′ ‘Com_show…Com_unlock_tables’, ’102′ ‘Com_update‘, ’15146253′ ‘Com_update_multi’, ’0′ ‘Connections’, …mysql.sock pid-file=/var/run/mysql/mysql.pid datadir=/db/disk2/mysql/data log-bin=/var/log/mysql/log log-slow-queries=/var/log/mysql…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…bit. So what is duplicate index ? This is when table has multiple indexes defined on the same columns…already created. MySQL is very permissive and allows you to create many keys on the same column… …and in memory and which need to be updated on update/insert delete. Duplicate keys are bad …
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
…. The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT: mysql> insert into post123 values(1,”aaa”,”…help because the column user_id is not part of the view so would not be available even if MySQL would … will act the same as INSTEAD OF triggers. A bit ugly but can be helpful.
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… user. This might be a > bit extreem if you have thousands of… background. > Things are different with MySQL. Good design will help dictate …may add processing time to inserts, updates, and deletes but there are other … the column’s possibilities. Also, indexing the full width of very wide columns …
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
…, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what… actially related to Innodb tables in general – these are features MySQL should get for all storage engines at some point. Descending…_date” column and using it for sort. With MySQL 5.0 you even can use triggers to update it as real date updates so…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
… be open or closed. Furthermore besides MySQL table_cache Innodb maintains its own (…100.000 tables with single integer column. The process of creating tables took… More complicated tables can take a bit more. So innodb_open_files …0) go giving 550 and 380 updates/sec appropriately. Why was second time…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…1024 KB 4GB of RAM MySQL version We used MySQL 5.1.16-beta sources …only different the values of accessed column is stored in key. Interesting … to make the difference a bit better. Unfortunately InnoDB shows bad scalability… to check PBXT performance on INSERT/UPDATE queries taking into account PBXT …
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… best for parallel insert/updates) has pretty much table level locks when it comes to auto_increment columns, which is however completely separate problem which can be fixed in MySQL 5.1 Data Clustering… MySQL status I can see it is loading about 200 rows/sec and the it is still slowing down a bit as…
