InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
3 views

Get the latest record and count them with time interval

I have a table as below id fk_sales_id status created_at 1 001 5 2015-02-17 16:45:44 2 001 8 2015-02-18 18:45:44 3 002 30 2015-02-20 16:45:44 ...
1
vote
1answer
298 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
1
vote
2answers
2k views

MySql InnoDB tables are missing after restarting

I had a MySql database with a few InnoDB tables. After restarting the server, and starting MySql Server again, I connected to the MySql Server using MySql Workbench, and couldn't see the database at ...
0
votes
0answers
31 views

MySQL is pretty slow after 50-100k rows whats happen?

i have trying now a lots of time, and i try to find a way there make sense to find out of my problem, but this problem stil make a lot perfumes issues. i testing it of on a developer server ( 2 ...
1
vote
2answers
55 views

Large ibdata file in MySQL

I am having MySQL database with all my tables as InnoDB with file_per_table config on. But Still I m seeing huge ibdata file ( ~50GB). All tables are having .ibd file as well. This is a machine I have ...
0
votes
0answers
39 views

Why is MySQL not saving inserted data?

So I have the following insert statement that I run through MySQL Workbench: INSERT INTO PeakOnlineCount (time_stamp, LocationId, Count) values ('2015-03-26', 0, 1) If I run the following query: ...
0
votes
1answer
193 views

How can i import 150GB database after deleting ibdata1,logfile in MYSQL?

I have 150GB database and my ibdata1 file size is 240GB so i want shrink the ibdata1 file so for that i have to export the DB and then drop the tables and then stop mysql, delete the ibdata1 file and ...
0
votes
0answers
17 views

How to restore mysql database in XAMPP using backup of mysql folder?

I have a backup of the entire xampp/mysql folder. How can I use it to recover my old db on a fresh installation of XAMPP? Simply copying the old xampp/mysql folder to new xampp leads to mysql errors ...
8
votes
5answers
15k 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 ...
1
vote
1answer
1k 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 ...
0
votes
2answers
23 views

Slow database drop in mysql

Just installed a new 24 core server and it is noticeably slower when doing truncates and drops. Example: $ mysql -u root lmcdental_test2 Reading table information for completion of table and column ...
0
votes
1answer
15 views

I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB?

FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below: Currently a database I am in charge of locks up every once ...
3
votes
1answer
3k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
0
votes
0answers
38 views

Percona Master-Master lag issues

I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation. What could be causing ...
0
votes
1answer
14 views

Clustering MySQL with InnoDB engine

I have a MySQL single server setup with all tables using the InnoDB engine (because I need support for foreign keys). As response times are getting longer, I now believe I need to create a cluster of ...
1
vote
1answer
23 views

“Unknown table engine 'innodb'” error after changing my.cnf

I am getting "Unknown table engine 'innodb'" for some query after changing my my.cnf. I have reverted the old my.cnf and restarted mysql and now I can see innodb. Could you please help me figure out ...
0
votes
2answers
345 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
1answer
86 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 ...
1
vote
2answers
1k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
1
vote
1answer
1k views

ibdata1 corrupted, recover from ib_logfile?

A failing hard drive have left me with a corrupted ibdata1 file. I know where the file starts but not where it ends. The hard drive recovery software claims the file is larger than the hard drive ...
4
votes
1answer
47 views

Does MySQL exectution plan depend on available indexes?

Does MySQL choose an execution plan for a given query taking into account what indexes are available, or does it first choose an execution plan and then uses indexes if they are available? ...
0
votes
1answer
27 views

Complex query performance in MySQL InnoDB versus MyISAM

I am running on MariaDB 10.0.15 on a 4GB RAM cloud server with Local SSD disk for database. My model: users <n---n> right_holders <n---n> tracks ---n> raw_detections I have this ...
1
vote
2answers
45 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 ...
2
votes
2answers
95 views

mysql write lock

I dont add any manual locking or any transaction anything, just manual running queries from php script, and execute below two update queries at the same time update table_x set status = 'PROCESSING' ...
2
votes
1answer
307 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
3
votes
1answer
760 views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
0
votes
2answers
2k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...
1
vote
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
1
vote
1answer
265 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
0
votes
1answer
26 views

Yet another “ERROR 1005 can't create table” question

Well, I've been looking at the ERROR 1005 questions, but I haven't been able to see my error among the issues addressed by those questions. This code is generated by MySQL Workbench under Linux. Can ...
3
votes
4answers
640 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
0
votes
1answer
46 views

Why does InnoDB allow a 772 byte unique constraint?

I've defined a unique constraint for two columns: A bigint and a VARCHAR(256) COLLATE utf8mb4_unicode_ci Then this error occured (which I'm already familiar with): Specified key was too long; max ...
0
votes
1answer
167 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
108 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, ...
0
votes
1answer
42 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
0answers
19 views

Big cpu caused by mysql

I have big cpu caused by mysql, 3 vcores, centos 6 64bits http://s3.postimg.org/z8l73e98z/mysql.png [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql symbolic-links=0 ...
3
votes
2answers
1k 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 ...
1
vote
2answers
2k 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
vote
2answers
1k views

Hyperthreading & MySQL InnoDB Thread Concurrency Performance

I have a dedicated DB Server with the following specs" Ubuntu 12.04 LTS Server Intel(R) Xeon(R) CPU X5670 @ 2.93GHz 12 Cores MySQL 5.5.29 64 GB RAM RAID10 We are currently doing Hyperthreading ...
0
votes
1answer
12 views

What if we skip the slave by counter 1 when it failed at event 1 of a transaction which has events 2 and 3

We have a master-slave replication which has all Innodb data. I ran 3 update statements under one transaction on master. ============= start transaction update T1 update T2 update T3 end ...
0
votes
2answers
178 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
2answers
30 views

troubleshoot high value for mysql innodb pages written per second

Our database is writting about 600 pages a second to its innodb buffer pools. Usually this value is about 10 pages/sec, so the server is experiencing a very high IO utilization, around 50%. Is there ...
0
votes
1answer
592 views

How to start mysqld with read-only data directory?

I'm trying to start MariaDB in a docker container, using a data directory from the host mounted read-only. MySQLd is of course shut down on the host. Unfortunately, even with adding the read_only flag ...
4
votes
4answers
10k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
0
votes
1answer
45 views

Doubts about Converting MyISAM to InnoDB (master/slave and more)

I want to convert my MySQL DB (46 Tables, 2.1 GB) from MyISAM to InnoDB. I have a couple of questions about this process. Since I have a Master/Slave structure, do I need to manage this and change ...
36
votes
3answers
58k 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 then DBA for MySQL so need some help Setup The servers are quite hefty with an ...
5
votes
2answers
10k views

Using multiple cores for single MySQL queries on Debian

I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four. I am doing expensive joins on large tables, ...
2
votes
1answer
134 views

How mysql (innodb) handles auto increment column

I am working on a OLTP environment with multiple parallel write operation. I found this in documentation about auto increment locking but still i am interested to know much about it in details. Can ...
0
votes
1answer
202 views

Blob datatype issue with log file

I am getting continuously below error message in mysqld.log [ERROR] InnoDB: The total blob data length (10152274) is greater than 10% of the redo log file size (3072). Please increase ...
0
votes
2answers
30 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 ...