InnoDB is the MySQL ACID-compliant storage engine.
0
votes
2answers
29 views
MySQL using too much CPU
I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:
CentOS 5.9
24gb Ram
8 ...
2
votes
1answer
274 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: ...
0
votes
1answer
18 views
The InnoDB log sequence number is in the future
Our site was getting intermittent database errors and I asked my webhost to check what's happening. After some inspection they found that the database has few issues and tried repairing it. Finally I ...
1
vote
1answer
91 views
Why does that query cause lock wait timeouts?
From time to time, I find a lot of these errors in my PHP error log:
MYSQL.1213: Deadlock found when trying to get lock; try restarting transactionSQL
The problem persists for about 2 or 3 minutes. ...
-1
votes
0answers
30 views
How much time is considered 'Ideal' for a SELECT query? [closed]
This is my first experience in developing for the masses, and I wanna know how much time is considered ideal for running a select query and returning the data to the client?
I've seen simple queries ...
-1
votes
2answers
26 views
How to use GROUP BY after the Having clause Mysql
Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions
All the accounts which is not ...
0
votes
2answers
21 views
How to add column to big table in MySQL
I'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried:
Alter table in screen with table ...
0
votes
0answers
28 views
How to add column/alter structure to big table in MySQL [duplicate]
'm php developer so dont be strict. I have a big table ~5.5gb dump. Our PM decided to make new column in it to perform new feature. Table is InnoDB So what i tried:
Alter table in screen with table ...
0
votes
1answer
19 views
How big can innodb_doublewrite_file grow?
When I use the enable innodb_doublewrite_file ...
How big can this file grow ?
How can I calculate the optimal disk size, if it would be used only for this file?
23
votes
4answers
24k views
How to safely change MySQL innodb variable 'innodb_log_file_size'?
So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB:
...
0
votes
2answers
32 views
how important are mysql's innodb logs?
I'm basically concerned about the following two settings, these are my default settings :
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
I haven't changed them for 2 reasons : one, ...
0
votes
1answer
21 views
Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC
I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error
ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
0
votes
0answers
24 views
Federate engine to help overcome distributed transaction?
Currently we have been using pure innodb. The issue is that we have architecture where we have multiple outlet each have its own local db and a copy of it in the hq outlet. The issue is that we ...
2
votes
1answer
81 views
Does the size of the primary key contribute to table size?
I have a table (InnoDB) with data length around 36G and index length 23G.
It had a composite primary key across three columns.
As an initial attempt to reduce the size of this table, I dropped the ...
1
vote
1answer
44 views
Why InnoDB ALTER TABLE is so slow? [closed]
The machine has a 4-core hyper-threaded Intel i7 670 @ 2.8GHz, 16G RAM, 8G of which is InnoDB buffer pool. The database resides on Intel SSD disk. DBMS is MariaDB 5.5.30 x64 running under Windows ...
1
vote
2answers
106 views
SHOW TABLE STATUS very slow on InnoDB
Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ ...
0
votes
1answer
25 views
MySQL HDD activity with empty process list
What does this mean and how can I understand what is mysql doing? I use InnoDB, my database is huge. This happens when I disconnect my app.
Thanx.
0
votes
2answers
50 views
InnoDB Tablespace critical error in great need of a fix
Link to screenshot :
http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG
(The value reported is outside the allowed range - Byte free, free, gauge, warn, critic)
Question: ...
1
vote
1answer
20 views
MySQL import LOCK doesn't prevent PRIMARY key issues
I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks).
Some info;
my tables are almost all ...
1
vote
1answer
150 views
MySQL backup InnoDB
I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
0
votes
2answers
391 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
103 views
How to check growth of database in mysql?
I want to know is there any method to check the growth of database on file
EXAMPLES
Database A contains all tables in INNODB storage engine
Database B contains all tables in MyISAM storage engine
...
0
votes
1answer
32 views
InnoDB Failure of some kind
I have MySQL 5.5 installed. I tried to install Joolma but it failed. I went into their sql and replace EGNINE=InnoDB with MyISAM and the install worked.
InnoDB is listed under SHOW ENGINES;
Any idea ...
1
vote
2answers
90 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
0
votes
3answers
35 views
Convert the table PROCESSLIST from InnoDB to MYISAM
I use MySQL. My database's name is "db", and I have a table called "Test".
I want recover the last update, so I used this query:
SELECT update_time
FROM information_schema.tables
WHERE ...
0
votes
1answer
55 views
How can extract the table schema from just the .frm file?
I have extracted the data directory of mysql from a backup and need to get the schema from an old table, however it's a backup from a different machine.
I have read a fair number of tutorials today ...
2
votes
1answer
187 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 ...
0
votes
1answer
141 views
How I prevent deadlock occurrence in my application?
I am developing an LMS application in PHP framework(Codeigniter 2.1.0). I am using MySQL database.
All the tables in the database have innodb engine. I also created indexes on each tables.
Now I am ...
0
votes
1answer
32 views
InnoDB tables inaccessible after reboot
After reboot each time, I can't see my InnoDB table. However, when use command mode I can see my tables, but cannot access to it.
mysql> show tables;
+----------------------+
| ...
2
votes
1answer
60 views
Which hardware you suggest?
I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be ...
2
votes
2answers
2k views
MySql - changing innodb_file_per_table for a live db
I have a large MySql DB (150GB) and only now i've noticed that the innodb_file_per_table is set to off which cause the entire DB to be hosted on one single file (ibdata1). i want to active ...
1
vote
1answer
25 views
mysqldump freezing on a specific table
I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376).
My dump command:
mysqldump -uxx -pxx ...
1
vote
2answers
45 views
mysql innodb_flush_log_at_trx_commit clarification
I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years)
So ...
1
vote
2answers
436 views
Mysql innoDB write operations are extremely slow
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you ...
0
votes
1answer
239 views
What is the actual difference between innodb_autoinc_lock_mode 0,1,2
I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given.
I see in "0" option, it locks the entire table and does the ...
1
vote
1answer
295 views
increasing mysql table open cache?
I often read that it is best to increase this variable slowly. Can someone explain why? My status indicates that I should increase it... What is best practice / "slowly"?
Thanks!
1
vote
1answer
108 views
select count(*) in mysql 5.5 innodb— rewrite advice?
I need advice on how to rewrite a select count(*) query for innodb tables mysql 5.5. in new environment its very slow...
select count(*)
from mails3
join questions using (question_id)
where ...
0
votes
2answers
16 views
what happens when InnoDB logspace is too small
I've just seen several mysql errors in my log:
InnoDB: ERROR: the age of the last checkpoint is X
InnoDB: which exceeds the log group capacity Y
I understand, that I do have to increase the ...
0
votes
0answers
47 views
InnoDB data back in 3 months after OS shutdown
I've got an interesting issue last week that after shutting down an instance of mine, all data were back in 3 months (like a big rollback), even though there is no sign of crashing, recovery crash ...
0
votes
1answer
52 views
MySQL dynamically optimize innodb tables without “file per table” setting
We are getting a “too many connections” error once a week the same time a mysql procedure runs. The procedure runs “optimize table” on hundreds of tables and takes nearly ten hours to finish, taking ...
1
vote
1answer
32 views
Sorting Result state taking very long time
I am using a MySQL 5.1.69-log DB for analysis purpose.
I've designed my database in a star schema. All tables are using InnoDB.
My innodb_buffer_pool_size=2Gb
This is the query I am running :
...
0
votes
1answer
36 views
MySQL - Export two columns from large table without causing a lock
I am a sysadmin who doesn't do a ton of dba stuff. For a project, I have access to a production server--which I am not the sysadmin for--with an enormous 40,000,000 row >10 GB MySQL InnoDB table. I ...
0
votes
1answer
22 views
Is there an advantage to moving to 'real' date partitions on MySQL 5.5?
We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column):
ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
3
votes
2answers
982 views
Cannot write to mysql database while select query in process
Running Mysql 5.1.57 on InnoDB, and have a database locking issue.
I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
1
vote
2answers
156 views
MySQL information_schema doesn't update
I have a database, say abc, in mysql server. It has only one table named test. test uses innodb engine and I've set innodb_file_per_table to true.
After I run the query delete from abc.test, I want ...
1
vote
1answer
29 views
Updating an FTS indexed column returns “Invalid InnoDB FTS Doc ID”
Environment:
Ubuntu 12.04 (and 13.04)
MySQL 5.6.11
I have a table which has a full text index on it (real table has much more columns and rows):
DROP TABLE IF EXISTS articles;
CREATE TABLE ...
0
votes
2answers
797 views
1286 - Unknown storage engine 'InnoDB'
I am trying to use roundcube and it recently just broke. I don't know if this is due to a MySQL update that happened recently or not but in phpMyAdmin I get the following error if I try and view a ...
1
vote
2answers
57 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 ...
2
votes
5answers
373 views
TokuDB not much faster than MySQL
I have converted a MySQL database with 80.000.000 rows to TokuDB.
Now when I run:
select count(id) from xxx where active=1
it takes 90% of the time of the normal MySQL request.
What do I have to ...
1
vote
1answer
187 views
How to run a cold backup with Linux/tar without shutting down MySQL slave?
I run the following before tar-ing up the data directory:
STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
