Questions tagged [innodb]

InnoDB : MySQL's ACID-compliant Storage Engine

Filter by
Sorted by
Tagged with
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 (...

1
3 4
5
6 7
9