Tagged Questions

InnoDB is the main ACID-compliant Storage Engine used in MySQL.

learn more… | top users | synonyms

-1
votes
0answers
7 views

Set MySQL innodb buffer pool size on IIS7

I am using VPS, Windows 2008 r2, IIS7, MYSQL 5.1.73, Hardward Config: Ram 2GB , Hardisk 40GB On this VPS only 1 .NET application is running. Application is currently using by only 5 users. I found ...
3
votes
1answer
50 views

High CPU on MySql Fusion-io server

I've just finished reading this old Q&A which had some really good detailed information on a similar setup to ours, though unfortunately our problem (now) is not with replication. MySQL ...
-1
votes
0answers
24 views

Do i need reindex mysql table after bulk delete? [migrated]

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 ...
0
votes
0answers
78 views

How to restore a corrupted MySQL master database from a slave database?

I recently discovered that the master db in a setup I'm running became corrupted. The slave was getting all of the data correctly so I have a solid backup. What I'd like to do is use replication to ...
0
votes
0answers
56 views

Never ending transaction problems after mysql 5.0->5.6 upgrade

I upgraded our entire backend mysql db from 5.0 to 5.6, including changing a few tables to innodb, and we've been having nothing but problems with never ending transactions since. I still have a ...
-1
votes
1answer
92 views

Innodb performs IO Operation when idle

I am currently doing some stress testing where I am pushing inserts to the database as fast as I can. The simulation inserts 10000 records per second. After inserting around 200 million records I ...
0
votes
1answer
43 views

Restore InnoDB to different machine

I currently have backups that were taken with Percona XtraBackup of all my mySQL databases. My specific scenario is that I want to restore a month old InnoDB database snapshot to my local machine for ...
1
vote
1answer
71 views

MySQL suddenly taking 2000 ms per request, instead of 200

UPDATE: finally remembered to look at munin, and it seems that one of the HDDs is the root of the problem after all, unless I'm totally reading this wrong. Here's the munin output: ...
0
votes
0answers
50 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
1
vote
1answer
31 views

Innodb tables not showing

If I run query show table in mysql console, the innodb tables will be listed. But when I tried a query like select * from tablename, it says table does not exist. How can I fix this issue and make ...
0
votes
1answer
51 views

Re-ocurring issue with keys on temp tables

We run a large forum with lots of reads and writes, particularly to the posts and topics tables which are both innodb. Last week I started doing 12 hourly backups with innobackupex because mysqldump ...
0
votes
1answer
514 views

Is enabling MySQL query_cache_size Innodb Lock Safe?

The query_cache_size looks like the kind of setting that one would usually want enabled which puzzled me since it defaults to 0. Then I read the following about the query_cache_wlock_invalidate ...
0
votes
0answers
57 views

MySQL, Table marked as crashed after vServer reboot

i am using a vServer in a data storage center. Sometimes the host machine is rebooted by data center admins which causes the vServer to be rebooted too. After every reboot the database crashes, the ...
0
votes
0answers
130 views

How to fix mysql error InnoDB?

[root@localhost ~]# tail -f /var/log/mysqld.log 140316 5:14:01 [ERROR] Plugin 'InnoDB' init function returned error. 140316 5:14:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. ...
0
votes
2answers
196 views

MySQL's InnoDb performance downfall

I'm trying to configure a MySQL production server using InnoDb and I can not get the performance I want out of it. I've used Percona tools to give me the following configurations: [mysql] # CLIENT # ...
1
vote
0answers
91 views

mariadb 5.5.36 with innodb plugin

I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
1
vote
1answer
155 views

changing mySQL (5.6) database from MyISAM to InnoDB

I have recently upgraded my CPanel server from mySQL 5.5 to mySQL 5.6 as it now supports full-text searches for InnoDB. Now, I'm not so great at mySQL so I was wondering is there anything special I ...
0
votes
1answer
24 views

Over 100% of stage done during DROP COLUMN

I'm running a DROP COLUMN on the primary key column (I know, it's terrible but I had to do it). There's a progress indicator: Stage: 1 of 2 'copy to tmp table' 107% of stage done I haven't been ...
2
votes
1answer
338 views

MySQL Periodically Crashing

MySQL Seems to be crashing quite often, I'd say every hour or so. The only thing running on this server that is using MySQL is a Drupal installation that I'm working on, there's only one person ...
1
vote
1answer
37 views

MySQL InnoDB buffer_pool_size not increasing?

There are no errors in the log file, but this doesn't seem to add up to me. Maybe I'm misunderstanding something: mysql> show variables like '%buffer_pool%'; ...
0
votes
1answer
325 views

Why is innodb engine disabled by default in ubuntu 13.10 mysql server?

We all know that InnoDB is the default engine as of MySQL 5.5 but ... for some reason an extra configuration file zentyal.cnf is included in MySQL's conf.d directory when installing mysql server ...
0
votes
0answers
286 views

MySQL 5.5 : Cannot drop database : Lost connection to MySQL server during query

After a huge crash of my mysql database, I've made a recovery, all is ok except one schema. In phpMyAdmin, this schema doest not cotains any tables, but we i ask mysql to drop the schema (drop ...
0
votes
1answer
31 views

Check if MySQL configuration was applied

I'm performing some heavy MySQL operations at my VM and constantly getting the following error: SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size I ...
0
votes
0answers
873 views

MySQL (MariaDB) crashes frequently

I recently migrated an old server running MySQL to a new VPS running MariaDB 5.5. I don't have too much running on the server (just a few PHP sites) and free memory seems to be OK, but the DB keeps ...
0
votes
0answers
278 views

MySQL 5.6 Create Table VERY slow

I recently upgraded my MySQL server from 5.1 to 5.6 (running on CentOS 6.x). Ever since the upgrade, when I try to perform certain table tasks the queries take a REALLY long time. This is really ...
1
vote
1answer
906 views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
2
votes
1answer
151 views

innodb Table is full and autoexpand

Hi I use bacula and one of the tables is full: JobId 8946: Fatal error: sql_create.c:860 Fill File table Query failed: INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq) ...
0
votes
0answers
81 views

MySQL Tables Disappearing

We're having some issues of tables "magically" disappearing. Here is the log file leading to the point of what seems to be the point of the table disappearing: 131103 9:08:39 InnoDB: Error: table ...
1
vote
2answers
779 views

MySQL SELECT query hangs

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
93 views

How to drop an 8 GB InnoDB table smoothly without global locks

MySQL version: 5.1.63. I am about to DROP a bigger ~8 GB InnoDB table. Last week I dropped a four times bigger table on another machine and a global lock kicked in that took us down for ~90 seconds. ...
1
vote
2answers
4k views

Server crashed, some MySQL databases are broken. Trying to recover with InnoDB recovery, but ibdata1 seems corrupt

I've a problem. Some days ago my root server crashes after a power outage. I realised that the MySQL server won't start. In the logs I find out that an error occured with InnoDB's ibdata1. I've ...
0
votes
1answer
107 views

mysql: unknown option '--skip-innodb'

In trying to disable innodb by adding this to mysql config [mysql] skip-innodb But I get the error shown in title.. I also tried to change [mysql] to [mysqld] with no luck. SHOW ENGINES: ...
0
votes
1answer
231 views

MySQL tmp tables: how to clean up diskspace after killing a copying to tmp table process?

i ran out of disk space while running an alter table on a large table. I restarted MySQL afterwards and checked the MySQL tmp dir. A show status like '%tmp%' lists 5 tmp files and 4 tmp tables. A df ...
0
votes
0answers
120 views

need assistance with my.cnf - 1500% CPU usage

I'm running into a few issues with our new database server. It is a HP G8 with 2 INTEL XEON E5-2650 processors and 32GB of ram. This server is dedicated as a MySQL server (5.1.69) for our intranet ...
0
votes
1answer
503 views

restore mysql from ibdata/frm files

A hard disk failure has left me trying to restore mysql from copy of "data" folder I had dumps of "most" content but am missing some Data folder has idbdata1 / log files / folders of .frm files So, ...
1
vote
1answer
359 views

InnoDB Buffer pool hit rate missing from engine status output

I want to check the buffer pool hit rate for InnoDB on MySQL but when I query the database the hit rate seems to be gone from the results: Using query: SHOW ENGINE INNODB STATUS\G ...
1
vote
1answer
104 views

Does adding a foreign key constraint in MySQL cause a table rebuild?

I need to alter a large table in order to add a foreign key. Does adding the constraint cause the table to be rebuilt? The referenced table already exists and has an index on the column.
1
vote
1answer
199 views

MySQL (innodb) always crashes on big ORDER BY statements

I'm running MySQL on Debian stable (normally installed from the stable packages). Everytime when I do a big ORDER BY, mysql crashes. Example query (innodb tables): SELECT * FROM `table` INNER JOIN ( ...
1
vote
1answer
194 views

Lost Frm files recovering data from ibdata

I was working with a mysql innodb datbase. When I was working with mysqlworkbench accidentally all the .frm files got deleted from mysql data folder but I still have ibdata file in the data folder. I ...
3
votes
2answers
335 views

Dropping Index on 10GB+ InnoDB table takes over 4 hours

This is the table I'm working with: CREATE TABLE IF NOT EXISTS `checklist_answer` ( `id` varchar(36) NOT NULL, `created_by` varchar(36) NOT NULL, `date_created` datetime NOT NULL, ...
0
votes
1answer
3k views

MySQL failed to start. Error 1053. Windows Server 2008 R2

I did a server restart for my web server and on restart, MySQL won't start. If I use taskmanager to kill mysqld.exe and try the start from services console, it gets about 50% through the startup, ...
0
votes
1answer
69 views

Setting innodb_buffer_pool_size in for Mysql

We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions Set ...
0
votes
1answer
120 views

Large InnoDB data size even though entire database is MyISAM?

I'm running mysqltuner to tune up my database and I'm running into a very strange occurrence. Even though the entire database is MyISAM (all tables) it says the following: [!!] InnoDB data size / ...
0
votes
1answer
381 views

MySQL “Too many connections” + > 6000 queries / second

We have quite a big mysql (innodb) database with a lot of insert queries and 6000 queries per second. Settings are: max_connections = 1000 and usually when i do show status like '%onn%'; ...
2
votes
2answers
928 views

InnoDB no longer available

Recently changed servers and went from CentOS 5.8 to CentOS 6.4. I installed MySQL 5.1.69 on my new server, and manually swapped /var/lib/mysql. I am not sure what version of MySQL was on my old ...
0
votes
1answer
569 views

Cannot stop MariaDB on Ubuntu Lucid

On starting MariaDB 5.5 I am getting the following error: InnoDB: Unable to lock /var/lib/mysql/ibdata1 Initially ran into this error after a server reboot was done running MariaDB 5.1. Have ...
1
vote
1answer
712 views

MySQL MyISAM multi-core optimization

I've noticed that our app does not respond to requests when a demanding request is in execution. Running top seems to identify the source of the problem: top - 13:54:25 up 1 day, 13:43, 2 users, ...
0
votes
2answers
288 views

Disable innodb_file_per_table

I have 24GB RAM i7 dedicated server, running CPanel 11.36 with MySQL 5.1.68. In my.cnf I have innodb_file_per_table enabled. Is it safe to just disable the option, not doing any damage to databases in ...
2
votes
2answers
300 views

Innodb one file per tablespace

I'm walking into an environment that uses MySQL and Innodb as the storage engine. I'd like to enable innodb_file_per_table to increase performance. Considering all of the databases/tables are ...
1
vote
1answer
483 views

Mysql crashing due to InnoDB not able to allocate memory

MySQL crashes every now and then due to, I believe, a large request hitting it above the innodb-buffer-pool-size set in my.cnf (currently at 210M), which I needed to set for mysql to start in the ...