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 61 to 90 of 90

Displaying posts with tag: myisam (reset)

LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute
+0 Vote Up -0Vote Down

I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us ‘transactional flavor’.

It isn’t, and here’s why.

When we speak of a transactional database/engine, we check out its ACID compliance. Let’s break out the ACID and see what LOCK TABLES provides us with:

  • A: Atomicity. MyISAM does not provide atomicity.  If we have LOCK TABLES followed by two statements, then closed by UNLOCK TABLES, then it follows that a crash between the two statements will have the first one applied, the second one not not applied. No mechanism ensures an “all or nothing” behavior.
  • C: Consistency. An error in a statement would roll back the entire transaction in a transactional
  [Read more...]
MySQL User Group Meetings in Israel
+0 Vote Up -0Vote Down

This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).

Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in Herzlia; second one, held last week, was at Interbit (a MySQL training center) in Ramat Gan. We hope to hold these meetings on a monthly basis, and the next ones are expected to be held at Interbit.

A new (blessed) law in Israel forbids us from sending invitations for these meetings via email without prior consent of the recepient (this law has passed as means of stopping spam). We do realize there are many users out there who would be interested in these meeting. For those users: please stay tuned to

  [Read more...]
2/3 myisam_suggest: an AutoComplete tool for MySQL fulltext indices
+0 Vote Up -0Vote Down
As I’ve written in my previous post “1/3 Implementing an AutoSuggest feature using MySQL fulltext indices”, it’s possible to use the MySQL/MyISAM full-text index to extract search words for an AutoSuggest feature with great performance (because the index tree is used actually). This tool, called myisam_suggest, is my first implementation of this. Download Here: myisam_suggest.c [...]
1/3 Implementing an AutoSuggest feature using MySQL fulltext indices
+0 Vote Up -0Vote Down
The MySQL full-text index Current MySQL versions provide a full-text index (FTI) which is generally used to index and search MyISAM (the default storage engine in MySQL) tables like this: SELECT id, content FROM documents WHERE MATCH(content) AGAINST ("tes*" IN BOOLEAN MODE) Internally, every indexed (text) column of a row is splitted into its words. [...]
What is the scalable replacement for InnoDB?
+0 Vote Up -0Vote Down
A while back a Sun engineer posted an article claiming that the best way to scale MySQL is to shard your database in many instances on a single server, each of which runs in threads that individually have low performance. The Sun way has always been to get high throughput with high latency. [...]
Should you move from MyISAM to Innodb ?
+1 Vote Up -0Vote Down

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...]
Recovering CREATE TABLE statement from .frm file
+0 Vote Up -0Vote Down

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...]
    Less known SQL syntax and functions in MySQL
    +0 Vote Up -0Vote Down

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

    GROUP_CONCAT

    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: SELECT

      [Read more...]
    Two storage engines; different plans, Part II
    +0 Vote Up -0Vote Down
    In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented. We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider [...]
    Two storage engines; different plans, Part I
    +0 Vote Up -0Vote Down
    A popping question is: “Can an execution plan change for different storage engines?” The answer is “Yes”. I will present two such cases, where the MySQL optimizer will choose different execution plans, based on our choice of storage engine. We will consider MyISAM and InnoDB, the two most popular engines. The two differ in many respects, and [...]
    CACHE INDEX per partition for MyISAM
    Employee +0 Vote Up -0Vote Down
    The newest development in the partitioning code
    is WL#4571.

    This new feature makes it possible to tie a
    partition using MyISAM to a specific cache index.
    The syntax for how to do is available in the
    above worklog entry.

    We found this feature to be useful for enabling
    higher performance of parallel ALTER TABLE
    (WL#2550). When adding
    a primary key to a MyISAM table the key cache in
    MyISAM limited scalability of Parallel ALTER TABLE
    severely, so adding several key caches, essentially
    one per partition we can ensure that the ALTER TABLE
    can be fully parallelised (all other ALTER TABLE
    on MyISAM already scales perfectly).

    We also have some ideas on how to

















      [Read more...]
    innodb_file_per_table Revisited
    +0 Vote Up -0Vote Down
    In a previous post, I was trying to figure out the most optimal way to switch from two large innodb table space files to using innodb_file_per_table to take advantage of some of the benefits of this setting. I had one part of it solved, which was to stop MySQL, add innodb_file_per_table to the my.cnf, then restart, perform a "no-op" alter of "ALTER TABLE t1 ENGINE=InnoDB" which would cause the table to be re-created an it's own .ibd file. The remaining problem was how to be able to resize the huge table space files after converting all the tables to a smaller size (in my case from 10GB to 10MB).

    Someone suggested a better way:

    1. Alter all innodb tables to MyISAM
    2. Stop the server
    3. Add innodb_file_per_table to my.cnf
    4. Change innodb_data_file_path to new settings (10MB tablespaces) in my.cnf
    5. Move all innodb files (logs, data) to a backup directory
    6.








      [Read more...]
    AUTO_INCREMENT and MERGE TABLES
    +0 Vote Up -0Vote Down

    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:
  • mysql> CREATE TABLE a1(i int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);
  • Query OK, 0 rows affected (0.01 sec)
  •  
  • mysql> CREATE TABLE a2 LIKE a1;
  • Query OK, 0 rows affected (0.00 sec)
  •  
  • mysql>
  •   [Read more...]
    Can you Trust CHECK TABLE ?
    +0 Vote Up -0Vote Down

    Take a look at this:

    PLAIN TEXT SQL:
  • mysql> repair TABLE a3;
  • +---------+--------+----------+----------+
  • | TABLE   | Op     | Msg_type | Msg_text |
  • +---------+--------+----------+----------+
  • | test.a3 | repair | STATUS   | OK       |
  • +---------+--------+----------+----------+
  • 1 row IN SET (0.10 sec)
  •  
  • mysql> SELECT * FROM a3 ORDER BY i;
  • +------------+
  • | i          |
  • +------------+
  • | 2147483648 |
  • |         11 |
  • |         13 |
  • |         14 |
  •   [Read more...]
    ANALYZE: MyISAM vs Innodb
    +0 Vote Up -0Vote Down

    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...]
    MySQL Back to Basics: Analyze, Check, Optimize, and Repair
    +0 Vote Up -0Vote Down

    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 OPTIMIZE TABLE, 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.

    (more…)

    Tuning Search In Drupal 5
    +0 Vote Up -0Vote Down

    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...]
    Variable's Day Out #15: bulk_insert_buffer_size
    +0 Vote Up -0Vote Down

    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

    Description:

    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
      [Read more...]
    MyISAM Statistics Gathering
    +0 Vote Up -0Vote Down

    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.

    What about 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 ANALYZE TABLE, REPAIR TABLE, and OPTIMIZE TABLE work with MyISAM. However, there’s a less explored way, for MyISAM

      [Read more...]
    MySQL versus PostgreSQL
    +0 Vote Up -0Vote Down
    I created and ran some simple tests on mysql and postgresql to figure out which one is faster. It is already known that postgresql is more stable and reliable than mysql. pgsql has a rich set of features. It is a complete RDBMS and also supports fulltext search. All benchmarks were done on my laptop - Intel core 2 duo (2.0 GHz) with 4MB L2 cache & 2 GB ram. I have 64 Bit ubuntu system loaded
    MySQL Engines: MyISAM vs. InnoDB
    +0 Vote Up -0Vote Down

    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.

    read more

    MySQL Conference and Expo 2008, Day Three
    +0 Vote Up -0Vote Down

    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.

    Inside the PBXT Storage Engine

    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...]
    Variable's Day Out #2: key_buffer_size
    +0 Vote Up -0Vote Down

    Properties:

    Engine(s) MyISAM Server Startup Option --key_buffer_size=<value> Scope Global Dynamic Yes Possible Values Integer
    Range: 8 - 4294967295 (4 GB) Default Value 131072 (128 KB) Category Performance

    Description:

    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...]
    Maria (aka MyISAM++)
    Employee_Team +0 Vote Up -0Vote Down

    Monty has started blogging and has announced the Early Release of the sources and Specs for Maria (aka MyISAM++). Also check out comments from Guiseppe (the Data Charmer) and from the Performance blog.

    Maria and Falcon are the two newest

      [Read more...]
    Achieving Optimal MySQL Performance for Drupal
    +0 Vote Up -0Vote Down

    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.

    read more

    MySQL Monitoring and Tuning
    +0 Vote Up -0Vote Down

    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.

    read more

    MySQL Archiver can now archive each row to a different table
    +0 Vote Up -0Vote Down

    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.

    how to undelete rows from a fixed length myisam table
    Employee +0 Vote Up -0Vote Down
    You have inadvertently deleted some rows from your table and want them back. This is semi-doable with fixed row format of MyISAM. I put together a few steps, which I'll compliment with a program that does it for you, later.

    • save output of SHOW TABLE STATUS LIKE 't1' and SHOW CREATE TABLE `t1`.
    • shutdown mysql server asap!
    • backup t1.frm, t1.MYI, t1.MYD immediately
    • create a new table t1_recover, which has no auto-inc and no unique/pk.
    • remove the files t1_recover.MYD and t1_recover.MYI
    • write a C program that scans through t1.MYD reading blocks of length Avg_row_length and checking if first byte indicates a row is marked as deleted.
    • if it's deleted, dump it as is into the file t1_recover.MYD
    • goto mysql prompt. issue REPAIR TABLE `t1_recover` USE_FRM
    Some notes.

    You cannot




      [Read more...]
    That error 127 MyISAM bug finally...
    Employee +0 Vote Up -0Vote Down
    Gotcha: Bug #29838

    The bug itself seems so simple to repeat. I'm boggling how it was not seen before. :-0 I really tried too many overly-complex tests, IMHO. Sometimes, it's still best to keep it simple!
    CIA's database
    +0 Vote Up -0Vote Down
    Hey look! CIA uses MySQL:

    http://cia.navi.cx/info
    Previous 30 Newer Entries Showing entries 61 to 90 of 90

    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.