Server Fault Stack Exchange
Server Fault Stack Exchange

Questions tagged [myisam]

70 questions
17
votes
5 answers

What is the difference between InnoDB and MyISAM?

I am using MySQL as my database for my current web project. I am new to MySQL. Please explain to me the difference between InnoDB and MyISAM.
user16614
16
votes
5 answers

How do you choose a MySQL database engine

In particular, how do you choose between MyISAM and InnoDB, when neither is missing a required feature (e.g. you don't need foreign keys). Does it always come down to trying both and measuring? Or are there good rules of thumb regarding the number…
Tony Meyer
  • 889
  • 1
  • 13
  • 25
15
votes
3 answers

MySQL Replication Performance

I'm having a serious issue with MySQL 5.5 replication performance between two machines, mostly myISAM tables with statement based replication. The binary logs and mysql data directory are both located on the same Fusion ioDrive. The problem was a…
Nick
  • 153
  • 1
  • 6
15
votes
4 answers

How to know storage engine used of a database?

Previously, on every database created, I use: mysql -u root -p CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_bin; GRANT ALL ON dbname.* TO 'dbuser'@'localhost'; and then use the database without thinking about MyISAM or InnoDB How to know…
cewebugil
  • 705
  • 3
  • 8
  • 12
13
votes
4 answers

Simpler way to convert all tables from InnoDB to MyISAM

Previously, I use this: USE dbname; ALTER TABLE tablename ENGINE=MYISAM; I'm looking for simpler way to convert all tables in a database, rather than writing every table name one by one
cewebugil
  • 705
  • 3
  • 8
  • 12
10
votes
5 answers

MyISAM for data reads

I have a table with around 1 Billion rows, and its 98% read intensive. I tried tuning the database, with different storage engines (MyISAM and InnoDB) Then ran a few test to view the performance In the where clause I had a primary key ID, and it…
Akash
  • 229
  • 3
  • 7
9
votes
3 answers

Do InnoDB tables got locked during mysqldump when mixed with MyISAM?

I'm looking for a backup solution for my mysql servers and I need as less downtime as possible. I have the following: MySQL servers they are not replicated each server stands for its own This number can grow, so setting up a master/slave…
tounano
  • 211
  • 2
  • 7
8
votes
2 answers

How to log all the MySQL queries that waited for a table lock?

We are trying to diagnose a deadlock problem MySQL 5.0. Currently all the tables are MyISAM tables and so all the locks are table locks. We have a good table_locks_waited to table_locks_immediate ratio (lower than 1:1000), but within a couple of…
netvope
  • 2,113
  • 5
  • 25
  • 35
7
votes
2 answers

How do MyISAM and InnoDB Utilize HD Space?

My MySQL server is running out of HD space fast. Most of my larger tables use the InnoDB engine (for no mission-critical reason). In an effort to avoid the dreaded 'drop database to recover innodb disk-space' response, I'd like to better understand…
Mike B
  • 213
  • 1
  • 3
  • 7
6
votes
1 answer

HA Setup for MySQL/mostly MyISAM Tables

I am currently using Master-Slave Replication, separating reads from writes at the application level. The only kind of fail-over currently implemented is application level detection of master failure, making the slave the new master so the…
Niko S P
  • 1,182
  • 8
  • 15
6
votes
2 answers

MySQL 5.0 -> 5.1 upgrade, table upgrades taking a very long time

We have a database server that was running MySQL 5.0 on Debian etch (!), and decided it was time to upgrade. It is now running 5.1 on Debian squeeze. This database server has about 1.2TB of MyISAM data on a SATA RAID array, and 2GB of ram. Normally…
Alex Forbes
  • 2,392
  • 2
  • 19
  • 26
5
votes
5 answers

phpMyAdmin showing database is MyISAM but tables are InnoDB

Is it possible for a database to be of one type (MyISAM) and the tables to be of another type (InnoDB) or is this a bug in phpMyAdmin? It is showing exactly that. Picture Here…
Brian Armstrong
  • 1,557
  • 3
  • 18
  • 22
5
votes
2 answers

Why is MySQL is using so many temporary tables?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were created on disk table_open_cache = 125…
ananthan
  • 1,490
  • 1
  • 17
  • 27
5
votes
2 answers

MySQL InnoDB table shows a negative number of rows in phpMyAdmin

I just converted a MyISAM table to InnoDB with around 1.4 million rows. When I converted it to InnoDB, it now shows -1.4 million rows. The table still works as expected, but why does it shows negative in the rows column?
James Simpson
  • 1,601
  • 2
  • 14
  • 30
4
votes
3 answers

mysql performance tuning, write, get db into ram

I am trying to improve the performance of a website by optimizing the Mysql server. In addition to general optimization I have a feeling that some write operations take an unacceptably long time. The server is a Mac os x server 10.5.8 (I think this…
tomsv
  • 273
  • 3
  • 8
1
2 3 4 5