0
votes
0answers
17 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
0
votes
1answer
93 views
+50

MySQL - Best long read query solution within a replication based system

I'm working on a project where work in a queue requires a database read against heterogeneous table schemas (InnoDB/MyISAM). The queries constructed during the "read" process can result in 10-15 ...
1
vote
1answer
39 views

InnoDB to MyISAM Conversion Problem

I have a dedicated server based on CentOS 5 OS , mysql 5 and phpmyadmin I was trying to convert all database tables from InnoDB to MYISAM. I tried to do it by setting in /etc/my.cnf file the value ...
1
vote
1answer
54 views

MyISAM with Innodb in the same Wordpress database reduce performace?

My new Wordpress database all the Wordpress created tables use InnoDB engine and plugin created table use MyISAM. But older site database all the tables use MyISAM table engine. I use MySQL ...
5
votes
1answer
279 views

MySQL 5.1 to 5.6: massive performance hit

Okay, this is possibly three questions. I want to move my existing MySQL 5.1 database using MyISAM to 5.6 using InnoDB for what I think are a host of obvious -- and possibly even good -- reasons. ...
0
votes
1answer
51 views

Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?

I inherited a DB using myisam on mysql 5.1 because of the need for fulltext search. I now read that fulltext search is available on mysql 5.6 innodb. Doing this would solve a lot of problems, as long ...
0
votes
0answers
32 views

.myd and .myi versus .frm and .ibd

I'm new to MySQL.I have trouble finding the .frm files and .ibd files in Windows 8 Explorer.They seem hidden and I have to find them using 'Find' and 'Open File Location', not sure how to make these ...
0
votes
0answers
64 views

What are the steps to change tables engines from MyISAM to InnoDB?

I have a Datebase with 10MB size . The Tables' engines were MyISAM (table-lock level). So I deiced to change them to InnoDB (row-lock level). I used the sql query for each table : ALTER TABLE t ...
0
votes
1answer
36 views

is it possible to set engine for database (not the tables)?

There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server. They were imported and were displayed in the tree structure of phpmyadmin (under the ...
1
vote
1answer
71 views

Need to optimize simple SELECT query for InnoDB table with 2million rows

I'd like to make this query the fastest possible, since it's called really often. SELECT login_events.id FROM login_events WHERE ( DATE(created_at) >= DATE(CURRENT_DATE) AND person_id = 1 ) ...
0
votes
2answers
39 views

MySQL using swap during conversion to InnoDB

I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
1
vote
2answers
151 views

Setting default CHARSET and DB Engine

I recently updated my server's database system from MySQL to MariaDB. After update, everything works well. But, when I went to phpMyAdmin, the default charset and types are changed: At the end of ...
0
votes
0answers
36 views

How to get rid of the table lock in MySQL

I have a database for my website the website have a betting game(using insert and update queries) and the other pages of the website are using (select, insert and update queries). When a user make a ...
1
vote
1answer
254 views

Timeout on ALTER TABLE table ENGINE=InnoDB - is table still converted?

I'm converting tables in an old MYISAM database to InnoDB to increase robustness in case of power loss etc. I'm using Mysql 5.5.x Before doing the production system, I am doing a trial run on a copy ...
0
votes
3answers
184 views

Converting live MyISAM table to InnoDB

I have a table that tracks web traffic. The table is constantly written and read from. Since table is pretty big in size I have to make sure the application will not "freeze" in the process of ...
0
votes
0answers
154 views

Improve Speed of MySQL Database Import

I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 ...
0
votes
1answer
142 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
1
vote
1answer
43 views

MySQL command querying all MyISAM database

I would appreciate if anyone could help me of what's the proper command in MySQL to query all database for having "MyISAM" engine. I need to convert all DB & tables from MyISAM to InnoDB.
1
vote
1answer
343 views

Spatial Index on an InnoDB table

I have a database which is in InnoDB, because I wanted consistency (cascading delete). Now, I want to use spatial indexes for selecting rows ordered by geometric distance. However, spatial indexes ...
1
vote
1answer
207 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
0
votes
1answer
3k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
2
votes
2answers
2k views

MySQL 5.5 High CPU Usage

I have been working with a client with high traffic website(500k visitors and 600-800 active users at any given time). This uses wordpress and the DB is using MyISAM engine. The problem we had was ...
1
vote
2answers
422 views

Remove InnoDB files (ibdata1 and iblog files) after migration to MyISAM

I changed the only two InnoDB tables on a replication slave to MyISAM, so the slave now only has MyISAM tables. Can I now remove the ibdata1 file completely?
0
votes
0answers
53 views

Multiple mysql tables, JOIN performance

I have multiple tables in a mysql database. Each table has about 10 million rows. I have multiple queries, and often need to join multiple tables in every query, which seems to be causing some ...
3
votes
1answer
102 views

What if I want to keep using MyISAM

In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate. The script uses MYISAM tables and ...
1
vote
1answer
63 views

InnoDB query duration inconsistent

I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 ...
0
votes
1answer
219 views

Information about Disk Storage MySQL

I am trying to create an application for monitoring MySQL,in my application there's a part of monitor DISK STORAGE, so after more research I have not found how to calculate : used and free size of ...
0
votes
3answers
229 views

Convert the table PROCESSLIST from InnoDB to MYISAM

I use MySQL. My database's name is "db", and I have a table called "Test". I want recover the last update, so I used this query: SELECT update_time FROM information_schema.tables WHERE ...
0
votes
2answers
247 views

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!

I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
1
vote
1answer
1k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Here is my current my.cnf: pastebin.com/FW8YXtLQ What would be the best configuration? (need to add InnoDB settings, byecause there are ...
1
vote
1answer
160 views

Issues converting MyISAM table to InnoDB (auto column issue)

I'm having issues trying to convert a table from MyISAM to InnoDB in MySQL 5.6. The following is the table dump: -- -- Table structure for table `companies` -- DROP TABLE IF EXISTS `companies`; ...
1
vote
1answer
320 views

Will OPTIMIZE TABLE have any impact on my data?

I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables. But before doing so, I would (of course) like to know if ...
1
vote
1answer
180 views

High IO when switched to InnoDB

We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times. The query is type of INSERT ... ON DUPLICATE KEY UPDATE I don't know if the problem is ...
0
votes
1answer
191 views

MySQL: replicating to a different table engine type

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say ...
1
vote
1answer
192 views

MySQL: OPTIMIZE after TRUNCATE?

Using MySQL (either InnoDB or MyISAM tables--I have both), if I TRUNCATE a table and then LOAD DATA INFILE, should I issue an OPTIMIZE TABLE call after the LOAD? It's unclear to me if this is a ...
1
vote
1answer
76 views

extra steps after changing storage engine and adding index

Someone told me to look into his website for quick optimization; I'm a programmer and i don't have much experience optimizing databases. I have a php/MySQL site uses the MyISAM storage engine. It ...
2
votes
2answers
135 views

Are there implicit indexes in InnoDB like MyISAM?

If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys: (a) (a, b) (a, b, c, id) Is this valid for ...
0
votes
1answer
129 views

Large queries on MyISAM tables crashing MySQL

I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
0
votes
2answers
267 views

Bypass MyISAM table lock

I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
0
votes
2answers
780 views

Unable to change engine to MyISAM in MySQL

I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine. I have checked following engines are supported in MySQL: show engines\G ...
2
votes
1answer
518 views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
0
votes
0answers
33 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+) [duplicate]

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
1
vote
2answers
331 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
3
votes
1answer
138 views

Which MySQL table to use for read-only, bulk-loaded tables?

So I have a workload on 2 key tables in my application with the following characteristics: READ-ONLY DATA The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
4
votes
2answers
236 views

Using MyISAM for reading and InnoDB for writing data

I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data. I want to use two structure like ...
4
votes
2answers
4k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
1
vote
1answer
414 views

How to check growth of database in mysql?

I want to know is there any method to check the growth of database on file EXAMPLES Database A contains all tables in INNODB storage engine Database B contains all tables in MyISAM storage engine ...
4
votes
3answers
4k views

MySQL LOAD DATA INFILE slows by 80% after a few gigs of input with InnoDB engine

I'm loading a 100GB file via LOAD DATA INFILE. I've had good success with MyISAM, a few hours and done. I'm trying it now using InnoDB. The load starts fast at over 10MB/sec (watching the table file ...
1
vote
1answer
462 views

MySQL: To use MYISAM or INNODB engine? (plot twist enclosed)

We use INNODB for all of our tables, but I have one special table, a 100+ GB reference table (only read-queries against it). We are loading changes to this table in a nightly batch job using LOAD ...
1
vote
1answer
215 views

Set up replication and migrate to InnoDB with low downtime

Current situation is this: I have a large legacy DB using only MyISAM tables, the size of whole DB is 4GiB, one of the tables contains over 10 million rows, so it is pretty large. The database is ...