Questions tagged [myisam]
MyISAM is the non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. In addition, it is the default storage engine type for versions of MySQL prior to 5.5.
513
questions
4
votes
4
answers
16k
views
Optimized my.cnf for high-end and busy server
Currently my main culprit of poor performance is MySQL and I'm not DB savvy. So far, I've had little luck in optimizing MySQL configs on my server, which has a Sandy Bridge processor with 16GB RAM and ...
- 2,964
4
votes
1
answer
4k
views
mysqlcheck to optimize innodb tables -- performance/scheduling
I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.
Is there a ...
- 193
4
votes
2
answers
17k
views
Preordering the table by a specified column
I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering.
Is it possible to order the table ...
- 1,017
4
votes
1
answer
9k
views
Can converting MyISAM to InnoDB cause problems?
I am changing an old MySQL database table which is still in MyISAM. I'd like to change it to InnoDB, but I don't know whether it can cause problems and it's a pretty important table so I don't want to ...
- 143
4
votes
1
answer
11k
views
How do I insert tens of thousands of rows without locking?
I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There ...
- 175
4
votes
2
answers
813
views
Should I move to InnoDB during a planned migration?
I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
- 39.9k
4
votes
1
answer
24k
views
MySQL Performance Tuning for MyISAM
I need to performance tune a mysql server and need help.
I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
- 41
4
votes
2
answers
8k
views
Database Tuning Issues
I'm not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I've found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end.
I've ...
- 113
4
votes
1
answer
15k
views
how to set bulk_insert_buffer_size in mysql?
I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such ...
- 213
4
votes
2
answers
2k
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 ...
- 297
4
votes
2
answers
9k
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
...
- 151
4
votes
3
answers
3k
views
Estimate query speed for +1.5 billion rows mysql table
I'm working on audio fingerprinting problem where I need to
query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to ...
- 81
4
votes
4
answers
11k
views
Why does MySQL slow down in loading data?
We have a really large file (26GB) that we need to upload to MySQL. In order to do so we split it into nine 3GB files.
What's confusing is that the first file took only 2 minutes to load, whereas the ...
- 173
4
votes
1
answer
15k
views
MySQL hangs in killed state
I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.
Scenario to reproduce:
OS: CentOS 6.5
MySQL: 5.6
Engine: MyISAM (with at ...
user3360578
4
votes
2
answers
6k
views
Tuning key_reads in MySQL
We are trying to tune our MySQL database (Myisam, 5.1.73) which is getting busy.
We investigated Key_reads:
$ mysqladmin ext -ri10 | grep Key_reads
During a normal load we have the following ...
- 93
4
votes
1
answer
528
views
Will a change from MyISAM to InnoDB require SQL query changes in MySQL?
I am planning to change some large MyISAM tables in my MySQL database into InnoDB.
This is because I am having performance problems where large and complex updates (updating a few million rows in one ...
4
votes
2
answers
1k
views
Less RAM than Index_length MyISAM
I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
- 664
4
votes
2
answers
7k
views
How to speed up mysql export and import
I need to perform a DB migration from Server A to Server B.
Server A: MySQL version installed 5.0.6
Server B: MySQL version installed 5.6.20
OS: Both are running RedHat Linux 64bit. 16 RAM memory.
...
- 107
4
votes
1
answer
611
views
What's the optimal server configuration for fastest inserting in a MySql database in this scenario?
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 ...
- 141
4
votes
2
answers
5k
views
What's row size in mysql?
What does row size mean in mysql? I have a mysql db and table in myisam and it shows
Space usage Type Usage
Data 1.3 GiB
Index 429.1 MiB
Total 1.7 GiB
Row Statistics Statements ...
user16108
4
votes
1
answer
4k
views
Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?
Per this post, in order to optimize the storage of a MyISAM table, you need to:
OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;
It's obvious what OPTIMIZE does for disk space but what ...
- 143
4
votes
3
answers
2k
views
MySQL Tables Require Daily Repairs - Server, Table or HD?
I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.
The bigger ones ...
- 41
4
votes
2
answers
4k
views
Repair MySQL table without downtime
We have a corrupt MyISAM table in our production MySQL database. Is there a method for repairing this table without locking it?
We were considering using a method similar to Facebook's Online Schema ...
Andrew Ashbacher
4
votes
1
answer
3k
views
Can I partially invalidate the MySQL table cache?
I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB.
After several hours, MySQL invalidates the entire table from cache, while < ...
- 41
4
votes
1
answer
7k
views
Not enough space for MySQL optimize; do new rows overwrite existing data?
My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space.
Is there any ...
- 161
4
votes
2
answers
9k
views
How to batch multiple insert statements for database dump with MySQL?
I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
- 143
4
votes
2
answers
825
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 ...
- 143
4
votes
2
answers
326
views
Choosing between MyISAM & InnoDB - two servers with related tables
I don't want to make a mistake.
I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.
I read here and there that InnoDB should be ...
- 141
4
votes
1
answer
1k
views
Fastest way to copy data from MyISAM to InnoDB
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try ...
- 357
4
votes
1
answer
11k
views
What could cause waiting for table metadata lock?
I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB ...
- 51
4
votes
1
answer
2k
views
Why would the size of MySQL MyISAM table indexes (aka MYI file) not match after mysqldump import?
I've been archiving some MySQL (version 5.0.96) MyISAM tables locally and deleting from my server after checking the data. Every time the process has been pretty similar and comes off mostly without a ...
- 41
4
votes
0
answers
1k
views
Dynamic MySQL partitioning based on Unix Time
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing Unix time.
I am designing an ...
- 173
3
votes
4
answers
3k
views
Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?
I am considering scaling up instead of scaling out. Therefore, I am interested to hear whether it is feasible to utilize the MySQL in-memory storage engine for a database that is 500+ GB, given that ...
- 1,185
3
votes
2
answers
2k
views
Why innodb doesn't store geospatial data?
I am very curious.
I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.
But why would I do so?
Why can't innodb store ...
- 1,365
3
votes
1
answer
1k
views
Best way to add a new column to a large table? (MySQL - MyISAM)
Here's more info on the table that needs to be altered:
15GB size MyISAM table with Index around 5GB
Over 100 million rows
MySQL 5.1 on RHEL 5 with 4GB RAM
Currently live online
What's the best way ...
- 33
3
votes
2
answers
15k
views
bulk_insert_buffer_size and InnoDB
I read the following on this page
http://dev.mysql.com/doc/mysql/en/server-system-variables.html
bulk_insert_buffer_size MyISAM uses a special tree-like cache to make
bulk inserts faster for ...
- 31
3
votes
2
answers
9k
views
How does mysql decide if concurrent insert is possible for a MyISAM table?
For "concurrent inserts", MySQL reference manual has the following explanation:
The MyISAM storage engine supports concurrent inserts to reduce
contention between readers and writers for a given ...
- 619
3
votes
3
answers
848
views
Dynamically changeable database
We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
- 1,017
3
votes
2
answers
3k
views
Migrating MySQL from Windows to Linux
I'm new to MySQL. However I am very familiar with Oracle and MSSQL. This is what I have done so far:
Installed MySQL 5.7 on RHEL 7.2
Created a test db called "mydb" and test table called "employee"....
- 157
3
votes
2
answers
2k
views
How to backup a MySQL database without slowing the application?
I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
- 134
3
votes
4
answers
8k
views
Can I specify a different disk for a specific MySQL table?
I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is ...
- 789
3
votes
2
answers
2k
views
Is it possible to block a specific query, or return a static result?
Before you yell at me for doing it wrong, I know I'm doing it wrong. I'm using WHMCS (source code is not available), and we have a quarter of a million invoices. One particular query, which runs on ...
- 143
3
votes
1
answer
9k
views
Converting Heap to MyISAM issue
At work we have a problem that has now occurred more then once.
Problem is that query is in state of converting HEAP to MyISAM and it paralyzes our server( query in this state is not able to kill ...
3
votes
1
answer
10k
views
How can I optimize my mysql setup to create my index faster?
I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package. The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.
I have an MyISAM like ...
- 239
3
votes
3
answers
2k
views
Are TEXT fields stored separately from the row in MySQL-ISAM?
We have a "notes" table and some of the queries are getting very slow even though we're not searching on the note contents. We're wondering if moving the note content into a separate table would speed ...
- 427
3
votes
1
answer
2k
views
MySQL Performance Issues
Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that ...
- 33
3
votes
2
answers
15k
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 ...
- 63
3
votes
1
answer
251
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 ...
- 31
3
votes
1
answer
8k
views
MySQL high CPU usage (MyISAM table indexes)
I have a problem with an inherited MySQL database.
From time to time mysqld uses up to 2300% CPU..
The only solution is to service mysql stop and run an myisamchk -r on a table.
After the indexes have ...
- 55
3
votes
2
answers
485
views
Writing transactions to .MYD file to take Backup
I'm trying to backup MySQL DB by archiving the data directory of required databases.
I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
- 183