|Previous 30 Newer Entries||Showing entries 61 to 88|
There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?
I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.
Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits,[Read more...]
So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.
So how to recover CREATE TABLE from .frm file ?
Recovering from .frm for Innodb Table
If we simply copy .frm file back to the database we will see the following MySQL creative error message:PLAIN TEXT SQL: [Read more...]
“Standard SQL” is something you read about. All popular databases have modified version of SQL. Each database adds its own flavor and features to the standard. MySQL is no different.
Some deviations are storage engine dependent. Others are more general. Many, such as
INSERT IGNORE, are commonly used. Here’s a list of some MySQL deviations to SQL, which are not so well known.
I’ll be using MySQL’s world database for demonstration.
Assume the following query:
SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode, which selects the number of cities per country, using MySQL’s world database. It is possible to get a name for one “sample” city per country using standard SQL:
How would you expect AUTO_INCREMENT to work with MERGE tables ? Assuming INSERT_METHOD=LAST is used I would expect it to work same as in case insertion happens to the last table... which does not seems to be the case. Alternatively I would expect AUTO_INCREMENT to be based off the maximum value across all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true:PLAIN TEXT SQL:
Take a look at this:PLAIN TEXT SQL:
Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb.
But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking I advice to use it.... a lot. In fact I should say I see more systems which have ANALYZE abused - run too frequently without much need than systems which do not run ANALYZE frequently enough.
First it is worth to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for[Read more...]
It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.
In my post about gathering index statistics, I referred to
ANALYZE TABLE, and
REPAIR TABLE — but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.
In previous search benchmarks, I utilized random content generated with Drupal's devel module. In these latest benchmarks, I used an actual sanitized copy of the Drupal.org community website database, with email addresses and passwords removed. The first tests were intended to confirm that Xapian continues to perform well with large amounts of actual data. Additional tests were performed to measure the effect of various MySQL tunings and configurations. The following data was derived from several hundred benchmarks run on an Amazon AWS instance over the past week using the SearchBench module.
These tests confirm that Xapian continues to offer better search performance than Drupal's core search module. Contrary to popular belief, the data also shows that using the InnoDB storage engine for search tables significantly outperforms using the MyISAM storage engine for search tables,[Read more...]
Properties:Applicable To MyISAM Server Startup Option
--bulk_insert_buffer_size=<value>Scope Both Dynamic Yes Possible Values Integer:
Range: 0 - 4294967295 (4G)Default Value 8388608 (8M) Category Performance
This cache is used by MyISAM to optimize bulk inserts. This cache is a special tree-like structure. Bulk inserts include statements like
LOAD DATA INFILE...,
INSERT ... SELECT,
INSERT ... VALUES (...), (...), ..., (...)
Bulk inserts are often used to minimize disk writes etc and are very common in applications inserting lots of data. I often use bulk inserting using the following technique: (pseudocode)
CREATE TEMPORARY TABLE tmp_table LIKE
So, how does one gather statistics on indexes? With InnoDB it’s one thing, with MyISAM it’s another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.
ANALYZE TABLE? It’s great — very fast in InnoDB. But with MyISAM a lock occurs while scanning the table.
OPTIMIZE TABLE? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an
ALTER TABLE — it might take forever, depending on the server configuration, as
OPTIMIZE TABLE for InnoDB maps to a
ALTER TABLE tableName ENGINE=InnoDB
We all know how
REPAIR TABLE, and
OPTIMIZE TABLE work with MyISAM. However, there’s a less explored way, for MyISAM
This article provides a comparison between the MyISAM and InnoDB storage engines for MySQL. InnoDB is commonly considered to perform worse than MyISAM, but this article aims to dispel this myth by describing the differences between these engines and what makes InnoDB a good fit for many database needs.
In addition, a look at when it is better to use MyISAM and a case study of the drupal.org site provide insight for determining which engine is best for a given situation.
Here’s a rundown of Thursday (day 3) of the MySQL Conference and Expo. This day’s sessions were much more interesting to me than Wednesday’s, and in fact I wanted to go to several of them in a single time slot a couple of times.
This session was, as it sounds, a look at the internals of PBXT, a transactional storage engine for MySQL that has some interesting design techniques. I had been looking forward to this session for a while, and Paul McCullagh’s nice explanations with clear diagrams were a welcome aid to understanding how PBXT works. Unlike some of the other storage engines, PBXT is being developed in full daylight, with an emphasis on community involvement and input. (Indeed, I may be contributing to it myself, in order to make its monitoring[Read more...]
Properties:Engine(s) MyISAM Server Startup Option --key_buffer_size=<value> Scope Global Dynamic Yes Possible Values Integer
This is a global buffer where MySQL caches frequently used blocks of index data for MyISAM data. Maximum allowed size is 4GB on a 32 bit platform. Greater values are permitted for 64-bit platforms beyond MySQL 5.0.52.
Keeping this buffer to an optimal value (neither too low nor too high) contributes heavily to the performance of your MySQL server. As given in the[Read more...]
I'm pleased to announce that Tag1 Consulting has partnered up with MySQL AB (http://mysql.com/) to offer an online presentation titled "Achieving Optimal MySQL Performance For Drupal". Aiming to provide a better understanding of how to properly monitor and tune your MySQL database, the online Webinar will take place on Thursday, January 31st, 2008, at 16:00 UTC (11:00 am EST). The presentation will last 45 minutes, followed by 15 minutes for questions and answers.
We recommend two open source tools to help with the regular tuning and monitoring of your MySQL database: mysqlreport and mysqlsla. Your website is made from many complex systems. Rapid growth, changes to your site, and other systems can change the load on your MySQL database. It is important that your internal staff become familiar with using these tools and implement routine maintenance. An initial review often leads to significant improvements, and will also help you to implement a monitoring solution for your ongoing performance efforts.
One of the enhancements I added to MySQL Archiver in the recent release was listed innocently in the changelog as "Destination plugins can now rewrite the INSERT statement." Not very exciting or informative, huh? Keep reading.
|Previous 30 Newer Entries||Showing entries 61 to 88|