InnoDB is the MySQL ACID-compliant storage engine.
3
votes
1answer
74 views
Find least recently used mysql table index
I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables.
While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
5
votes
1answer
3k views
intermittent MySQL crashes with error “Fatal error: cannot allocate memory for the buffer pool”
I'm a beginner system administrator for a small virtual server. The main function of the server is to run the open-source Moodle course management system software, written in PHP. It relies on a ...
7
votes
1answer
2k views
MySQL InnoDB crash post-mortem
MySQL crashed on me this morning.
With the exception of the standard MySQL included databases everything I use is InnoDB.
I attempted to restart the MySQL daemon but it failed twice.
I then ...
2
votes
3answers
2k views
ERROR Cannot find or open table?
MySQL version: 5.5.24
Due to the following problem:
mysql> desc reportingdb.v3_zone_date_cpm7k;
ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist
...
3
votes
2answers
5k views
How do I shrink the innodb file ibdata1 without dumping all databases?
InnoDB stores all tables in one big file ibdata1.
After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and ...
5
votes
2answers
383 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
3
votes
1answer
1k views
Is Percona xtrabackup the right choice for my 500GB MySQL backup?
I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
1
vote
1answer
431 views
MySQL / InnoDB: Does daily optimization improve performance?
Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
2
votes
1answer
522 views
MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases
I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all ...
1
vote
3answers
155 views
Mysql error while creating FK
here is structure of both tables
category table
CREATE TABLE category (
`catID` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT ...
2
votes
1answer
400 views
InnoDB “corruption”
I use:
5.1.63-0ubuntu0.11.10.1
Apache/2.2.20 (Ubuntu)
MySQL client version: 5.1.63
The system is continuously displaying the following error during reading data from a MySQL database:
...
4
votes
1answer
77 views
How badly does innodb fragment in the face of somewhat out-of-order insertions?
Let's say there's a composite primary key on (field1, field2)
Also, let be X a theoretical set of n numbers
Inserts look like this:
INSERT INTO table(field1, field2)
VALUES([a number picked at ...
1
vote
2answers
168 views
What MySQL storage engine should I choose?
I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.
What engine should I choose for my database, InnoDB or MyISAM?
There will only be about 1,000 new records ...
1
vote
1answer
376 views
ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)
I get an error when I try to create a table:
ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)
CREATE TABLE IF NOT EXISTS `cache`
(
`cid` varchar(255) NOT NULL ...
10
votes
3answers
521 views
Where is the INNODB Performance Leak?
I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me.
The Situation
I am working on a system which handles a lot ...
5
votes
2answers
247 views
MySQL : perform a big data migration between tables
I want to migrate data between 2 InnoDB tables.
Currently I'm running this query:
INSERT INTO table_a SELECT * FROM table_b;
If the dataset grows, what's the best way to avoid CPU overload?
...
2
votes
2answers
974 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 ...
2
votes
2answers
3k views
innodb_flush_method=O_DIRECT vs O_DSYNC performance impact on ext3 with LVM disk partition
In one of my production environment, we have two instances running on a RedHat cluster, with one production instance associated with the cluster.
We have 125G main memory with 24G InnoDB buffer pool ...
2
votes
2answers
659 views
MySQL 5.1.63 - using tuning-primer - Table Cache question
tuning_primer says the following:
TABLE CACHE
Current table_open_cache = 812 tables
Current table_definition_cache = 256 tables
You have a total of 498 tables
You have 812 open tables.
Current ...
1
vote
1answer
341 views
How to change the display of an empty string in mysql command line tool?
I'm maintaining a database with InnoDB tables.
These tables have some columns of type (from show create table):
`val0` varchar(30) default NULL,
`val1` varchar(30) default NULL,
etc...
From the ...
1
vote
1answer
78 views
Select ``,* from table?
I'm trying to upgrade our database servers to MySQL 5.5. In doing so, I'd like to convert all of our tables to InnoDB to take advantage of several performance benefits.
However, we use VARCHAR(50) ...
4
votes
2answers
346 views
Could running ANALYZE on all databases on large InnoDB tables show some performance increase?
So we have an old database that was fragmented until we ran OPTIMIZE on all tables.
The database/tables are about 100GB in size. Tables are InnoDB with heavy read/write activity.
There are so many ...
2
votes
1answer
1k views
innodb_force_recovery when InnoDB corruption
When I start mysqld (in /etc/init.d), it failed with
InnoDB: corruption in the InnoDB tablespace.
What's the best innodb_force_recovery value to force mysqld to start? I have tried 4 and 6, but ...
1
vote
2answers
215 views
Simple queries taking ages to run after changing from MyISAM to InnoDB
I have a quite large (300GB) table on MyISAM, which I've "converted" to InnoDB, and performance for a specific query has dropped through the floor.
When I say "converted", I can't afford to take the ...
1
vote
1answer
144 views
Removing/overwriting sensitive data located in already deleted records in ibdata files
As far as I know, using InnoDB tables with innodb_file_per_table = off does not prevent deleted data from being visible in the ibdata1 file.
I know this is by-design, and that I could switch the ...
3
votes
2answers
149 views
MySQL InnoDB Index in swap
I can't find any info regarding my question. This is more theoretical question.
For example, i have table keywords.
CREATE TABLE IF NOT EXISTS `keywords` (
`kid` int(11) unsigned NOT NULL ...
2
votes
1answer
3k views
How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed
I just switched my Wordpress databases to use the InnoDB engine.
When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work.
I manually ran OPTIMIZE TABLE ...
3
votes
1answer
336 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
2
votes
1answer
186 views
MySQL table locks solution -> InnoDb / Partitions
I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).
It's mainly caused due many heavy inserts. My current thoughts to optimize:
Changing to ...
0
votes
1answer
372 views
Unable to select the records from specific partition in mysql?
I've created a table using the following query,
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
fname VARCHAR(25) NOT NULL,
lname VARCHAR(25) NOT NULL,
store_id ...
0
votes
1answer
539 views
MySQL Innodb: What is the difference between running 'mysqlcheck --optimize --all-database' compared to running 'optimize table' in the mysql client?
Without having to script an 'optimize table' query to do all the databases/tables, we would like to run mysqlcheck --optimize --all-database instead.
What are the drawbacks of doing this through ...
0
votes
1answer
138 views
Data storage in innodb
I am new to mysql administration and I came to know about the data storage in myisam is through .frm, .myd and .myi. I could not find the data storage mechanism in InnoDB rather than its storage in ...
2
votes
0answers
2k views
Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded
Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
1
vote
1answer
120 views
Optimizing table and choosing storage engine
Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
...
2
votes
1answer
343 views
Fastest way to copy data from MyISAM to InnoDB
I want to copy all data from a MyISAM table with 16 millions rows to a InnoDB table, that will have a different (optimized) schema. See tables below to see the changes.
However, every approach I try ...
6
votes
1answer
1k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
3
votes
3answers
1k views
Mysql backup strategies?
We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
1
vote
1answer
582 views
Optimizing MySQL for Read-Only?
We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key ...
5
votes
1answer
342 views
Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?
I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
1
vote
2answers
431 views
Similar function NOWAIT in MySQL
Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
2
votes
1answer
714 views
error 1118: row size too large. utf8 innodb
I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question.
Using MySQL 5.6 - windows ...
2
votes
1answer
273 views
How do you extend the tablespace when using innodb_file_per_table?
With innodb_file_per_table off, you can create multiple tablespaces on multiple devices if necessary to manage growth, balance I/O, etc.
With the option on, how do you control growth of the files? Do ...
0
votes
1answer
237 views
InnoDB - Use combined index with primary key on GROUP BY
As far as I know, an InnoDB index always contains the primary index.
I cannot get the following to work:
SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid
WHERE t1.b = 99
GROUP ...
2
votes
1answer
624 views
Enabling InnoDB on an existing MySQL production server
We have a MySQL 5.1.59 server that has 30-40 existing MyISAM databases. I need to deploy an application that requires InnoDB databases.
Can I just enable/configure InnoDB in /etc/my.cnf and restart ...
3
votes
1answer
827 views
Slow insert performance in mysql innodb table for solr indexing
I have a Slave server (MySQL 5.5) having innodb tables. This server is being used for SOLR indexing. Few procedures used to fill 12 million data into 9 tables. The insertion speed is too slow.
...
3
votes
2answers
51 views
Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?
I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have ...
3
votes
1answer
1k views
PostgreSQL 9.1 vs MySQL 5.6 with InnoDB ? Which is better for scaling?
PostgreSQL 9.1 vs MySQL 5.6 with InnoDB. I do know that PostgreSQL offers better SQL features.
I am currently using MySQL 5.6 lab release (the one with InnoDb full text search feature)(not in ...
1
vote
1answer
157 views
Mysql: Tell InnoDB to keep a specific table or database out of pool/cache?
I have some tables that are used primarily to keep historical data, or that are only used once a day.
I do not want any operations on these tables to disrupt the more important tables present in ...
1
vote
2answers
344 views
Warnings: Why are statements with limit clause unsafe even if there isn't a limit?
I'm a bit irritated because of some warnings in mysql errorlog.
Example 1:
[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.
The statement ...
1
vote
2answers
339 views
Deleting Entries from Table not reducing my DB file
Iam usind INNODB Engine with innodb_table_per_file , so every table has it's own .ibd file instead of ibdata1 file.
When i Entered 512 Record in my users table its size get increased to 200kb from 96 ...
