Questions tagged [innodb]
InnoDB : MySQL's ACID-compliant Storage Engine
409
questions
3
votes
3answers
1k views
MySQL High Availability for InnoDB
I am looking for MySQL High Availability with huge transactions. I prefer to go with InnoDB Engine.
I prefer to use MySQL Master - Slave Replication. However, this option provides us Manual fail over ...
3
votes
2answers
6k views
How are DB locks tied to connections and sessions?
I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
3
votes
2answers
6k views
Two MySQL Server accessing same database over NFS
I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:
...
2
votes
2answers
6k views
How to analyse innodb status on deadlock in insert Query?
I have a dead lock which i have posted here. I managed to get the innodb status from my server. Here is the Innodb status.
OS WAIT ARRAY INFO: reservation count 247864, signal count 247470
Mutex ...
2
votes
1answer
1k views
Why is there no data loss with mysql innodb_flush_log_at_trx_commit set to 0?
Here is the MySQL info:
mysql> show variables like 'innodb%flush%log%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-----...
1
vote
1answer
9k views
MySQL database drop insanely slow
I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server.
I installed it under my user home directory like I'm used to doing.
But this time the queries are extremely slow to ...
1
vote
1answer
2k views
Fill ibdata1 With Zeros/Nulls/etc Or Change A Table's Tablespace
I have a MySQL 5.5 server running on CentOS 6. The MySQL package is a MySQL-released version (MySQL-server-5.5.28-1.el6.x86_64), not the stock CentOS one. I have moved an existing database over from a ...
0
votes
1answer
468 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 ...
8
votes
3answers
6k views
Using MySQL triggers or transactions?
I want to ask your opinion in using MySQL triggers or transactions in a website.
Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
5
votes
1answer
6k views
Slow InnoDB INSERT/UPDATE/DELETE
Background
A couple of weeks ago all of my INSERT's/UPDATE's/DELETE's became overall slow (not SELECT's). Before that, everything was normal (mysql statements executed in <0,01 seconds). Now a ...
5
votes
3answers
3k views
Drop Tables with Bigger size
We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that ...
4
votes
3answers
12k views
innodb_buffer_pool_size not changed
I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf
default-storage-engine = innodb
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256M
innodb_thread_concurrency = 8
...
4
votes
1answer
9k views
Can I reset the ibdata1 and ib_logfile files when innodb_file_per_table is already in place
I am running into an issue where sensitive data was not encrypted properly at the application level of our production system.
Even though the guilty records have been removed or corrected, scanning ...
3
votes
1answer
13k views
Is it possible to restore a MySql database only using files from Data directory?
I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to
c:/...
3
votes
2answers
423 views
Should the Redo log on a production MySQL server be configured to another location?
Concerning the innoDB storage engine, should the innodb_log_group_home_dir option (The directory path to the InnoDB log files) be configured to another disk, other than the default directory, in order ...
3
votes
3answers
11k views
Purge old records from mysql ibdata1 file
Is it possible to purge old records from an ibdata1 file without taking down the database attached to it?
Records that have been deleted from the database are still present when I scan ibdata1 for ...
3
votes
2answers
4k views
Database space doesn't match ibdata1 size
I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.
In the MySql workbench, when I query for the databases used space, with this query
SELECT ...
3
votes
2answers
2k views
MySql replication with Raw Data and InnoDB tables
I want to start replication on DB that has many InnoDB tables and MyISAM tables.
I did everything according to manual Creating a Data Snapshot Using Raw Data Files.
Firstly, I tried copied all ...
2
votes
1answer
322 views
Is there any way to use different InnoDB settings for different databases on the same server?
From what i know, this is not possible but i'm surprised its not highly requested feature. I'd love to be able to have one db using InnoDB in ACID compliant mode and another db on the same server used ...
2
votes
3answers
3k views
Backup / Export data from MySQL 5.5 attachments table keeps failing!
Can anyone please help! - I have a large table in a MySQL 5.5 database. It is a table which holds a mixture of blobs/binary data and just data rows with links to file paths. It has just over a million ...
2
votes
1answer
555 views
How does subpartitioning actually work and what physical files are created?
I can't understand how the subpartitioning works in mysql.
I am reading the following example:
create table invoices (
customer_id int not null,
product_id int not null,
amount decimal (8,...
2
votes
1answer
800 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 ...
2
votes
1answer
31k views
How to estimate the disk space for 1 million rows if I have a table of fewer rows in InnoDB MySQL [duplicate]
I want to estimate the disk space for 1 million rows.
Currently, I have 8527959 rows, Data Length of 744 MB and Index Length 989.4 MB.
Please let me know if my calculations are correct.
Total Data ...
2
votes
3answers
2k views
How long can Deadlock info hold in innodb status page?
When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp
------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31
Questions
Any idea how long Deadlock ...
2
votes
2answers
2k views
Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB? [closed]
Title says it all. However i have to enter at least 30 characters.
1
vote
1answer
2k views
Dedicated mysql server slow response [closed]
I have server in Kimsufi with 2gb RAM but my server is very slow , with simple hosting in ovh it responds quickly ...
With pingdom.com test i have for
server: 10 sec
and simple hosting: 2sec
is ...
1
vote
1answer
61 views
How can I 'further' improve my table and queries
This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query.
I also changed my application so that unused filters weren't ...
1
vote
2answers
3k views
ERROR 1031 (HY000): Table storage engine for 'proc' doesn't have this option
I am new to MySQL administration, and this week a customer came to me with a problem. They created an application with Wamp in a Windows Server environment.
For some reason when listing the tables in ...
1
vote
1answer
2k views
How does innodb_stats_on_metadata relate to query execution plan stability?
The MySQL documentation for innodb_stats_on_metadata says "It can also improve the stability of execution plans for queries that involve InnoDB tables." That is a little vague. What is an example ...
1
vote
1answer
402 views
InnoDB issue after power outage a few weeks ago - no backup
A few weeks ago we had a power outage on one of our web servers that hosts several websites, we didn't think it affected anything and everything came back up as normal as far as we were aware. I have ...
1
vote
1answer
335 views
MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result
MySQL 8.0.16, 2 simple RC transactions, each trying to insert and delete rows:
CREATE TABLE test1 (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100),
PRIMARY KEY (id)
) ...
0
votes
1answer
101 views
In InnoDB, how does fuzzy checkpointing's recovery consistency work?
I've looked the documentation quite thoroughly but still cannot figure out so asking this question.
In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets ...
0
votes
3answers
254 views
Control InnoDB buffer pool allocation in MySQL (5.7+)
I have some large tables which causes to fill the InnoDB buffer pool and removing other data.
Is there any way how can I control the InnoDB buffer allocation in one shared database?
Like:
a) Limit ...
0
votes
2answers
508 views
Do I need to reindex my table in mysql after delete?
I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase ...
29
votes
7answers
37k views
Why using innodb_file_per_table?
There are many articles exaggerating (IMHO of course) the need for innodb_file_per_table. I understand that with innodb_file_per_table, there should be a better control over the individual tables; ...
23
votes
1answer
27k 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-...
16
votes
2answers
4k views
Rows missing after online conversion from MyISAM to InnoDB
We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.
Now that the ...
14
votes
2answers
51k views
innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition
In one of my production environments, we have two instances running on a RedHat cluster, with one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool ...
9
votes
2answers
45k views
Select query takes more time than it should
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:
Below is some of ...
12
votes
1answer
10k views
MySQL: Unique constraint on large column
I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column.
MySQL appears to ...
11
votes
1answer
17k views
Row size error with MySQL
I'm running a MySQL server on my Macbook (for testing). Version is 5.6.20 from Homebrew. I started running into "Row size too large" errors, and I've been able to reduce it down to this test case. ...
5
votes
5answers
16k views
MySQL said “InnoDB: Initializing buffer pool, size = 128.0M”, does it mean it is using 128MB RAM?
I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said
InnoDB: Initializing buffer pool, size = 128.0M
Does it mean even I am using in such a small size, ...
13
votes
1answer
10k 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.
8
votes
3answers
39k views
Finding and fixing InnoDB index corruption
I encountered a new issue yesterday with one of my MySQL 5.5 slave DBs that runs on EC2 in AWS. The DB was created from a snapshot of another slave. The data is correct, but for at least one table a ...
10
votes
4answers
15k views
show table name + number of records for each table in a mysql innodb database
How to list all tables in the current database, together the number of rows of the table.
In other words, can you think of a query to come up with something like this in mysql?
+---------------------...
10
votes
2answers
16k views
MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
8
votes
1answer
21k views
Error when dropping InnoDB database
I am trying to drop a database in mysql, but every time I get this error
ERROR 2013 (HY000): Lost connection to MySQL server during query
even logged in as root.
Originally, I had a corrupted table ...
7
votes
2answers
41k views
Flush/Clean MySql ib_logfile0 ib_logfile1
Is there a way to flush ib_logfile0 and ib_logfile1 without dumping the tables to a .sql file, deleting, then re-inserting? Somebody entered a couple plain text credit card numbers into the customer ...
7
votes
2answers
15k views
Recover the disk space after deleting rows from table
I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
6
votes
3answers
30k views
How to repair, or drop/create a corrupted table in mysql?
Situation: I have a MySQL database with 104 tables, running locally on XAMPP on windows 7. All the tables have InnoDB engine. XAMPP MySQL is running as windows service. As front end I have PHP (...
