…GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=” . $parent); echo “”; while ($row = mysql_fetch_assoc($result)) { if ($row['Count'] > 0…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL…matters (really!). By enforcing…groups, but also updates groups with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups…_count % 100 == 0 ) { $db->do…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
…(PXC) for MySQL. I actually had more material than I covered (by design), but… any node in the synchronous replication group from getting too far behind the…0 18 0 0 0 0 0.0 0 109 0 0 09:22:21 myclu P 3 3 node3 Sync T/T 0 27 0 0 0 0 0.0 0 109 0 0… control is in a pause state 100% of the time. We can …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…_list overhead created by concurrent updates. … to scan about 100 update transactions to…2000M innodb_log_files_in_group = 2 innodb_file_per…0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…flash card as storage. By default linkbench dataset …_page_t*, unsigned long) | | | |–100.00%– buf_LRU_…2000M innodb_log_files_in_group = 2 innodb_flush_log_at…0 performance_schema=0 #56only loose-innodb_flush_neighbors=0 loose-metadata_locks_hash_instances=256 innodb_buffer_pool_instances=16 # MySQL…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…100, 1000 records in the main table. and transactions for this workload are: $ID=monotonically increasing ID $K=rand(0,10000) // distributed by…file_size = 4G innodb_log_files_in_group = 2 innodb_log_block_size=4096…threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…GROUP BY STATE ORDER BY Total_R DESC; +———————-+———-+——-+——-+————–+ | STATE | Total_R | Pct_R | Calls | R/Call | +———————-+———-+——-+——-+————–+ | removing tmp table | 0.095135 | 55.10 | 3 | 0…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…where table_name like ‘%ssb%’ group by 1,2 ) sq order by pct_buffer_pool desc; +——————-+——————+——-+——+——+———+—————–+ | table_… 10 rows in set (0.00 sec) mysql> select sq.*, pages / ((@@innodb_buffer_pool_size / 16384)) * 100 pct_buffer_…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… (order_id) GROUP BY (customer_id), (customer_name), (DATE_FORMAT(order_date,’%Y%m’)) 1 row in set, 1 warning (0.00 sec….dashboard_customer_sales dsc ‘> group by customer_id ‘> order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view… orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query OK, 484 rows affected (0.27 sec…
