Questions tagged [myisam]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
2k views

MariaDB (MySQL) 'table marked as crashed' recurring

I have a production environment where about once a week I'm finding the database needs to be repaired and restarted. The MariaDB database contains some InnoDB and some MyISAM tables (the mixture being ...
Ade's user avatar
  • 709
0 votes
0 answers
36 views

Website down with 3,000 concurrent user 4Gb Memory Dedicated Server (myIsam - MySQL) [duplicate]

Our site has 3,000+ concurrent users when we open our registration on our website. The site was too slow and get the server down sometime, even sub accounts websites are down. Free memory was 400MB. ...
c.k's user avatar
  • 101
0 votes
0 answers
954 views

Occasional slow database queries caused by InnoDB

Update 2017-11-26 The problem seems to be related to InnoDB. The CI as well as the Wordpress installations are using InnoDB Engines while the rest is working with MyISAM. After I converted all MODx ...
travisbotello's user avatar
0 votes
1 answer
751 views

MySQL behaviour and disk space handling after deletions and insertions with MyISAM

I have a question about the way MySQL with MyISAM engine handles the free/empty space after deletions and how it behaves when new records are inserted. For example, think about a new and empty DB (...
Riccardo M.'s user avatar
1 vote
3 answers
2k views

Backing up MySQL MyISAM databases by copying the physical folders

I have a Windows Server 2008 R2 machine and on it I have 2 MySQL MyISAM databases. These databases are quite large as they are over 20GB in combined size. I update these databases once a week. (I ...
user3480610's user avatar
0 votes
0 answers
791 views

Mysql table keeps crashing. How to debug the cause and prevent it?

My wordpress wp_users table keeps crashing and when I get up on the morning is : wp_users' is marked as crashed and last (automatic?) repair failed So all my users can't access the site. If I do a ...
chifliiiii's user avatar
0 votes
1 answer
1k views

MySQL INSERTs on a database table block SELECTS in another database and in another table

I am using MySQL 5.1 on a Windows Server 2008 R2 Machine with 8GB RAM. I am updating 2 databases weekly. Both databases consist of MyISAM tables, and I run a script which performs the necessary ...
user3480610's user avatar
0 votes
1 answer
7k views

What do "key buffer size" and "total MyISAM indexes" unit sizes mean?

I ran MySQLTuner on my database to check if things are working correctly and optimally, but I was confused as to the units used for these two values. Here is what the output looks like for ...
Joe Lloyd's user avatar
  • 103
0 votes
2 answers
191 views

MyISAM Throughput spike at 12000 record insert

I am testing database performance with bulk record inserts. Both systems are identical apart from storage engine **Database 1000 4000 12000** MySQL_InnoDB 60.95330976 218....
johnnydrama's user avatar
1 vote
3 answers
109 views

Any solution for MySQL Servers to avoid rendering them unresponsive when running big queries?

I do run several mysql servers for several clients, there is a common problem I used to see. When I run some big query against big databases, for example: I have a MyISAM table with several fulltext ...
Ahmad Hamad's user avatar
1 vote
1 answer
105 views

Size of MYI index smaller on slave server

I've recently altered a huge MyISAM table (80 million entries) by adding a new value in an enum field. After I did this I've checked the health of the table on 2 slave servers which are used to ...
morandi3's user avatar
  • 113
2 votes
1 answer
1k views

changing mySQL (5.6) database from MyISAM to InnoDB

I have recently upgraded my CPanel server from mySQL 5.5 to mySQL 5.6 as it now supports full-text searches for InnoDB. Now, I'm not so great at mySQL so I was wondering is there anything special I ...
user2643870's user avatar
0 votes
1 answer
2k views

MySQL MyISAM corrupt table, how to restore?

I have a MySQL MyISAM table that's corrupt. It first showed up in my mysql log as: [ERROR] /usr/sbin/mysqld: Table './mydb/mytable' is marked as crashed and last (automatic?) repair failed I then ...
curtisdf's user avatar
  • 251
0 votes
1 answer
222 views

Large InnoDB data size even though entire database is MyISAM?

I'm running mysqltuner to tune up my database and I'm running into a very strange occurrence. Even though the entire database is MyISAM (all tables) it says the following: [!!] InnoDB data size / ...
robert's user avatar
  • 103
3 votes
2 answers
229 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario? [closed]

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
Gary Willoughby's user avatar
0 votes
2 answers
671 views

mysql 5.5 myisam db tunning

I'm using mysql 5.5 with myisam db. Currently, sometimes the cpu usage of mysql will reach 200% CPU resource. And there are lots of queries 'waiting for table locking' in mysql. And at this time, the ...
Meteor's user avatar
  • 151
0 votes
1 answer
2k views

cpanel account transfers failing: ERROR: Failed to dump one or more databases [closed]

Getting this error when transferring multiple accounts via whm transfer utility: ERROR: Failed to dump one or more databases I was told this by a cpanel tech Here is the specific error I'm seeing ...
user avatar
3 votes
3 answers
10k views

MySQL Table not repairing

Table info: Database name: user_motiva Table name: wp_options.frm wp_options.MYD wp_options.MYI wp_options.TMD when I do a mysqlcheck -r --all-databases it gets hung on that table even if you let ...
Tiffany Walker's user avatar
2 votes
1 answer
2k views

What conditions trigger a table level lock

Working on some vBulletin performance issues, I ran into this situation, where everything is stuck waiting on a table-level lock: Id Command Time State Info 83 Query 47 ...
tylerl's user avatar
  • 15.1k
2 votes
1 answer
498 views

Which is more durable? MyISAM or InnoDB with innodb_flush_log_at_trx_commit=0 [closed]

I am trying to determine the difference in durability between MySQL's MyISAM and InnoDB when innodb_flush_log_at_trx_commit=0. I am aware of the some of the benefits of InnoDB with ACID. However, ...
adamSpline's user avatar
10 votes
5 answers
942 views

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 ...
Akash's user avatar
  • 229
10 votes
3 answers
7k views

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 ...
tounano's user avatar
  • 221
15 votes
3 answers
7k views

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 ...
Nick's user avatar
  • 153
3 votes
1 answer
2k views

Queries Stuck on the 'Connect' Command

So, little back story. We have a master-slave set-up and multiple times per day, we see something like this on the slave database, trying to replicate whats come from the master Id User ...
Ascherer's user avatar
1 vote
1 answer
176 views

Running mysqlimport on a MyISAM table for hours, Data_length stays at 0 [closed]

I'm trying to import a fairly large file (5Gb, ~130M records) into a MyISAM table (mysql 5.1.61). It's been running for more than 5 hours now with no errors displayed, and I can see "LOAD DATA INFILE ....
Yuriy's user avatar
  • 145
5 votes
2 answers
9k views

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 ...
ananthan's user avatar
  • 1,520
2 votes
2 answers
2k views

MySql crash: Incorrect key file for table

We have a MySql 5.5 database running InnoDB on Linux. One table (named table_search) is running MyISAM to be able to perform full text searches. The table has 823 368 rows. Yesterday we had a server ...
Martin's user avatar
  • 379
4 votes
3 answers
4k views

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 ...
tomsv's user avatar
  • 283
6 votes
1 answer
3k views

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, ...
Niko S P's user avatar
  • 1,182
3 votes
3 answers
25k views

Best my.cnf configuration for a 8GB MySQL server with MyISAM use only

I am preparing a large Drupal 6.x based installation and have setup a dedicated MySQL server with 8GB of RAM. Drupal 6.x uses only MyISAM engine and there will be around 500 concurrent users using the ...
Mohammad Emran's user avatar
2 votes
2 answers
1k views

What happens to InnoDB and MyISAM tables if the power fails during a transaction?

We have two MySQL tables, one is InnoDB and the other is MyISAM. If I execute a chunk of inserts in a single transaction on both tables and the power fails in the middle, what will be the status? ...
Ash's user avatar
  • 21
6 votes
2 answers
587 views

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 ...
Alex Forbes's user avatar
  • 2,462
0 votes
2 answers
2k views

MySQL: view read/write proportion, per specific table

I'm optimizing the configuration of a MySQL server, running only Bromine. This is non specific to application, and was just noted for completeness. I am trying to determine if switching from MyISAM to ...
J. M. Becker's user avatar
  • 2,461
1 vote
2 answers
2k views

which is faster mysqldump/restore or alter table?

I have InnoDB databases and they are using the same ibdata file, the size of this ibdata is around 250G. now i have one of those DBs around 100G. My manager asked me to move this DB to another server ...
Alaa Alomari's user avatar
1 vote
2 answers
556 views

Weird MySQL Errors After Distrib Upgrade (SQL 5.0 > 5.1)

Just updated Debian from Lenny to Squeeze on one of my servers. It hosts a MySQL server. Upgrade went fine, but afterwords, when MySQL starts up, it checks the databases to make sure they are okay. I ...
ThaKidd KG5ORD's user avatar
1 vote
1 answer
180 views

MySQL deadlocks when trying to drop large fulltext index

I'm trying to just drop a 500mb fulltext index on an older 4.1 MySQL MyISAM table. When I do it, the cpu pegs to 100% and sits like that for over 10 minutes. This cannot be normal, right? I gave up ...
ck_'s user avatar
  • 469
3 votes
2 answers
760 views

multiple mysql processes caused DB table to crash

My website has several PHP+MySQL applications. I noticed my website has been very unstable after I installed Limesurvey. myisam tables crashed every 3-4 days. I copied mysql logs here: 110812 04:10:...
user895701's user avatar
8 votes
2 answers
9k views

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 ...
netvope's user avatar
  • 2,123
3 votes
2 answers
2k views

Best Mysql DB Engine When Insert update delete query is not made

What should be the best Mysql db engine When query like Update, Insert, Delete are barely made (99% of time are not made) The database has a lots entry mainly numeric data type, the select query are ...
Isdev's user avatar
  • 207
0 votes
1 answer
233 views

Can I Record Limit on MyISAM Table?

I have a big data in myisam so a table's disk space is got full so Can I Record Limit on MyISAM Table?
freddiefujiwra's user avatar
1 vote
2 answers
753 views

Merge data from two mysql server

I have two old mysql servers and now I bought a new server and I want to migrate the files to this server, my question is if I copy all those files to the new server will the mysql recognize this ...
Mokus's user avatar
  • 422
0 votes
1 answer
132 views

MyISAM Partition empty table load problem

> Create Table: CREATE TABLE `trb3` ( `id` int(11) default NULL, `name` varchar(50) default NULL, `purchased` date default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (...
freddiefujiwra's user avatar
1 vote
1 answer
9k views

How to check what query caused the lock

I have some problems with my server applications and it sometimes causes some tables to lock themselves and then I got thousands of queries waiting for the table being unlocked. But it won't happen. ...
Balon's user avatar
  • 145
0 votes
2 answers
164 views

changing lastactivity mysql table from myisam to memory a good idea?

I am trying to reduce the writes to disk and in my social site I have a lastactivity table which is updated with the time() a user last used the site. It is currently MyIsam: id int(7) PRI ...
bearhunt's user avatar
1 vote
1 answer
1k views

MySQLdump deadlock on a myisam table?

Today I woke up with our production server down. Not happy. We pinpointed the problem to a daily cronjob, that does a full mysqldump from the production database, to a remote server. The SQL command ...
Silver Dragon's user avatar
13 votes
4 answers
39k views

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's user avatar
  • 715
3 votes
3 answers
7k views

How to disable all MariaDB storage engine except MyISAM?

(in my.cnf) I've tried default-storage-engine = myisam, skip-innodb, and commented out every innodb features, but none of them works
cewebugil's user avatar
  • 715
15 votes
4 answers
52k views

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 ...
cewebugil's user avatar
  • 715
3 votes
4 answers
5k views

MySQL: how to convert many MyISAM tables to InnoDB in a production database?

We have a production database that is made up entirely of MyISAM tables. We are considering converting them to InnoDB to gain better concurrency & reliability. Can I just alter the myISAM tables ...
Continuation's user avatar
  • 3,130
0 votes
2 answers
185 views

MySQL - Innodb, search

I recently switched from myisam to innodb in one of my mysql tables. Before i was using MATCH(). Since innodb doesn't support match() i decided to use LIKE. Now I have a problem. My table is 190,6 ...
Erik's user avatar
  • 125