Tag Info

Hot answers tagged

31

The only way MyISAM can be faster that InnoDB would be under this unique circumstance MyISAM When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this: ALTER TABLE mytable ROW_FORMAT=Fixed; I wrote about this ...


17

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out: No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this ...


15

Oracle is not like MySQL in this. Like most other RDBMSs it comes with it's own built-in storage engine that cannot be exchanged for another.


11

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types. For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be ...


9

You said I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam Whoever told you that should have told you to enable binary logs as @jynus commented. The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups. EXAMPLE #1 : ...


8

There are plenty of cases not to use the memory storage engine - and when InnoDB will be faster. You just need to think about concurrency and not trivial single threaded tests. If you have a large enough buffer pool, then InnoDB will become entirely memory resident for read operations as well. Databases have caches. They warm themselves up! Also - do ...


7

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup. 2) This page of the MySQL manual has instructions to convert table types. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or ...


7

In a simple world, MyISAM is faster for reads, InnoDB is faster for writes. Once you start introducing mixed read/writes, InnoDB will be faster for reads as well, thanks to its Row locking mechanism. I wrote a comparison of MySQL storage engines a few years ago, that still holds true to this day, outlining the unique differences between MyISAM and InnoDB. ...


7

Nonclustered indexes always include a row locator. For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that ...


7

Let me start by saying, I hate ALTER. It's evil, IMHO. Say, this is your current table schema - CREATE TABLE my_table_of_love ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, my_value VARCHAR(40), date_created DATE, PRIMARY KEY(id) ) ENGINE=MyISAM CHARSET=utf8; Here's the path I recommend - Create a new table object that will replace the old one: CREATE ...


6

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space: The PFS has one byte for each page, recording whether the page is ...


6

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3 Examples on improvements/features are "Index Condition Pushdown", "Cost-based ...


6

I can't tell you if this is a common practice. I can say about my own experience. I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine. When I need special features just available on InnoDB, I go for it. When a table is mostly read-only, I choose Archive engine before I could blink. ...


5

Pros If there is no Sphinx API for the language you are using SphinxSE allows you to do searches through MySQL. If you are doing background processing and storing the search results in your database SphinxSE allows you to move the data from the searchd service directly into MySQL instead of going through an application layer. Cons You have to compile ...


5

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable. MyISAM is probably easier to manage from a DBA standpoint with recovery and repair. And in 5.1, I think InnoDB's benchmarks ...


5

The write I/O was a result of repeated DROP TABLE/CREATE TABLE statements. I replaced those with TRUNCATE TABLE and the problem is gone.


5

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common. Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes. I would say it is a fairly good ...


5

Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes. Internal row overhead can expand significantly if certain engine features are in use. For example, using sparse columns reduces the user-accessible data size to 8019 bytes. ...


4

In general, MariaDB is a drop-in replacement for MySQL. It says so on their home page. :-) Your applications won't notice a difference. For specialized applications and edge cases, there may be difference. But these are going to be tuned to use special features of the newer versions of MySQL. You may also want to look at Percona Server as it tracks the ...


4

When it comes to InnoDB, you should never use big primary keys. Why? InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way ...


4

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions: Why still using MyISAM ? MyISAM still has ...


4

Both works with b-tree index, but MyISAM can work with FULLTEXT index also. MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA. InnoDB is more strict in data integrity, while MyISAM is loose InnoDB has transactions while MyISAM does not. InnoDB has foreign keys and relationship contraints while MyISAM ...


4

You may want to watch how many MEMORY tables you are using. Even with a MEMORY table, there must be an open file handle to the .frm of the MEMORY table. Check your open_files_limit. You may need to increase that as well. You also have a great disparity between read_buffer_size and read_rnd_buffer_size. You have read_buffer_size at 64M and ...


4

There's no need to check for disk space when allocating a new extent to an object. SQL Server already owns that space on the disk. It knows which pages within it's data file are allocated and which aren't, so there's no need to verify that we own the pages within the extent as we know for a fact that we already do. It will simply allocate space in the ...


4

XtraDB replaces InnoDB (it's compatible and a drop in replacement), so just add ENGINE = InnoDB However, it should be the default in MySQL 5.5.x onwards


4

For completeness, whilst Jack's answer is technically true, it is possible to use Data Cartridges to expand on vanilla Oracle offerings. In fact, a company called CopperEye released a new indexing method (patented, now not available) utilizing this functionality. See this old press release. The patent makes for fascinating reading.


3

It's X times easier to optimize whole server (memory configuration, caches, indexes) when you have only one engine used. Mixing myisam with innodb on large databases will always stuck at some point forced by some comprosise for both engine to work good (but not excelent :) I recommend You to interest in some dedicated full text search engines like sphinx, ...


3

I have not had much experience switching the myisam_data_pointer_size option from 4 to 6. Simply put, I think you got it right based on the Documentation. Here is why: According to the MySQL Documentation 5.5 on myisam_data_pointer_size: The default pointer size in bytes, to be used by CREATE TABLE for MyISAM tables when no MAX_ROWS option is ...


3

mysqldump is a client application. It connects to the MySQL server using "normal" credentials and queries for data. It has no immediate access to the data other than what it can ask of the server. The mysqldump binary doesn't have to run on the same host as the MySQL server. Hence it is obvious it cannot access files directly. It is instructive to turn the ...


2

I think it's time to do some math. What's your system ram vs. the size of table your trying to stuff into it? What is the size of the struct (data storage requirements of each row) you're storing in your memory table? Have you multiplied that * qty rows to see if that exceeds your values? http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html ...



Only top voted, non community-wiki answers of a minimum length are eligible