InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
1answer
16 views

Sync two MySQL servers data, while changing the Engine in the slave

I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer ...
1
vote
1answer
158 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
425 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
166 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
118 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
64 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
308 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 ...
3
votes
0answers
26 views

MySQL Innodb_row_lock_current_waits is 2^64 - 1

I have a MySQL 5.6 server which we monitor using Nagios. Over the weekend, it's started complaining about the Innodb_row_lock_current_waits, which currently has a value of 18446744073709551615, yes, ...
1
vote
0answers
29 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....
1
vote
0answers
38 views

MySQL lost connection during query after specific record

I started getting the usual Error Code 2013 Lost connection to MYSQL server during query at a random moment when working on a PHP script. Some stuffs in the software still works, but the specific ...
0
votes
2answers
221 views

How can delete an orphaned temporary table on Mysql Innodb?

I was issuing an Alter table statement and suddenly the server crashed, Now i can not run any alter table to that table because it shows me the error 1050 and it says that a table named #sql1504d ...
2
votes
2answers
86 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
133 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
2answers
51 views

Converting from InnoDB to InnoDB by file

I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM. In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM. I really want to convert those 20 tables over to InnoDB ...
1
vote
1answer
476 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
33 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
482 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
160 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 ...
0
votes
0answers
24 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
1
vote
1answer
332 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 ...
3
votes
1answer
656 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5....
15
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
23 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
0answers
32 views

MySQL insert with sub select slow

We are using Python and LOAD DATA INFILE to load data from CSVs into our staging database. From staging we have sql scripts moving the data into our actual production database. LOAD DATA INFILE is ...
0
votes
0answers
24 views

Update on a table on MySQL Innodb seems to hang / lock forerever

When performing an update on a MySQL database for an InnoDB table, the update sentence never finishes. This is the update sentence from the MySQL query log: UPDATE `bb_plan` SET `creation_date` = '...
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 @...
1
vote
1answer
282 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
1
vote
1answer
182 views

Why do innodb tables get corrupted so easily?

I'm upgrading a legacy system running MySQL 5.1 all the way to MariaDB 10. We're on a brand new Windows server, running XAMPP. I've done plenty of DB migrations in my time but this one has just been ...
2
votes
1answer
247 views

innodb , mysql Indexes difference when adding index

When I am altering one of my MySQL table to add an index I am getting below error in error log. Table a/b contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined ...
0
votes
0answers
24 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 ...
0
votes
1answer
159 views

Hash index for InnoDB

I saw a database schema that looked like this CREATE TABLE foo ( name_hash bigint, name varachar(500), a varchar(500), b varchar(500), ... PRIMARY KEY (name_hash), KEY ... ...
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? +---------------------...
66
votes
4answers
123k 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
46 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, ...
0
votes
1answer
210 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
5
votes
2answers
2k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
3
votes
1answer
64 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
35 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 ...
1
vote
1answer
1k views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm -...
0
votes
2answers
1k views

Restore InnoDB to different machine

It was pointed to me from serverfault that the following question would be a better fit here so this is basically my original question from there. I currently have backups that were taken with ...
0
votes
0answers
19 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
19 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 ...
0
votes
1answer
71 views

Memory(buffer pool) not populating on mysql start on Percona 5.6.28

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. ...
5
votes
2answers
270 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------...