Hot answers tagged innodb
5
The short answer is that you can't eliminate writes to disk, because InnoDB is doing its very best to make sure that it can recover if the server crashes.
I'd strongly recommend against migrating to MyISAM - a MyISAM table that's heavily written to is extremely likely to be corrupted if your server crashes, and you'll likely lose data (or need to restore ...
4
It seems to be in the file list for mysql-client-5.5.
sudo apt-get install mysql-client-5.5 should be all you need to do.
Note that the mysql client program is in a separate package, mysql-client-core-5.5, so it's possible to have mysql installed without any of the other useful tools like mysqladmin or mysqldump.
4
That really depends on what the directory structure underneath /home/mysql/mydb - if that's just the data files for one database, you'll need to extend your reach.
If you're going to use rsync (or take file-based backups in general), you'll need to make sure that your backup includes, at the very least:
Your innodb logfiles (ib_logfile*)
Your main innodb ...
3
Conditions with OR are harder for the optimizer than conditions with AND only.
Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.
Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:
WHERE ( languageId = 3 AND
...
3
Unfortunately, there are no metadata available for that info. The two fields you are asking for
DB_TRX_ID
DB_ROW_ID
are only visible from a textual viewpoint in SHOW ENGINE INNODB STATUS\G
You can learn more about it from the MySQL 5.1 Documentation
MySQL 5.5+ offers some metadata for live transactions
mysql> use information_schema
Database changed
...
3
it takes 90% of the time of the normal mysql request.
If it takes less time, then it is faster?
TokuDB is all about efficient use of SSDs, particularly for write performance and longevity - if most of your data fits in memory, the MyISAM and InnoDB will be much faster at fetching data. And it will be no faster for single thread benchmarks. You appear ...
3
I work for Tokutek. The answers here are mostly good. As Justin mentions, you need the right index, and your schema probably does not have the right index. I am happy to hear TokuDB was a little faster than InnoDB, but for table scans, assuming the table is not aged, it can go either way.
Here is a talk I gave on indexing that you may find helpful: ...
3
How are you coming to the conclusion that "these files contain some data even after graceful shutdown"?
Even if it's been fully flushed and uncommitted transactions cleared, etc., by using innodb_fast_shutdown = 0, the "flushing" probably means that the file is "empty" as far as MySQL is concerned, but it won't physically be full of zeroes.
If you start ...
3
The steps you outlined are correct.
I have written about this before
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?
May 30, 2012 : mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size
Doing innodb_fast_shutdown = 1 is incomplete because ibdata1 still have other transactional tidbits that ...
2
The only way to see the temp table needed would be to micromanage the process.
Here is how:
USE mydb
CREATE TABLE anonymized_table_name_new LIKE anonymized_table_name;
ALTER TABLE anonymized_table_name_new ADD COLUMN `anonymized_column_name` VARCHAR(100) NULL;
INSERT INTO anonymized_table_name_new (list of all columns except anonymized_column_name)
SELECT ...
2
There is a small problem in your aproach, if I understood correctly, you changed the log file size, and restarted the MySQL with innodb_fast_shutdown = 0. The problem here is that you still have some transaction on log files that aren't in the ibdata. What you need to do is tell MySQL to write all changes from log files to ibdata before move the log files, ...
2
Transaction isolation levels and database locking can make it very difficult to handle the problem you've described.
Hopefully ypercube's suggestion does the trick.
If it's valid to have multiple (campaign_id, user_id) combinations, just not within seconds of each other, you might want to consider adding a column with the user's web/application session ...
2
You don't have active indexed. The only reason tokudb would be faster than InnoDB or MyISAM for this query would be if the table had exceptional compression which would reduce the total IO, because you are examining the whole table.
If a small fraction of the rows in the table (less then 30% give-or-take) have the value of active=1, then adding an index ...
2
OPTIMIZE TABLE basically does three(3) things
Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics
Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp ...
2
The short answer: yes, it's fine.
The not much longer answer: I'm assuming that you, like 99% or users, have everything under the "datadir" directory, and have no special setup for innodb tablespace files, innodb log files etc, location-wise.
In fact, rsyncing (or just plain old copying across NFS etc) is presumably the safest way to backup one's database. ...
2
Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them
You can have SELECTs get deadlocked by UPDATEs and DELETEs
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks ...
2
Two hints for you:
The KEY uid is redundant, because it is covered by the PRIMARY KEY
40,000 rows at a time might make for too large a transaction. Although these are very small rows (two INTs) this may cause the transaction to go to disk, depending on your settings. I usually go with around 1,000 rows at a time (I go as low as 100 and as high as 10,000).
...
1
memlock was the issue.
turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql.
changed memlock settings in '/etc/security/limits.conf', and limit to 8GB.
then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log.
a few ...
1
Please look carefully at the queries that are locking
The SHOW ENGINE INNODB STATUS\G is incomplete
I cannot fully tell you anything more because you gave me three transactions that
execute the exact same query
lock the same page in the GEN_CLUST_INDEX
as shown from your display
---TRANSACTION 0 2491310, ACTIVE 29 sec, process no 6622, OS thread id ...
1
In all fairness, TokuDB has strengths and weaknesses against InnoDB.
InnoDB has better transactional throughput than TokuDB until you hit a bottleneck that puts both storage engines the same level playing field: Data Compression.
TokuDB is always compressing data and saves space by a factor of 3 over InnoDB. What verified this was a Percona benchmark ...
1
ANSWER #1
This feels clumsy doing this in Windows but here it goes. If you are able to login to mysql, then run this script
set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set SQLSTMT=SELECT CONCAT('REPAIR TABLE ',table_schema,'.',table_name,';')
set SQLSTMT=%SQLSTMT% FROM information_schema.tables WHERE engine='InnoDB'
set MYSQL_CONN=-u%MYSQL_USER% ...
1
OBSERVATION #1
You have innodb_thread_concurrency set to 8. In MySQL 5.5, it is 0 by default. Setting innodb_thread_concurrency to 0 allows InnoDB to decide the best number of concurrency tickets to handle at the same time. I discussed way back on May 26, 2011 : About single threaded versus multithreaded databases performance
OBSERVATION #2
You have ...
1
A bug filed here (http://bugs.mysql.com/bug.php?id=68287) looks like it is potentially relevant - check what table_definition_cache is set to by running:
SHOW GLOBAL VARIABLES LIKE '%table_definition_cache%';
1
I have five(5) aspects to discuss here
ASPECT #1 : innodb_buffer_pool_size
You need to run this query
SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) BPSIZE
FROM information_schema.tables WHERE engine='InnoDB';
This will give you the ideal sized buffer pool because InnoDB caches data and index pages.
If the DB Server has 32G RAM, the ...
1
OPTION 1:
To find the size of this table you can use the follow query:
SELECT DATA_LENGTH+INDEX_LENGTH AStotalTable, TABLE_ROWS from information_schema.TABLES WHERE TABLE_SCHEMA = 'DB_NAME' AND TABLE_NAME = 'TABLE_NAME';
Now you have to calculate the difference from the actual size totalTable + what you will add, see the list on [MySQL Documentation] ...
1
At present, only MyISAM permits auto_increment values in a composite key. I wrote about this over a year ago : How can you have two auto-incremental columns in one table?
According to the Book
Section 5.7 Page 89 Paragraph 1 states the following:
The MyISAM Storage Engine supports composite indexes that include an AUTO_INCREMENT column. This allows ...
Only top voted, non community-wiki answers of a minimum length are eligible
