In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”
Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.
This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral[Read more...]
Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).
(hr:min:sec)Details (Conf. site link)
I think most developers are able to cache database queries by now. But what about DML queries? Every query, connection - or in the general case - ressource needs time. So I thought a lot about how to write data as efficiently as possible into the database - in my case MySQL. Hmm...We have to take a closer look at the details and we can't choose the same asynchronously writing method for every kind of query. With kind of query I mean that it depends on what storage engine we use, the complexity of the query, should more than one record be written at once, are triggers involved and so on. Sure, the one or the other query MUST be written instantly, but most of the writing querys are stackable with no need to check if the request has succeed.
A few weeks ago, we had a query optimization request from one of our customer.
The query was very simple like:PLAIN TEXT CODE:
This column in the table is looks like this:PLAIN TEXT CODE:
The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.PLAIN TEXT CODE: [Read more...]
There are those that are very adamant about letting people know that using INFORMATION_SCHEMA can crash your database. For example, in making changes to many tables at once Baron writes:
“querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous.”
Though Baron is telling the truth here, he left out one extremely important piece of information: you can actually figure out how dangerous your INFORMATION_SCHEMA query will be, ahead of time, using EXPLAIN.
In MySQL 5.1.21 and higher, not only were optimizations made to the INFORMATION_SCHEMA, but new values were added so that EXPLAIN had better visibility into what MySQL is actually
Here are some discussions and mentions around the web:[Read more...]
Are you experiencing current performance bottlenecks in your high availability applications ? Are you designing a new mission-critical application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries?
Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance when using MySQL Cluster.
We will discuss guidelines and best practices covering the following areas: