…. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5…log_file_size = 1G innodb_log_files_in_group = 4 innodb_buffer_pool_instances = 4 innodb_adaptive… B+tree, having multiple buffer pool instances to reduce contention problems caused by buffer pool mutexes, using…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… case you could be reading the same page multiple times into the buffer pool. So considering this …_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT …at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=…
Post: What would make MySQL Multiple Queries Usable ?
September 5, 2007 By 1 Comment
… and not significant gains in most cases. What would make MySQL Multiple Queries API more usable ? Allow to run queries in parallel…, as well as same set grouped by site (to show sites with most matches) as well to group by date to show history of matches per day. This could be very well optimized by performing search query…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to the… BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping and filtering is…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 6 Comments
… run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3…(*)` FROM words2 AS `words2` GROUP BY 1,2,3 ORDER BY NULL ) The other important optimization combines results from multiple queries together. This query…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…are created by FlexCDC. The refresh algorithm computes changes in multiple transactions… as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; … <-- too high +------------------+ 1 row in set (0.68 sec) mysql> select sum(total_lines) from dashboard_customer_…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… important. Sphinx mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit….47 sec) MySQL mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit… comes from its ability to scale almost linearly using multiple CPU cores and multiple nodes in the system. The raw scan speed…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…99.9% of the work, due to the multiple levels of result set reduction. Finally, Shard-Query…resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies…id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`,…
Post: Two Types of MySQL Users
May 10, 2010 By 5 Comments
…got number number of comments saying if MySQL would not have multiple storage engine interface it would not…have unique problems which can’t be solved by conventional problems (and a lot more people …not attend MySQL Conference and may not even know Oracle owns MySQL these days. This group is often …
Post: High availability for MySQL on Amazon EC2 – Part 2 - Setting up the initial instances
June 29, 2010 By 9 Comments
…:~$ ec2-add-group hamysql -d ‘nodes for HA MySQL solution’ GROUP hamysql nodes for HA MySQL solution From now… one line the parameters I need separated by a delimiter. yves@yves-laptop:~$ cat filtre_instances… manager MySQL, just the host running it. I’ll also skip the raid configuration of multiple …
