The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
0answers
57 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
1
vote
1answer
175 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 ...
0
votes
1answer
132 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 ...
0
votes
1answer
127 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 / ...
3
votes
2answers
201 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 ...
0
votes
2answers
351 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 ...
0
votes
1answer
784 views

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

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 ...
1
vote
2answers
2k 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 ...
1
vote
1answer
363 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 ...
2
votes
1answer
234 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, ...
9
votes
5answers
374 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 ...
8
votes
3answers
3k 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 ...
12
votes
3answers
3k 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 ...
2
votes
1answer
384 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 ...
1
vote
1answer
136 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 ...
3
votes
2answers
3k 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 ...
2
votes
2answers
888 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 ...
2
votes
3answers
1k 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 ...
5
votes
1answer
1k 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, ...
2
votes
3answers
12k 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 ...
2
votes
2answers
550 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? ...
1
vote
0answers
39 views

Copying records of MyISAM table is taking long time

I am facing an issue for the past couple of weeks. I am working on a windows server which is a dedicated server for MySQL having 32GB of RAM and using mysql 5.1.42 version. I am having two databases ...
6
votes
2answers
364 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 ...
1
vote
1answer
249 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 ...
2
votes
2answers
781 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 ...
1
vote
2answers
365 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 ...
1
vote
1answer
126 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 ...
3
votes
2answers
427 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 ...
5
votes
2answers
2k 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 ...
3
votes
2answers
754 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 ...
0
votes
1answer
147 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?
1
vote
2answers
321 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 ...
0
votes
1answer
103 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 ...
0
votes
1answer
193 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. ...
0
votes
2answers
152 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 ...
1
vote
1answer
576 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 ...
4
votes
2answers
5k 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
3
votes
2answers
3k 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
3
votes
3answers
4k 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 ...
2
votes
4answers
2k 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 ...
0
votes
2answers
174 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 ...
1
vote
1answer
257 views

Can I change a MySQL table back and forth between InnoDB and MyISAM without any problems?

I have a site with a decently big database, 3Gb in size, a couple of tables with a dozen million records. It's currently 100% on MyISAM, and I have the feeling that the server is going slower than it ...
2
votes
1answer
1k views

Join performance on MyISAM and InnoDB tables

I am thinking about converting some tables from MyISAM to InnoDB in my mysql server. The tables will certainly benefit from the change because a lot of write requests come to these tables, while there ...
1
vote
1answer
77 views

What are some general tips to make InnoDB for MySQL perform at its highest?

I've been using MyISAM exclusively for several years now and know the ins-and-outs pretty well of how to optimize it, but I've just recently started using InnoDB for some of my tables and don't know ...
1
vote
2answers
1k views

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 ...
1
vote
1answer
1k views

How to use most of memory available on MySQL

I've got a MySQL server which has both InnoDB and MyISAM tables. InnoDB tablespace is quite small under 4 GB. MyISAM is big ~250 GB in total of which 50 GB is for indexes. Our server has 32 GB of RAM ...
1
vote
2answers
310 views

Will deleting old MySQL data improve performance?

Would deleting old data from my tables improve MySQL query performance? When my queries use indexes? When my queries don't use indexes? When I use InnoDB? When I use MyISAM?
0
votes
1answer
212 views

Rebuilding a MySQL database from 160 retreived files with no name

I had a server crash and it took with it the hard drive. I put the hard drive in a USB enclosure, but the main partition would not mount because the partition was corrupted. I was able to see the USB ...
0
votes
1answer
469 views

MySQL retireive rows in sorted order on a very large table

I have a MyISAM table T with the following schema: f1 (integer unsigned not null) f2 (integer unsigned not null) This table has an index on f2 and it currently contains 320 million rows, and is ...
2
votes
3answers
388 views

InnoDB vs MyISAM

I'm creating a service like FriendFeed. For the database, what's the best to have the tables with InnoDB or MyISAM? There are a lot of selects and Inserts. The table must be serchable, sortable ...