InnoDB : MySQL's ACID-compliant Storage Engine

learn more… | top users | synonyms

0
votes
0answers
29 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 ...
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
0answers
16 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 ...
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 ...
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 ...
4
votes
1answer
45 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
26 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
53 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 ...
1
vote
2answers
39 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' ...
0
votes
1answer
25 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 ...
0
votes
1answer
45 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 ...
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
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 ...
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
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
29 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
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 ...
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 ...
1
vote
1answer
41 views

Concurrency when simulating autoincrement with triggers on MySQL

I am updating an old application to implement virtual tenancy, which involves adding a tenant_id column to the primary key of every single table. The application uses auto-incrementing columns ...
1
vote
1answer
20 views

Acceptable to use Antelope and Barracuda simultaneously?

I've been doing some reading about the Antelope and Barracuda file formats for InnoDB. I recall at least one source mentioning that the two should not be used simultaneously. I can't think of a good ...
0
votes
1answer
18 views

Will a static MySQL database become bigger in file size over time, even without anyone writing to it?

I backed up a database a few weeks ago, then for comparison downloaded it again. It was about 10,000 kB larger. There should not be anyone writing to it, but there are lots of reads. Will a static ...
1
vote
1answer
51 views

What is InnoDB Buffer Usage and how to clear it?

In Mysql Workbench when I go in Server > Server Status there is a field called "InnoDB Buffer Usage" It is "95%" even if there is no query running. Why? It affects my performance in my new queries? ...
3
votes
0answers
46 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
1
vote
2answers
35 views

InnoDB row metadata size (minimum row size)

I'm trying to decide whether to normalize a thing further, and need to know what the smallest a row in InnoDB can be (I've got MySQL 5.6). This is not exactly my case, but for simplicity, let's ...
0
votes
1answer
30 views

MySQL - Changing from system tablespace to file per table

The MySQL database I am working on has the InnoDB tables and indexes stored in the system tablespace which is now > 20Gb. Some years ago I managed a MySQL database that made use of a file per table ...
3
votes
2answers
97 views

How to speed up mysql export and import

I need to perform a DB migration from Server A to Server B. Server A: MySQL version installed 5.0.6 Server B: MySQL version installed 5.6.20 OS: Both are running RedHat Linux 64bit. 16 ...
0
votes
1answer
42 views

What can I do about this MariaDB 10.0 error?

I have a serious error that crashes the mariadb service, and it won't start up again until I delete the database folder in the data directory of the database I was working on at the time. Here is the ...
0
votes
4answers
43 views

If all tables are myisam, should I use myisam engine instead of default innodb?

I have read all of the innodb/myisam threads and they are confusing. Everyone says innodb but I am assuming that they are saying innodb engine with innodb tables.? The software I am using creates ...
1
vote
4answers
63 views

with MySQL, are multiple single column indexes faster than one multi-column index? [closed]

I have a performance related question, in MySQL, are multiple single column indexes faster than one multi-column index?
0
votes
1answer
59 views

How to optimize a slow query?

I work as an administrator and software developer for a company that provides services to the car selling business. In essence we develop the software that they use when selling cars and such. This ...
0
votes
2answers
10 views

How does InnoDB access disk?

I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page ...
2
votes
2answers
39 views

Question About Large InnoDB Table with Deleted Rows

Our server has a DB that's over 600 GB - we are deleting older rows that are non-essential to contain the growth. We can't optimize the table due to possible downtown on the table but we are ...
1
vote
1answer
94 views

Server stuck in INTERNAL DDL LOG RECOVERY IN PROGRESS

Recently, one of my servers had a crash. I had alot of trouble but managed to restore everything. But, Now, the first background thread upon startup is locked on "INTERNAL DDL LOG RECOVERY IN ...
1
vote
0answers
10 views

How “relatively safe” is the data which was salvaged from corrupted MySQL InnoDB database?

I encountered the corruption like the following. 150208 14:27:52 InnoDB: Error: page 443375 log sequence number 8941 870477908 InnoDB: is in the future! Current system log sequence number 5968 ...
0
votes
1answer
66 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz ...
1
vote
0answers
22 views

InnoDB Deadlocks Involving Currency Transactions

We're dealing with InnoDB deadlocks that are affecting our virtual transaction system. LATEST DETECTED DEADLOCK 150210 20:51:03 *** (1) TRANSACTION: TRANSACTION 0 4240405649, ACTIVE 0 sec, process no ...
0
votes
0answers
24 views

DB Rolls Back And Loses Data

So we've come across an astounding issue here. At random moments, our DB is dropping the database and recreating the table with data from an earlier point in time. Unfortunately general query logs ...
0
votes
0answers
85 views

MYSQL: InnoDB inserts wont rollback transaction inside a stored procedure

I have this stored procedure: START TRANSACTION; code_gen_loop: WHILE @Codes > 0 DO IF MOD( @Codes, 10000 ) = 0 THEN EXECUTE qry_status_stmt2; EXECUTE qry_status_stmt3; ...
0
votes
1answer
34 views

Change Storage Engine on Referenced Tables

i need to change storage engine from 'InnoDB' to 'MyISAM' on some tables of different databases. So i made a script file to do so: #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names ...
1
vote
2answers
44 views

How to Permanently solve MySQL error 2002 (HY000)

I have a problem on my cloud server with 2GB of RAM and 40GB Hard Disk that's hosting a Wordpress website that receives around 1000-2000 hits a day. My problem is that MySQL is always crashing and my ...
1
vote
2answers
68 views

Invision Power Board 3.4.6 - mysql locks during query with pending updates

about 2 weeks ago, we started experiencing random lockups and slowdowns on our forums. We've been running this site for around 6 years minimum with no issues. the site and db are on dedicated vms. The ...
20
votes
6answers
4k views

DELETE command not completing on 30,000,000 row table

I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data made by an error by our programmer. Before I add any new, ...
0
votes
2answers
50 views

Is possible to backup database online using rsync?

Some people tell me that the backup database on-the-fly using rsync is possible. I think it is possible but not safe because some log, buffer... is not flushed. Database is being recovered from the ...
1
vote
0answers
37 views

MySQL - Cannot add new Foreign Key

I'm using magento and it does some re-index for a product flat table structure. In this process a new table is created. The SQL for that table is something like: CREATE TABLE ...
0
votes
2answers
35 views

Is it a good idea to add a reserved field to a large MySQL table?

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible. Is it a good idea to add a text column to each table (say, meta) to save some ...
0
votes
0answers
40 views

54M InnoDB keeps shutting down unexpectedly [duplicate]

I have a mariadb server running several databases with very small load. From time to time, when I have 20+ users browsing one of the wordpress websites i have, the daemon just shuts down with the ...
2
votes
3answers
304 views

UPDATE waits for lock too long after SELECT in MySQL (Innodb)

I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen ...