June 19, 2013

Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By 17 Comments

…at a simple cost of one network roundtrip per node (which is done in parallel to all nodes at once…row affected (0.00 sec) node2 mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.01 sec) node2 mysql> insert…

Filed Under: MySQL, XtraDB Cluster

Post: MySQL Server Memory Usage

May 17, 2006 By 21 Comments

…at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb…MySQL server will needs its own buffers. About 256K is allocated at once…larger – bulk inserts may allocate bulk_insert_buffer_size …complex queries. Sorting 10 rows will be as …

Filed Under: Insight for DBAs Tagged With: , ,

Post: SELECT LOCK IN SHARE MODE and FOR UPDATE

August 6, 2006 By 51 Comments

… locking hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE…mysql> insert into tst values(1); Query OK, 1 row affected (0.00 sec) SESSION2: mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql…ensure you get write locks for all rows at once which reduces chance of deadlocks – lock…

Filed Under: Insight for Developers Tagged With:

Post: InnoDB thread concurrency

June 5, 2006 By 4 Comments

…row; update_row; delete_row; At start of each of these methods InnoDB checks count of already entered threads, and if this count is over innodb…INSERT threads) thread thrashing could be still observed even with limited innodb_thread_concurrency. This is why innodb…

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

Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80

February 25, 2013 By 10 Comments

…Once you have your hardware, here is how to get Percona Server for MySQL up and running: Insert…at …InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row…

Filed Under: Hardware and Storage, Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , ,

Post: Optimizing InnoDB for creating 30,000 tables (and nothing else)

December 22, 2011 By 5 Comments

…at all exciting mysqltest language that will create 30,000 identical tables, insert a row…mysql-test-run) in reasonable time? Well… I can set some InnoDB options! I’m going to try the obvious first: innodb…

Filed Under: MySQL Tagged With: , , , , ,

Post: How innodb_open_files affects performance

November 18, 2009 By 4 Comments

…MySQL table_cache Innodb…inserting the row in each of 100K tables. This test ran about 180sec first time and about 260sec second time (with innodb…once) Same as with select case I could not see any measurable difference between two tested innodb…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

…once you started Innodb with innodb_force_recovery=1 you can do the following: mysql> CREATE …Innodb processes page data. The next comes trial and error approach: mysql> insert…rows or dropped table. I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb…

Filed Under: Insight for DBAs Tagged With:

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

March 4, 2013 By 7 Comments

… sec) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb… of “MySQL” might be more desirable to us than an article which only has it mentioned once. So if I look at these…

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

Post: Test Drive of Solid

September 22, 2006 By 5 Comments

…to ensure rows are locked at once so transaction could proceed modifying them without causing deadlocks. For comparion here are InnoDB sessions… 3 rows in set (0.00 sec) Test 3: Phantom rows (no phantom rows in repeatable-reads): Session1: mysql> begin; Sessino2: mysql> begin; Session1: mysql> insert…

Filed Under: Uncategorized Tagged With: ,
« Older Posts
Newer Posts »