Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 61 to 70 of 103 10 Older Entries

Displaying posts with tag: myisam (reset)

Quick comparison of MyISAM, Infobright, and MonetDB
+4 Vote Up -0Vote Down

Recently I was doing a little work for a client who has MyISAM tables with many columns (the same one Peter wrote about recently). The client's performance is suffering in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of columns), but they're ad-hoc (can access any columns) and it seems tailor-made for a column-oriented database.

I decided it was time to actually give Infobright a try. They …

  [Read more...]
Backup MySQL in a Second with ZFS
Employee +1 Vote Up -1Vote Down

MySQL backup soon becomes an important matter when the database is used in production. The pain-point comes from the fact that while backuping the database is not available to respond to client requests anymore. With mysqldump - the standard tool for performing MySQL backups - and a large database the operation can go over many tenth of minutes if not hours. If I am running my business on line this is simply not acceptable.

The classical approach to workaround this problem is to take advantage of MySQL replication. I set up a master/slave configuration where the slave acts as copy of the master. Then, when needed, I run mysqldump on the …

  [Read more...]
How number of columns affects performance ?
+4 Vote Up -0Vote Down

It is pretty understood the tables which have long rows tend to be slower than tables with short rows. I was interested to check if the row length is the only thing what matters or if number of columns we have to work with also have an important role. I was interested in peak row processing speed so I looked at full table scan in case data fits in OS cache completely. I created 3 tables - First containing single tinyint column which is almost shortest type possible (CHAR(0) could be taking less space), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the same row length but have …

  [Read more...]
There is more than one way to do it….
+3 Vote Up -2Vote Down

I spent Friday examining the systems for a more traditional consulting gig (in case you did not know, Pythian has offered more traditional consulting and emergency services in addition to our remote DBA offering for our entire 12 year history). It is a familiar story to many people — the database performance was unacceptable.

The company had a few log tables that had huge amounts of inserts and foreign keys, so they used InnoDB. Unfortunately, they also used a GUID as a primary key (which is getting more and more common these days, but long primary keys with InnoDB slow down INSERT, …

  [Read more...]
What to do with MySQL Full Text Search while migrating to Innodb ?
+2 Vote Up -0Vote Down

It is rather typical for systems to start as MyISAM but as system growths to move to Innodb. The reason of the move could be just desire for better data consistency guaranty or being bitten repairing multiple GB MyISAM table few times, though Table Locks is probably the most important issue - with modern multi core servers not only the fact you can't well mix SELECTs and UPDATEs but also the fact only one update can be happening at the time can be the problem, not to mention …

  [Read more...]
On mysql's myisam_block_size setting
+2 Vote Up -0Vote Down

There is a little-known setting, myisam_block_size in MySQL. This affects the block size used in the indexes of MyISAM tables stored in .MYI files on disc and in the key buffer.

The default value is 1k, this is possibly too small for best performance on modern systems; in particular, many filesystems used a bigger block size, so writing a single index block requires a read followed by a write. Random reads are really slow on hard discs (writes are mostly fast as they go into your battery-backed raid controller which has lots of RAM).

I am currently in the process of experimenting with myisam_block_size, and so far have …



  [Read more...]
Blame it on MyISAM
+3 Vote Up -0Vote Down

I reviewed most of the changes from the v4 Google patch today. My head hurts now. During this review I checked whether bugs fixed in the patch have also been fixed in recent releases of official MySQL. I am happy that most of them have been fixed. But some changes will never be accepted, such as the one that added support for INF for FLOAT/DOUBLE columns.

The default value of sql_mode is the empty string. You probably want to change that before your applications come to depend on it. When it is the empty string, invalid values are coerced to valid values on INSERT …

  [Read more...]
Using the Sphinx Search Engine with MySQL
+6 Vote Up -0Vote Down

MySQL Full Text Search Limitations

Suppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you’ll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.

A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won’t take long before enough read traffic on the slave starts causing slave lags.

Why …

  [Read more...]
Importing times in MySQL
+0 Vote Up -0Vote Down

One of the ways to import data into MySQL is using the LOAD DATA INFILE. It is a faster method than recovering from a dump, as it’s raw data instead of SQL sentences.

The import time depends on the table engine, for example, MyISAM can be 40 times faster than Innodb. Let’s benchmark this:

Preparation

I’m gonna make some benchmarking using MySQL 5.1.36 (64 bits MacOS X). I’ll need a big table, so I’ll take City from the World Database and create a huge table called “city_huge”:

CREATE TABLE city_huge LIKE CITY;

INSERT INTO …
  [Read more...]
MyISAM quote of the day
+1 Vote Up -0Vote Down

Seen in #maatkit on Freenode:

I never realized just how terrible recovering MyISAM from a crash can be

Sad but true — it can be pretty painful. This is one of the reasons I pretty much recommend InnoDB (okay, okay, XtraDB) for most data unless it’s read-only.

Related posts:

  1. Hindsight on a scalable replacement for InnoDB A while ag
  2. What is the scalable replacement for …
  [Read more...]
10 Newer Entries Showing entries 61 to 70 of 103 10 Older Entries

Planet MySQL © 1995, 2016, 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.