InnoDB is the MySQL ACID-compliant storage engine.
2
votes
1answer
163 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 ...
2
votes
1answer
847 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 ...
2
votes
1answer
263 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: ...
1
vote
1answer
34 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
165 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
87 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 ...
1
vote
1answer
82 views
Mysql DB server hits 400% CPU
I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top.
- load average 40 to 50
- CPU % - 400%
- idle % - 45%
- wait % - ...
1
vote
1answer
63 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
vote
1answer
168 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 ...
1
vote
1answer
235 views
InnoDB - How to get top locked tables and rows which are locked
I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?
0
votes
1answer
20 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 ...
0
votes
1answer
63 views
MySQL: Error reading communication packets
I get this warning in mysql,
[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)
I have been through few topics in google ...
0
votes
1answer
21 views
How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
0
votes
1answer
56 views
Cannot Utilize Maximum CPU and Memory Usage for MySQL
Good day.
I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.
I have a single stored procedure ...
0
votes
1answer
81 views
MySQL database drop insanely slow
I just installed MySQL 5.0.45-log Source distribution on my Debian 6.0.6 server.
I installed it under my user home directory like I'm used to doing.
But this time the queries are extremely slow to ...
0
votes
1answer
116 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
173 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 ...
3
votes
0answers
102 views
“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28
We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28.
During high load, randomly we got the below error.
MySQLcheck, innochecksum shows no ...
3
votes
0answers
98 views
MySQL 1264 warning from libmysql
I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader).
I managed to connect and things are going almost fine. I use mysql_real_connect and ...
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
0answers
23 views
Storing history of full/partial tables
I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables.
I wonder if it's efficient to store a lot of rows ...
1
vote
0answers
408 views
Tables don't exist after MySQL restart
I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior:
I have some tables with data in them
I made ...
1
vote
0answers
735 views
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?
Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ?
updated to answer the 3 questions:
mysql> show variables like 'version%';
...
0
votes
0answers
9 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
0answers
38 views
How much RAM for a large database on a dedicated MySQL InnoDB server?
Hereunder my hardware config:
CPU: 1x Intel Xeon X3470 Quad core @ 2.93GHz
RAM: 4x 8GB DDR3
Disks: 2 x SATA 320GB for the system & 4 x SAS 300GB for the DB
I've upgraded from 12Gb to 32Gb and ...
0
votes
0answers
5 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
Question: Could the error shown on the screenshot be the reason why my site is very slow?
0
votes
0answers
41 views
is it good to use combined primary key for MySQL InnoDB clustered index?
I'm trying to build a aggregated reader website of multiple forums.
Because most queries are likely to be within same time periods for written_time column,
I'm thinking about taking advantage of ...
0
votes
0answers
47 views
InnoDB errors after having deleted “ibdata1”
By accident, I have deleted the ibdata1 file in the MySQL directory because MySQL wasn't functioning correctly.
After reading so many forums, I found a post that recommended deleting the 3 files ...
0
votes
0answers
31 views
MM Replication and Zabbix in slave, Error code 1032
I have multi-master replication and using Zabbix for server monitoring. Since the replication is row based, I get this error in slave everytime which also breaks my replication. The error I get is:
...
0
votes
0answers
64 views
InnoDB tuning with 1G of ram limit
I am trying to calculate variable moving averages crossover with variable dates.
That is: I want to prompt the user for 3 values and 1 option. The input is through a web front end so I can build/edit ...
0
votes
0answers
25 views
Defacto way of fixing constraint issues without downtime?
I come across two constraint issues all the time (UNIQUE, and referential integrity constraints) within InnoDB. My question is when these issues arise reproducibly what is typically the solution? I'm ...
0
votes
0answers
20 views
MySQL (v5.6.11) InnoDB 5.6.11 restoring indexes during table copy
When i see the profile for my optimize table query here is what i see for the InnoDB storage engine 5.6.11:
+----------------------+------------+
| Status | Duration |
...
0
votes
0answers
26 views
Slave lags ONLY when long running sql runs
I have mysql Master/Slave setup.
Master in 5.0.77 and Slave in 5.5.28.
The Master serves 24/7 with 82.06 inserts/s, 94.39 updates/s, 0.00 deletes/s, 22613.29 reads/s.
They both are in sync every ...
0
votes
0answers
79 views
UPDATE / SELECT incredibly slow in MySQL InnoDB
I have 1 table with 2.5 billion records(2.5 Cr.) and I am trying to get count of records based on indexed column. it takes 115 seconds to execute same.
Query : Select count(1) from table1 where ...
0
votes
0answers
34 views
InnoDB best index practises
I have an InnoDB table with around ~3.7m rows in it. Here are my current indexes:
...
0
votes
0answers
84 views
How long would a delete take for 50 million records in MySQL/InnoDB?
How long would a delete take for 50 million records in MySQL with the InnoDB engine?
Server Config:
MySQL Version 5.0.77
CentOS 5.4 Final
64 bit 16GB RAM
RAID 5
Processor Model : Intel(R) Xeon(R) ...
0
votes
0answers
90 views
Need help! Interpreting INNODB MONITOR OUTPUT for Prestashop performance problems
We use a INNODB MYSQL Engine driven Database for hosting a Prestashop Version 1.5.3.1 on debian with apache2 webserver. We are facing performance problems, maybe some missconfigurations. There is a ...
0
votes
0answers
446 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 ...
0
votes
0answers
45 views
400GB Innodb table with deletes and no data_free?
I have an Innodb table which is over 400GB and performing quite a lot of deletes on it, so I thought I should try to optimize the free space kept it is probably creating. I ran this query to find out ...
0
votes
0answers
42 views
What would this non-'unique index with a unique search condition` query lock?
I am not sure about this part of the transaction manual
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
0
votes
0answers
41 views
row locking or avoiding duplicate insertion on primarykey/otherfield combo
i have a table that stores additional data pertaining to files/images saved in files elsewhere, each entry can be associated with another type in the system,
for users there may be one or more ...
0
votes
0answers
84 views
table is full in innodb
One of the table t_keyword_stat_ga in the my DB on server(currently the master) was set as INT for a column id. This column is also set as the auto_increment. As noticed today the INT column reached ...
0
votes
0answers
57 views
How to efficiently model this simple data structure?
catId(INT) | userId(INT) | catName(VARCHAR)
0 1 Cat1
1 1 Cat2
2 2 U2Cat1
3 2 ...
0
votes
0answers
219 views
InnoDB row level locks without SELECTs?
I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation:
Each client runs the following ...
0
votes
0answers
29 views
“Disk leak” with xtrabackup intrementals/innodb
I have several Percona Mysql databases being backed up using Xtraback. Aside from the mysqldb these are all innodb databases. These are setup on a weekly full backup schedule with multiple daily ...
0
votes
0answers
87 views
innodb deadlock on different tables
I am using mysql 5.5.28.
I would like to ask a question about the nature of deadlocks in innodb engine.
I monitor deadlock with pt-deadlock-logger.
Recently I found deadlock of two transactions to ...
0
votes
0answers
67 views
Should I use compression in my InnoDB tables for this project?
I'm building a web portal that has a lot of tables and data is mostly id's (integers) in range 0 -> max, and strings of chars (VARCHARs) and fixed char(1) like 'y' and 'n' s mostly.
I switched (still ...
0
votes
0answers
161 views
Error connecting to DB
In regards to:
http://serverfault.com/questions/442218/remounting-mysql-under-a-new-mount-point
Everything is running. MySQL started but for websites they can not connect to the mysql database at ...
0
votes
0answers
550 views
InnoDB: Error: unable to create temporary file; errno: 2
I am attempting to run a fresh MySQL install on my Windows machine, however I get this in the error log upon running mysqld:
120702 16:56:28 [Note] Plugin 'FEDERATED' is disabled.
120702 16:56:28 ...
0
votes
0answers
98 views
slow response from MySql server
We just moved our database from an old server to a new one with more processing power and 4G RAM. However, for some unknown reason, the execution of our store procedures became even slower. An ...
