… MySQL manual, especially the option …delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
… which result in low hanging fruit troubleshooting MySQL Replication Lag First fact you absolutely need… in the loop, controlling maximum amount of values per batch in multiple row insert statement or… as SELECT may end up being extremely complicated query. It is best to avoid INSERT … SELECT going …
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
…_increment) engine=innodb; # each insert does one operations to get the value: INSERT INTO option1 VALUES (NULL); # $connection->insert_id(); Option #2: Use… from 1 # each insert does two operations to get the value: UPDATE option2 SET id=@id:=id+1; SELECT @id; So which… each statement Option 1 – nest each loop in a transaction Option 2 – nest each loop in a transaction The raw results are…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… is important is that nested-loop joins on large data sets are very expensive. If MySQL supported sort-merge or hash… server. If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it’ll… problem is that MySQL doesn’t tend to perform well on a data warehousing workload. The nested-loop joins are not…
Comment: Beware: key_buffer_size larger than 4G does not work
February 14, 2007 By 17 Comments
… & * Hi got a error “out of memory” while running some SELECT query. This behavior is seen once in a blue moon…_size. For this i changed the following variables as follows: #mysql SET GLOBAL key_buffer_size=100 SET global sort_buffer… max_heap_table_size=1000 Then i ran SELECT , INSERT and UPDATE command in infinite loop hoping that key_buffer_size will get…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…mysql-user=msandbox –mysql-password=msandbox –mysql-socket=/tmp/mysql_sandbox5516.sock prepare Running Sysbench and MySQLDump. Note we run them in the loop… contents. mysql [localhost] {msandbox} (information_schema) > select concat_ws(‘.’,… to note MySQL actually uses midpoint insertion for its…
Comment: Edge-case behavior of INSERT...ODKU
October 28, 2012 By 6 Comments
…s no tomorrow due to mysql‘s gap locking. The method I use is to insert into these types of tables, involves batching up a number of rows to be inserted / updated, then selecting …into a local array (with username as the key). Loop through the first array and check for the …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…really worry for most CPUs. innodb_sync_spin_loops can be used to ballance between wasting CPU… mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select…are any records is insert buffer. Next it shows how many inserts were done in insert buffer, how many…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
….mysqlperformanceblog.com/files/benchpbxt/mysql.start and script to run whole loop of benchmarks http://www…_POINT Query: SELECT name FROM $tableName WHERE id = %d READ_KEY_POINT Query: SELECT name FROM … also interesting to check PBXT performance on INSERT/UPDATE queries taking into account PBXT way…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
… without looping inside PHP: Table: CREATE TABLE services ( id INT UNSIGNED NOT NULL, client_id INT UNSIGNED NOT NULL, KEY (id)); INSERT INTO services VALUES (1,1),(1,2),(3,5),(3,6),(3,7); SELECT id,client_id FROM…_concat: This should work faster, as we remove loop from PHP to MySQL server side. Also it can be handy to use…
