All Questions
3,206 questions
281
votes
10
answers
286k
views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
- 3,039
258
votes
6
answers
472k
views
How large should be mysql innodb_buffer_pool_size?
I have a busy database with solely InnoDB tables which is about 5GB in size. The database runs on a Debian server using SSD disks and I've set max connections = 800 which sometimes saturate and grind ...
- 2,984
212
votes
7
answers
308k
views
How can I optimize a mysqldump of a large database?
I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to ...
- 4,299
142
votes
5
answers
255k
views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer than DBA for MySQL so need some help
Setup
The servers are quite hefty with an OLAP/...
- 70.1k
132
votes
5
answers
228k
views
MySQL any way to import a huge (32 GB) sql dump faster?
I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:
mysql -uroot dbname < dbname.sql
It is taking too long. ...
- 1,423
117
votes
5
answers
241k
views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
- 23.5k
102
votes
3
answers
256k
views
Difference between On Delete Cascade & On Update Cascade in mysql
I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
- 1,131
72
votes
1
answer
152k
views
What is the best way to reduce the size of ibdata in mysql?
I have some Production servers whose ibdata files increase in size day by day.
It has already consumed 290GB of space.
The tables in the servers are mostly InnoDB and there are high read and write ...
- 9,717
71
votes
5
answers
138k
views
Is it safe to use innodb_flush_log_at_trx_commit = 2
I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?
- 855
69
votes
8
answers
188k
views
ERROR: no schema has been selected to create in
I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not ...
- 793
67
votes
3
answers
89k
views
What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
- 771
64
votes
4
answers
141k
views
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
- 2,009
63
votes
1
answer
64k
views
What is the meaning of filtered in MySQL explain?
As described here in the MySQL docs:
The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows ...
- 685
55
votes
3
answers
73k
views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
- 4,531
53
votes
10
answers
399k
views
restore table from .frm and .ibd file?
I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-...
- 633
46
votes
5
answers
79k
views
How to estimate/predict data size and index size of a table in MySQL
I am finding what is the best way to estimate size of a table for that I have studied lot of blogs and forums but unable to find any accurate answer
For example, we have a table City with InnoDB ...
- 9,717
45
votes
2
answers
115k
views
InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup
I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,...
- 1,407
40
votes
6
answers
70k
views
How do you tune MySQL for a heavy InnoDB workload?
Assuming a production OLTP system with predominantly InnoDB tables
What are the common symptoms of a mistuned/misconfigured system?
What configuration parameters do you most commonly change from ...
- 1,377
40
votes
1
answer
57k
views
intermittent MySQL crashes with error "Fatal error: cannot allocate memory for the buffer pool"
Added on edit, 2013-05-29: Because this is a long question and discussion, here's a short summary of the question and the solution. I had problems running MySQL and Apache on a small Linux server (1 ...
- 503
40
votes
4
answers
17k
views
Why does MySQL not have hash indices on MyISAM or InnoDB?
I have an application that will only select on equality, and I figure I should use a hash index over a btree index. Much to my dismay, hash indices are not supported on MyISAM or InnoDB. What's up ...
Alex
39
votes
4
answers
46k
views
What is the optimal way to upgrade production RDS instance?
I have MySQL small RDS instance as part of my production system and I want to upgrade it to medium instance with provided IOPS.
As old-school DBA I'm aware about "add slave; promote to master; ...
38
votes
5
answers
58k
views
Why are simple SELECTs on InnoDB 100x slower than on MyISAM?
I have quite an annoying problem. I want to use INNODB as my main database engine and give up on MyISAM as I need the former for using galera-cluster for redundancy.
I copied (description follows) ...
- 563
37
votes
2
answers
41k
views
What does 'ibfk' stand for in MySQL?
If I create a foreign key constraint for table 'photos' in phpmyadmin, I later see that the constraint is named 'photos_ibfk_1', and the next constraint is called 'photos_ibfk_2', etc. From this I ...
- 475
36
votes
3
answers
78k
views
How to speed up select distinct?
I have a simple select distinct on some time series data:
SELECT DISTINCT user_id
FROM events
WHERE project_id = 6
AND time > '2015-01-11 8:00:00'
AND time < '2015-02-10 8:00:00';
And it takes ...
- 637
35
votes
2
answers
93k
views
Deleting & Reclaiming space from InnoDB table
I have a 700GB InnoDB table which I'm not writing any more data to (only reading).
I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete ...
- 979
34
votes
3
answers
43k
views
Why do I get a PostgreSQL permission error when specifying a tablespace in the "create database" command?
When I create a database in PostgreSQL without explicitly specifying a default tablespace the database is created without issue (I'm logged in as the pgsys user):
postgres=> create database rich1;
...
- 441
32
votes
7
answers
49k
views
Does innodb_file_per_table affect mysql performance?
There are many articles exaggerating (IMHO) the need for innodb_file_per_table. I understand that with innodb_file_per_table there should be better control over individual tables, like being able to ...
- 4,531
32
votes
3
answers
65k
views
GRANT USAGE on all schemas in a database?
I want to GRANT USAGE to a user/role for a given database. The database has many schemas.
I know there's an ON ALL TABLES IN SCHEMA, but I want "all schemas". I tried GRANT USAGE .. ON DATABASE, but ...
- 431
32
votes
2
answers
24k
views
Amazon RDS for MySQL vs installing MySQL on an Amazon EC2 instance
At work, we host all our webservers on Amazon EC2 and usually have used MySQL databases installed on the same box as our Apache webserver, and communicated with them on localhost. We now face a need ...
- 1,095
31
votes
3
answers
66k
views
innodb_file_format Barracuda
I have a couple questions for those more familiar. Most of my instances have been running Antelope despite having support for Barracuda.
I was looking to play around with some compresses innodb ...
- 5,263
31
votes
6
answers
57k
views
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
- 937
30
votes
3
answers
62k
views
ALTER primary key column from INT to BIGINT in production (MySQL 5.6.19a)
Some INNODB tables in our production database are about to hit the INT AUTO_INCREMENT limit of 2147483647 and we need to alter them to BIGINT otherwise writes will start failing.
The tables are in a ...
- 403
29
votes
4
answers
17k
views
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then ...
- 4,299
29
votes
1
answer
36k
views
MySQL transaction size - how big is too big?
I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction.
There are many aspects, and the imports may yield anywhere between 100k-...
- 413
28
votes
2
answers
87k
views
`ERROR 1114 (HY000) the table ... is full` with innodb_file_per_table set to autoextend
I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
- 537
28
votes
7
answers
68k
views
Fastest way to check if InnoDB table has changed
My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.
My ...
- 540
26
votes
1
answer
43k
views
Multiple databases in same AWS RDS PostgreSQL instance
I have an existing database in a RDS AWS PostgreSQL instance. I need to create one more database in this instance as spinning up a separate cluster will be expensive. There doesn't seem to be any ...
- 565
26
votes
1
answer
51k
views
Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity
I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.
I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual ...
- 363
25
votes
5
answers
65k
views
Making sense of INNODB buffer pool stats
After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. ...
- 479
25
votes
2
answers
39k
views
What to set innodb_buffer_pool and why..?
I have 170GB of InnoDB index and Data.
I have to readjust the innodb_buffer_pool size for better performance.The Max table size of a InnoDB table(Index+data) is 28GB.
So what should be the optimal ...
- 9,717
25
votes
4
answers
63k
views
How do you identify InnoDB table corruption?
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and ...
- 3,934
25
votes
2
answers
57k
views
How do I shrink the innodb file ibdata1 without dumping all databases?
InnoDB stores all tables in one big file ibdata1.
After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and re-...
- 816
25
votes
3
answers
19k
views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
- 23.5k
24
votes
6
answers
44k
views
DELETE command not completing on 30,000,000 row table
I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. ...
- 551
24
votes
3
answers
60k
views
MySQL InnoDB page_cleaner settings might not be optimal
Seeing this note in mysqld.log:
[Note] InnoDB: page_cleaner: 1000ms intended loop took 15888ms. The settings might not be optimal. (flushed=200 and evicted=0, during the time.)
There seems to be ...
- 352
24
votes
2
answers
24k
views
Percona vs MySQL
What is Percona?
How does it differ from MySQL?
When should we consider switching (or upgrading) from stock MySQL to Percona?
To add some specifics in our situation we almost exclusively use ...
- 510
24
votes
3
answers
11k
views
Is the overhead of frequent query cache invalidation ever worth it?
I'm currently working on a MySQL database where we are seeing a large number of invalidations from the query cache, primarily because of the high number of INSERT, DELETE and UPDATE statements that ...
- 405
23
votes
1
answer
48k
views
InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT
I am working with mysql version 5.7.14 (homebrew installation on OS X El Capitan). My system did not shutdown properly while the mysql was running and after rebooting when i try starting the mysql ...
23
votes
2
answers
29k
views
Is it possible to change ENUM() lists?
I wasn't sure if changing ENUM() list isn't possible so I made a test.
In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no').
Then I executed...
...
- 333
22
votes
2
answers
21k
views
Does amazon rds PostgreSQL require vacuum?
It isn't clear if auto vacuum is running in rds. Thanks!
- 221