InnoDB is the MySQL ACID-compliant storage engine.
1
vote
1answer
40 views
Changing TEXT to VARCHAR
In a MySQL database (InnoDB) we have a table containing roughly 60.000.000 rows. One of the columns is of type text.
Currently the longest entry has length 360 (found using SELECT MAX(LENGTH(value)) ...
2
votes
1answer
44 views
When and why can this kind of deadlock occur?
------------------------
LATEST DETECTED DEADLOCK
------------------------
130409 0:40:58
*** (1) TRANSACTION:
TRANSACTION 3D61D41F, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT ...
1
vote
1answer
149 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!
0
votes
1answer
43 views
If MySQL isn't caching, then what is?
I ran a few commands to see whether MySQL is caching queries/result sets. It looks like this is not the case.
mysql> show variables like 'query_cache_size';
--------------
show variables like ...
1
vote
1answer
84 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
2answers
84 views
MySQL performance impact of increasing innodb_buffer_pool_size
I have two databases one of size 170 MB and second one is of 15 MB, both having INNODB tables and innodb_buffer_pool_size is set to 8 MB.
My queries are responding well now i.e under mili seconds.
...
5
votes
1answer
90 views
“Site Offline” MySQL server failing to start and stop
I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the ...
1
vote
1answer
78 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
46 views
Is there a query to set a table's engine to innodb if it's not already?
I can set a (MyISAM) table's engine to InnoDB using the query:
alter table tablename engine=InnoDB
which takes some time to complete for large tables. It appears though that if I run the same query ...
0
votes
1answer
159 views
ibdata1 grows exponentially when innodb_file_per_table is configured
I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month).
Is My my.cnf file is ...
2
votes
1answer
114 views
Innodb Slow queries since convert from MyISAM
a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to ...
0
votes
0answers
73 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 ...
1
vote
1answer
83 views
For a InnoDB only DB, which of these elements can be removed?
So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming ...
2
votes
1answer
75 views
Drupal 7 Uses InnoDB, so do I need other configs?
If I'm optimizing a database that will be delivering multiple Drupal 7 websites, all using InnoDB, do I need to worry about [myisamchk] & [isamchk] sections, or can I just worry about the stuff ...
2
votes
2answers
57 views
Are there implicit indexes in InnoDB like MyISAM?
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:
(a)
(a, b)
(a, b, c, id)
Is this valid for ...
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:
...
1
vote
1answer
61 views
Linux 32b MySQL periodically crashing with signal 11 and OOM errors [closed]
I have a MySQL DB with mostly InnoDB tables that I'm fairly certain isn't tuned properly based on what's happening, but I don't have enough know how to pinpoint what to change. The server is a 8G 32b ...
0
votes
1answer
109 views
Is using the alpha version of Percona 5.6 safe? [closed]
I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it ...
0
votes
1answer
75 views
Large queries on MyISAM tables crashing MySQL
I have a PHP website, which is using the third-party software ResourceSpace, that when I installed it, it used my default storage engine to create the tables, which at the time was MyISAM. Now that ...
1
vote
1answer
55 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
31 views
INNODB real-time locks?
How can I view real-time locks in innodb when information_schema.innodb_locks is not available?
2
votes
1answer
63 views
Minimize locking on UPDATE with multiple WHERE columns
I have a problematic query that's causing a lock timeout:
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2
LIMIT 100
...
3
votes
1answer
194 views
MySQL: Can't find .ibd files after turning on innodb_file_per_table
Question:
This a question about MySQL. Let me give the specifics of my setup:
Mac OSX 10.6.8
MySQL 5.5.28
Workbench 5.2.44 for all administration
InnoDB engine exclusively
In order to manage ...
1
vote
1answer
110 views
What is causing the Periodic High Disk IO writes on MySQL Apache Server
I've already read all the posts related to this subject but couldn't find any answers. Here are my settings. Server has 4GB of RAM but top shows only 2.2GB are used. I've set the ...
0
votes
2answers
122 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
0answers
78 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
88 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 ...
2
votes
1answer
90 views
Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?
I learned that innodb_buffer_pool_size makes innodb behave like an in memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or ...
0
votes
2answers
72 views
Bypass MyISAM table lock
I have a big MyISAM table with a fulltext index for searching. Problem is that inserting data into that table results in a table lock and in the meantime no other sql query can perform any action ...
0
votes
0answers
391 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
1answer
114 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 ...
3
votes
2answers
556 views
Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?
My Hardware Spec
Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 ...
3
votes
4answers
245 views
Stress Test to Saturate Memory for MySQL InnoDB
I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
0
votes
2answers
96 views
How are DB locks tied to connections and sessions?
I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
0
votes
1answer
55 views
Integer comparison in MySQL. does it cause lots of overhead?
I have an 8 digit number in my db. I want to do a select on the table in MySql (Innodb) and get the list of all numbers that are equal or smaller than my number. Then I sort them from larger to ...
1
vote
2answers
111 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 ...
0
votes
3answers
48 views
Using MySQL InnoDB as an Archive
My site has a main MySQL InnoDB table that it does most of its work on. New rows get inserted at a rate of 1 million per week, and rows older than a week gets moved over to an archive table on a daily ...
2
votes
1answer
65 views
SQL Uniqueness Across Multiple Tables
I have two SQL tables: Users and clients.
Every row in the users table has a set of clients in the clients table, linked by the users table id field and the clients table user_id field.
The users ...
1
vote
1answer
101 views
Error when dropping InnoDB database
I am trying to drop a database in mysql, but every time I get this error
ERROR 2013 (HY000): Lost connection to MySQL server during query
even logged in as root.
Originally, I had a corrupted ...
3
votes
1answer
49 views
Process sessions foreach minute
I have a table that stores sessions. The columns are: ID, Start (Timestamp) and Stop (Timestamp, can be NULL).
It's for online radio. A listener connects to the stream, and a row is created. Once ...
1
vote
2answers
46 views
Separated databases, separeted datas
We have a global project. We will have some servers all continents. We separate the datas because it is not necessary that they be in one place together. BUT! We need unique IDs (MySQL, InnoDB).
I ...
0
votes
1answer
180 views
Unable to change engine to MyISAM in MySQL
I have installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.
I have checked following engines are supported in MySQL:
show engines\G
...
2
votes
1answer
154 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
94 views
Why is InnoDB auto_increment column skipping over large blocks of unused values?
We have an active and important InnoDB table with about 8M rows. We just noticed that for several months, the primary key value (a BIGINT) has been jumping by hundreds at a time.
I turned on query ...
2
votes
1answer
157 views
What causes InnoDB to write 100% more pages while slowing down answering queries?
I have a somewhat big database server: 4 processors, 32 cores, 288GB RAM, 2 ethernet cards bounded together, 2 independent RAID controller cards with 1GB cache each, 24 2.5" disks, being 8 SAS, ...
1
vote
3answers
732 views
Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?
I have some tables that many users need to access to:
mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
Table: v3_cam_date
Create Table: ...
0
votes
1answer
73 views
Maintenance in MYSQL when innodb_file_per_table disabled
I have read your post and I completely understand OPTIMIZE TABLE to perform in an environment where innodb_file_per_table is disabled, does not shrink the global ibdata1 tablespace.
But what if I ...
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 ...
1
vote
1answer
81 views
How does mysql deal with queries that touches myisam and innodb tables?
In our mysql database, we use both myisam and innodb tables, though there are more myisam tables.
QUESTIONS
If a query involves both myisam and innodb tables, will the query use table lock or row ...
0
votes
0answers
39 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 ...
