InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

1
vote
1answer
162 views

Fastest engine for Mass Updates (only updates done on table)

I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the ...
0
votes
1answer
1k views

Cannot Start mySQL Daemon with Error “mysqld from pid file … ended”

I cannot get MySQL to run. It was working just fine hours ago, but upon starting, repeatedly ends without telling me very much. Here are my logs [myusername@location ~/mysql]$ ./bin/mysqld_safe --log-...
0
votes
1answer
432 views

MySQL Entity Relationship Problem - 3-way join table?

This is a db relationship question. I have a scenario which I believe I have come up with a solution - however, I can't be sure it's a manageable one, or even correct. The scenario is as follows: ...
1
vote
1answer
173 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
0
votes
1answer
119 views

InnoDB compression: memory usage

InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on ...
2
votes
2answers
79 views

In MySQL, how exactly does data flows from query to disk?

I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then ...
2
votes
1answer
322 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
1
vote
0answers
51 views

What exactly is MySQL Spin rounds per wait in SEMAPHORES section?

I am facing slowness issue on MySQL Slave. I am suspecting this due to OS I/O as i compared Spin rounds per wait values with PROD. Slave have higher values. Also slave has less resources compared to ...
19
votes
3answers
24k 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 ...
1
vote
2answers
2k views

Mysql Deadlock on update query on same row

My server is experiencing occasional deadlocks on two update queries on same row both using index (primary key). The conflicting queries are: UPDATE auth_user SET rep_score=185 WHERE (auth_user....
2
votes
2answers
99 views

Will hacking away ibdata1 destroy databases with only MyISAM tables?

I made a mistake. I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so. I know that safely shrinking ...
0
votes
2answers
142 views

Does MySQL Workbench lock database during migration process?

I am doing a migration of my database from one server (RDS) to another (localhost) via the MySQL workbench tool. I want to know if during this process my database will be locked for write and read ...
1
vote
1answer
554 views

Mysql Tuning for a small server

I have a small Cloud Server running under Ubuntu 2 core, 2GB ram and 2GB swap, with Plesk Panel, Apache, Nginx as reverse proxy and Mysql 5.5. I have about 18 domains hosted here and each domain has ...
1
vote
2answers
39 views

guessing unused junk space in table. (innodb mysql/mariadb )

We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am ...
1
vote
1answer
2k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
4
votes
1answer
485 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
1
vote
1answer
166 views

Write-lock a whole table during transaction

I need to perform a delicate operation to my table in which I will solely insert, delete and select upon all of my rows and no God may interfere with the table during this operation: the table will be ...
1
vote
1answer
336 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
6
votes
3answers
13k views

Mysql slowly uses memory until it starts to use swap

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more ...
16
votes
3answers
6k 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 ...
0
votes
0answers
25 views

InnoDB LSN in the future - how to insert the “required amount” of data?

Please forgive me - I'm not a DBA, I'm a lowly devops whose mysql DBA went on vacation the day before this happened and is gone for 10 days. I'm learning as much as I can about mysql/innodb/LSNs but I'...
0
votes
1answer
33 views

How costly are InnoDB rollbacks?

I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement? To give a context, we use spring+hibernate and set @...
0
votes
0answers
29 views

Howto defrag a Galera Cluster (5.5.41) with innodb_file_per_table = off? After TRUNCATE a huge table is defrag needed?

First question how to defragment our cluster (see Galera and MariaDB config)? I don't know how Galera replicates the new data files size? Second question, is defrag needed? We truncated a 30 GB table ...
4
votes
2answers
6k 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? +---------------------...
69
votes
4answers
126k 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 ...
0
votes
1answer
58 views

MySQL MVCC implementation

The question is about the behavior of simultaneous SELECT and UPDATE in MySQL InnoDB table: We have a relatively large table which we periodically scan reading several fields including a field named, ...
3
votes
1answer
78 views

MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup. How to solve ...
2
votes
1answer
7k views

Configuring MySQL (my.cnf) for MyISAM and InnoDB

I want to convert some (not all) the MyISAM tables to InnoDB. Current my.cnf Show engines output Server has two 6-core Xeon processors (2 threads per core = 24 threads) What would be the best ...
0
votes
0answers
37 views

How to improve my big MySQL database with low concurrence?

I have a MySQL database who is been used to storage a large amount of data, it doesn't have a lot of concurrence( its minimum), but every day a big amounts of data is loaded and also a profiling ...
0
votes
0answers
26 views

InnoDB: Operating system error number 9

I got error when trying to backup with percona. Backup command [xtrabackup --backup --target-dir=/data/backups/mysql/] was worked and When I try to prepare backup [xtrabackup --prepare --target-dir=/...
0
votes
0answers
20 views

Innodb Change Buffer merge

I have been testing the performance of my innodb mysql. I ran a script which inserts and selects one after the other for 100000 times. So according to the change buffer concept, the inserted ...
1
vote
1answer
36 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
1
vote
1answer
62 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
1
vote
1answer
92 views

howto read/interpret “show engine innodb mutex” output?

We use a 3 node Galera cluster with version 5.5.41-MariaDB-wsrep MariaDB [(none)]> show engine innodb mutex; +--------+---------------------------------+------------------+ | Type | Name ...
0
votes
1answer
34 views

Mysql repetition of column in Criteria

What happens when I repeat the same column in the criteria part but with different values . For example col1 ='val1' or col1 = 'val2' or col1 = 'val3' . Assume col1 is not indexed. Will mysql ...
1
vote
1answer
86 views

How to fix `The table '/tmp/mysql/#sql_xxxxx' is full`?

Ubuntu server 16.04, php7, nginx 1.10, mysql 5.7 in Hyper-V on Windows 10 host How to fix The table '/tmp/mysql/#sql_xxxxx' is full ? In mysql-error.log [ERROR] InnoDB: Tablespace ...
0
votes
1answer
41 views

MySQL disk scanning algorithm [closed]

How does MySQL (InnoDB engine) read data from disk? What scanning algorithm does it use? Since the rows are stored in data pages will it read the entire data page to get the resulting rows?
2
votes
2answers
911 views

tmp_table_size with mostly InnoDB tables

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two ...
1
vote
0answers
45 views

Getting InnoDb internal Errors on every query run

I am having following errors in error.log at the run of each query due to which I am not able to use error.log properly. 2016-05-30 09:39:58 7f7c7e3cb700 InnoDB: Error: Column last_update in table "...
1
vote
1answer
65 views

Composite Primary keys and auto increment?

i'm developing SaaS app with multi-tenancy, and i've decide to use single DB (MySQL Innodb for now) for client's data. I chose to use composite primary keys like PK(client_id, id). I have 2 ways here,...
6
votes
3answers
15k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
6
votes
2answers
16k views

MySQL: Error reading communication packets

I get this warning in mysql, [Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets) I have been through few topics in google ...
0
votes
0answers
20 views

What are the best settings of InnoDB for FULLTEXT queries?

This question has been asked before, but I was unable to find the right answer for my situation. We have a local server with Quad core and 32GB RAM dedicated for a single InnoDB database, which ...
0
votes
1answer
68 views

improving performance of concurrent inserts on innodb with randomized primary

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up ...
1
vote
2answers
41 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
29 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
1
vote
1answer
34 views

What happens to mysql information_schema after switching to innodb_file_per_table?

We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box. We've backed all our dbs up and about to ...
0
votes
1answer
52 views

how to purge / shrink .idb tables

I know this has been asked many times but, what is the proper configuration to succesfully purge / shrink .idb tables? I have tried to use optimize table table_name but after the query the .idb ...
1
vote
0answers
67 views

MySQL is using swap memory while I have plenty of free RAM

I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read ...
1
vote
1answer
35 views

MySQL innodb_log_file_size change is not being recognized

I am trying to set the innodb_log_file_size option in a MySQL 14.14 (distrib. 5.6.30) installation on Ubuntu 15.10 to solve an issue (importing a database with large BLOB fields) however the log size ...