June 19, 2013

Post: MySQL 4 to MySQL 5 Upgrade performance regressions

June 6, 2007 By 8 Comments

…cases when upgrading from MySQL 4.0 and 4.1 to MySQL 5.0. By serious I mean … course you also have to have plenty of concurrent transactions so group commit would trigger in MySQL 4.0 …” stuck in “end” stage or have “commit” queries in the processlist for multiple statement transactions. Looking at …

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

… and others have no prefix, results that have the …might expect to get back multiple matches containing the words “James… dir_test_myisam ORDER BY 3 DESC LIMIT 5; +——–+——————————+——————–+ | id | full_name |…group the search terms as a single phrase: With 5.5: mysql…

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

Post: 3 ways MySQL uses indexes

September 12, 2009 By 28 Comments

…MySQL limitation – once MySQL runs into the interval range it will not use any further index parts. If you have…MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple… can also see others like using index for group by but I think they can be pretty …

Filed Under: Insight for Developers Tagged With:

Post: MySQL: Followup on UNION for query optimization, Query profiling

August 14, 2006 By 14 Comments

…: Lets say we want to show people in appropriate age group sorting by time when they were last online. If age is fixed this works great and it is efficient, however if we have multiple ages to deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain…

Filed Under: Insight for Developers Tagged With: ,

Post: When would you use SAN with MySQL ?

March 9, 2009 By 18 Comments

… buffering can too be done by MySQL and local RAID (with BBU) … SAN – if you have MySQL/server restart you may have warmup being quite …have binary log flushed on commit too. MySQL also has broken group commit (which we have…more reliable, same as server with multiple power supplies ECC memory kill-…

Filed Under: Hardware and Storage Tagged With:

Post: Facebook at Percona Live MySQL Conference and Expo and Advanced Registration Ending Soon

March 19, 2013 By Leave a Comment

…multiple datacenters. His talk entitled “High Performance Replication” will cover his field experiences of having…

Filed Under: Benchmarks, Cloud and NoSQL, Events and Announcements, Hardware and Storage, Insight for DBAs, Insight for Developers, MySQL, Percona Live, Percona Software, Percona Toolkit

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… state of Innodb scalability with multiple threads and multiple concurrent transactions ? HT: The scalability …Group commit still busted in 5.0/5.1? HT: Yes. However, work has been done on it by…have the memlock also. PZ: memlock already works – it is global MySQL Server option, though it may have…

Filed Under: Insight for Developers Tagged With: ,

Post: How adding another table to JOIN can improve performance ?

August 1, 2008 By 12 Comments

… would have a year ? 5 years ? What if you get say thousands of groups at the same time ? Performing such query MySQL has… by having all rows to have different group_id and it still does not work. The trick however does work if you have just one group… performance by joining data to yet another table though why it does not work for multiple group remains question to check with MySQL Optimizer…

Filed Under: Insight for Developers Tagged With: ,

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_…multiple EBS volumes since it is not the main purpose of this blog series. EBS based AMI Others have…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Missing Data - rows used to generate result set

July 20, 2008 By 15 Comments

… small fraction of queries. If you’re joining multiple tables or if you have GROUP BY query the number of rows which need to… if we have the same table with no indexes and query SELECT GENDER, COUNT(*) FROM PEOPLE WHERE COUNTRY=’USA’ GROUP BY GENDER even… (SELECT COUNTRY,COUNT(*) FROM PEOPLE GROUP BY COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in the…

Filed Under: Insight for Developers Tagged With:
« Older Posts
Newer Posts »