Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 69 Next 9 Older Entries

Displaying posts with tag: optimization (reset)

The “Shadow Table” trick.
+0 Vote Up -0Vote Down
The need: Often there is a requirement where data in a particular table has to be processed, and the data processing might be slow, while the table might be a one that is used by your application extensively. For example, a logging table that logs page hits. Or there might be an archiving operation that has to be performed on a particular table. Archiving / processing / aggregating records, all these operations are slow and can really blog down a website, combine that with the added overhead if the table that needs to have these operations performed is one that...
Performance tuning using vertical partitioning.
+0 Vote Up -0Vote Down
Does having small data-sets really help? Of course it does! Are memory lookups faster that disk lookups. Of course ! So many times I have seen people complain about queries taking too long now, while they were not taking that long earlier. There is one big reason for this, earlier the size of data-set was small so it could fit into memory. Now that the data-set has grown large enough that it cannot fit entirely into memory, the disk seeks really have slowed down the queries significantly. What to do now? Vertical partitioning. Divide the data-set into separate data-sets vertically....
MySQL Indexes – Multi-column indexes and order of columns
+0 Vote Up -0Vote Down
The problem: Many a times people find that they create index but the query is still slow or the index is not being used by MySQL to fetch the result-set. Mostly the reason is that the index is not created properly, either not the right columns being indexed or the order of columns in the index does not match how its being used in the query. The order of index! What’s that. Well that’s what we will be discussing today. How does the order of column in the index matter? The order of columns in the index matters a lot,...
sort_buffer_size and Knowing Why
+5 Vote Up -5Vote Down

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...]
Videos of Pythian Sessions from the 2010 O’Reilly MySQL Conference and Expo
+2 Vote Up -0Vote Down

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).

TitlePresenterSlidesVideo link
(hr:min:sec)Details (Conf. site link)

Main Stage
Keynote: Under New Management: Next Steps for the CommunitySheeri K. Cabral (Pythian)N/A18:16
session 14808Ignite talk: MySQLtuner 2.0Sheeri K. Cabral (Pythian)PDF5:31N/A
Interview
Thoughts on Drizzle and

  [Read more...]
Write data asynchronously to MySQL
+0 Vote Up -0Vote Down

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.

Read the rest »

When the subselect runs faster
+1 Vote Up -1Vote Down

A few weeks ago, we had a query optimization request from one of our customer.

The query was very simple like:

PLAIN TEXT CODE:
  • SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
  • This column in the table is looks like this:

    PLAIN TEXT CODE:
  • `col1` enum('A','B','C','CD','DE','F','G','HI') default NULL
  • 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...]
    How to tell when using INFORMATION_SCHEMA might crash your database
    +8 Vote Up -2Vote Down

    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

      [Read more...]
    Follow-up To Loading CSS And JS Conditionally
    +0 Vote Up -0Vote Down

    First of all, I'd like to thank everyone who read and gave their 2 cents about the [WordPress Plugin Development] How To Include CSS and JavaScript Conditionally And Only When Needed By The Posts post. The article was well received and will hopefully spark some optimizations around loading styles and scripts.

    Here are some discussions and mentions around the web:

      [Read more...]
    Free MySQL Cluster Performance Tuning webinar – TODAY!
    Employee +2 Vote Up -0Vote Down

    MySQL Cluster Performance Tuning Best Practices

    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:

    • General Design Concepts and Guidelines
    • Schema Optimization
      • BLOB/Text vs VARBINARY/VARCHAR
      • Partition by Key
      [Read more...]
    Indexing text columns in MySQL
    +2 Vote Up -0Vote Down
    This time, I’m talking about indexes for string typed columns. In particular, I’ll show a procedure I find useful while looking for good index length values for these columns. I’ll use a sample table called people. Here’s what it looks like: mysql> desc people; +————+——————+——+—–+———+—————-+ | Field | Type | Null | Key | Default […] Related posts:
  • Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
  • Making use of procedure analyse() SELECT
  •   [Read more...]
    Log Buffer #150
    +1 Vote Up -0Vote Down

    This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

    Many people other than Dave are finding release this week. Giuseppe Maxia explains some details of MySQL’s New Release Model. Andrew Morgan announces a New MySQL Cluster Maintenance Release. Aleksandr Kuzminsky of the MySQL Performance Blog releases

      [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he provides

      [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he

      [Read more...]
    webinar on Data Reduction and Smoothing in MySQL
    Employee +0 Vote Up -0Vote Down

    If you have missed Michael McFadden's session at the last MySQL Conference, here's a chance to catch up.

    On June 11, at 17:00 UTC Michael McFadden will present at a free webinar, on the subject of Faster Data Reduction and Smoothing for Analysis & Archival in MySQL (http://www.mysql.com/news-and-events/web-seminars/display-361.html).

    Don't let the "For ISVs" distract you. This session is a collection of very practical and down to earth tips for tasks that can be in the TODO list of any DBA.

    In addition to being practical, Michael's advice is justified by rigorous statistical analysis, and the tips he

      [Read more...]
    Multi Direction Sorts and avoiding a file sort
    +0 Vote Up -0Vote Down
    There are two PRIMARY directions to sort data in SQL: Ascending (ASC) and Descending DESC.
    When these two sort definitions are put together in a single statement a filesort is produced.

    Why do we want to avoid filesorts?

    Filesorts are bad. 1st they tickle a thread based buffer called sort_buffer_size. Additionally filesorts reads the data twice, unless max_length_for_sort_data limit is reached and as a result the Filesort runs slower to reduce disk I/O. If you want filesorts to run faster at the expense of the disk increase the default max_length_for_sort_data. You can read the filesort algorithm here.

    So, here is an example

    CREATE TABLE `ABCD` (
    `A` int(10) unsigned NOT NULL default '0',
    `B` int(10) unsigned NOT NULL










      [Read more...]
    Selectivity threshold for a non-covering index
    +0 Vote Up -0Vote Down

    Assume you have a table with about 300 000 rows, and an indexed column ‘col1′ with only 9 distinct values. Now you got a query like ’select * from t1 where col1 = const’. The questions are

    - when the index is faster to full table scan and vice versa?
    - does MySQL use the optimal plan by default?

    These questions became very relevant now that QOT got server access and is able to gather various table metrics including selectivity. Besides index selectivity the threshold value obviously depends on the storage engine used, so for me it is also interesting to see how our PBXT engine compares to others in this aspect. Namely to InnoDB - an engine with similar transactional properties and MyISAM - a very fast engine for read-only scenarios.

    For the test I took


      [Read more...]
    Making use of procedure analyse()
    +1 Vote Up -0Vote Down
    SELECT Field0[,Field1,Field2,...] FROM TABLE PROCEDURE ANALYSE() is a nice tool to find out more about your table’s columns. Still, it could be improved in a lot of ways, and the stored procedure below is a starting point. It makes use of procedure analyse (though with ‘SELECT * FROM’), and modifies it’s output to include the […] No related posts.
    Presentation: Partitioning in MySQL 5.1
    +0 Vote Up -0Vote Down

    At the January 2009 Boston User Group I presented a session on the new partitioning feature in MySQL 5.1. I go through how to define partitions, how partitioning makes queries faster, the different types of partitioning and when to use each type, and the restrictions and limitations of partitioning.

    The slides are available at http://www.technocation.org/files/doc/2009_01_Partitioning.pdf. The 380.6 Mb .mov movie (1 hr 16 min) can be played directly in your browser at http://technocation.org/node/671/play or downloaded at http://technocation.org/node/671/download.

    Notes:
    The partitioning part of the MySQL Manual is at:

      [Read more...]
    MySQL Performance Optimizations
    Employee +0 Vote Up -0Vote Down
    You might be wondering what's been happening with MySQL performance since Sun arrived on the scene. The good news is that we haven't been idle. There's been general recognition that MySQL could benefit from some performance and scalability enhancements, and Sun assembled a cross-organizational team immediately after the acquisition to get started on it. We've enjoyed excellent cooperation between the engineers from both organizations.

    This kind of effort is not new for Sun - we've been working with proprietary database companies on performance for years, with source code for each of the major databases on site to help the process. In this case, the fact that the MySQL engineers are working for the same company certainly simplifies a lot of things.

    If you'd like to get some insight into what's been happening, a

      [Read more...]
    MySQL Performance Optimizations
    Employee +0 Vote Up -0Vote Down
    You might be wondering what's been happening with MySQL performance since Sun arrived on the scene. The good news is that we haven't been idle. There's been general recognition that MySQL could benefit from some performance and scalability enhancements, and Sun assembled a cross-organizational team immediately after the acquisition to get started on it. We've enjoyed excellent cooperation between the engineers from both organizations.

    This kind of effort is not new for Sun - we've been working with proprietary database companies on performance for years, with source code for each of the major databases on site to help the process. In this case, the fact that the MySQL engineers are working for the same company certainly simplifies a lot of things.

    If you'd like to get some insight into what's been happening, a

      [Read more...]
    MySQL Performance Optimizations
    Employee +0 Vote Up -0Vote Down
    You might be wondering what's been happening with MySQL performance since Sun arrived on the scene. The good news is that we haven't been idle. There's been general recognition that MySQL could benefit from some performance and scalability enhancements, and Sun assembled a cross-organizational team immediately after the acquisition to get started on it. We've enjoyed excellent cooperation between the engineers from both organizations.

    This kind of effort is not new for Sun - we've been working with proprietary database companies on performance for years, with source code for each of the major databases on site to help the process. In this case, the fact that the MySQL engineers are working for the same company certainly simplifies a lot of things.

    If you'd like to get some insight into what's been happening, a

      [Read more...]
    Mutex contention and other bottlenecks in MySQL
    +0 Vote Up -0Vote Down
    Over the last few weeks I have been doing some work on improving the concurrency performance of PBXT. The last Alpha version (1.0.03) has quite a few problems in this area.

    Most of the problems have been with r/w lock and mutex contention but, I soon discovered that MySQL has some serious problems of it's own. In fact, I had to remove some of the bottlenecks in MySQL in order to continue the optimization of PBXT.

    The result for simple SELECT performance is shown in the graph below.

    Here you can see that the gain is over 60% for 32 or more concurrent threads. Both results show the performance with the newly optimized version of PBXT. The test is running on a 2.16 MHz dual core





      [Read more...]
    How to pick indexes for order by and group by queries
    +0 Vote Up -0Vote Down
    First some of the things that you need to use and understand

    Explain Syntax

    Order by Optimization

    Group by Optimization

    Update: Updated errors.

    Now some details that are usually missed. GROUP BY does sorting unless you tell mysql not to. GROUP BY has two optimization methods, loose index scan, and tight index scan.

    Loose index scan, scans the entire table index, while tight index scan uses some sort of constraint. For large datasets that are accessed often and require some sort of group by, tight index scans are better.


    So how to pick columns to create the optimal














      [Read more...]
    Using Sphinx for Non-Fulltext Queries
    +0 Vote Up -0Vote Down

    How often do you think about the reasons why your favorite RDBMS sucks? Last few months I was doing this quite often and yes, my favorite RDBMS is MySQL. The reason why I was thinking so because one of my recent tasks at Scribd was fixing scalability problems in documents browsing.

    The problem with browsing was pretty simple to describe and as hard to fix - we have large data set which consists of a few tables with many fields with really bad selectivity (flag fields like is_deleted, is_private, etc; file_type, language_id , category_id and others). As the result of this situation it becomes really hard (if possible at all) to display documents lists like “

      [Read more...]
    Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community - Video
    +0 Vote Up -0Vote Down
    The video of one of my three sessions, "Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community", presented at MySQL Conference & Expo 2008 has been uploaded by Sheeri. I am very thankful to her for doing all the hard work and making it available.

    There are a few slides that were edited out of video because of reasons beyond my control. However, you should still be able to enjoy most of the video.

    There is one point related to this video that I would like to make: Based on my particular experience I was leading to believe that



      [Read more...]
    EXPLAIN Cheatsheet
    +0 Vote Up -0Vote Down

    At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

    For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

    * or tea, for those of us in the civilized world.

    Two basic indexing tips ...
    +0 Vote Up -0Vote Down
    Here are two basic tips for proper indexing ...Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?Put
    Tweaks for loading data into MySQL
    +0 Vote Up -0Vote Down
    A
    More progress on High Performance MySQL, Second Edition
    +0 Vote Up -0Vote Down

    Whew! I just finished a marathon of revisions. It's been a while since I posted about our progress, so here's an update for the curious readers.

    Previous 30 Newer Entries Showing entries 31 to 60 of 69 Next 9 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.