Tag Info

New answers tagged

0

(I'm adding an "answer", not a comment for formatting reasons.) @akuzminsky covered most things quite thoroughly. Why a BIGINT? Won't an INT UNSIGNED (max value 4 billion) suffice? That would saver 4GB. The fill factor comes in two flavors -- If the writes are cleanly done, then the blocks are 15/16 full. Grand total might be 40-45GB If there is ...


4

Each row in InnoDB (let assume COMPACT format) has these headers: Offsets - one or two byte per variable length type field. NULL flags - one bit per NULL-able field, aligned to a whole number of bytes. So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed. Then comes primary key ...


0

STEP #1 Add the option to my.cnf [mysqld] innodb_file_per_table = 1 STEP #2 If you are running MySQL 5.6, login to mysql and run mysql> SET GLOBAL innodb_file_per_table = 1; If you are not running MySQL 5.6, restart mysqld # service mysql restart STEP #3 Write a script to create NULL ALTER TABLE commands MYSQL_USER=root ...


0

I you can't or don't want to make "native" binary backups than mydumper is a good alternative. dumping a loading is much faster than regular mysqldump backups.


3

The best way to migrate tables between servers is to do it in binary "native" format. Doing a serial logical dump (as mysqldump does) not only may take days on a very large database, but it will take even more for recovery. If you need to maintain availability on the source server for InnoDB tables, the best way is using an utility like MySQL Enterprise ...


2

You may have moved around manually the tablespaces or improperly importing the tables physically. The error assertion is a detection by MySQL InnoDB that pages have internal values that do not correspond to their physical location, thus forcing the kill of the server to avoid further corruption. I have very very rarely seen that as a problem due to InnoDB ...


0

The simple answer is "you don't have to do anything". The "right" answer is that you should switch all the table to InnoDB and change a couple of my.cnf settings. The other answerers have given you multiple reasons. If you choose to make the switch, here is a blog that explains what you should take care of during the conversion: ...


1

Software that uses MyISAM tables is often a sign that you are dealing with amateur quality software. There have been very few valid reasons to use MyISAM for several years, now... ACID and intelligent crash recovery and foreign key constraints are just a few things that come to mind that MyISAM will never dream of being able to provide. Turning off InnoDB ...


0

Your question seems to imply that the innodb engine is reading myisam tables. This is not the case the innodb engine only reads innodb tables. The myisam engine only reads myisam tables. Which version of mysql determines which engine is used for the mysql schema - the tables holding authentication and schema metadata switched from myisam to innodb around ...


-1

"Using innodb engine" means that INNODB would be the default engine for creating tables, i.e. if you CREATE TABLE a ... without specifying an engine for the table, it will be created according to the default engine. If your tables are already created as MYISAM tables, it's meaningless to "use innodb". You are actually using MYISAM. In other words - the DB ...


1

SHORT ANSWER Only as a last resort LONG ANSWER Having multiple indexes can be a rather arduous adventure for MySQL Query Optimizer. I have written about this before Sep 18, 2012 : How are multiple indexes used in a query by MySQL? Apr 19, 2014 : Optimizing indexes (Under the Heading ANSWER TO QUESTION #2) In essence, MySQL will do lookups along ...


1

If you are testing a and b, INDEX(a, b) is likely to be better. Indexing a flag (by itself) is almost never useful. Please provide SHOW CREATE TABLE and a few WHERE clauses; I will give specific advice. Here's a quick cookbook for building an INDEX that will often be optimal. Given a WHERE with a bunch of expressions connected by AND: List all the ...


0

When a query plan is constructed, MySQL finally decides to use only one of the indexes on the table which means that multiple single column indexes will never be used in a single query. However, if you have multi-column single index which covers all the required fields (coverage index), the database will never have to read from the disc which will make the ...


0

There is no clear answer to your question, as it really depends on the query. BUT: If you like to filter e.g. for two colums a combined index will have a better effect.


1

You have three potential areas you could address: LIKE operators, subqueries, and OR operators. Regarding LIKE operators: See http://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx If your filter criteria uses equals = and the field is indexed, then most likely ...


0

The short answer: Queries will not be noticeably faster. The long answer... If you are doing table scans of 600GB, they needs fixed. And, yes, this type of query will be faster. But nothing else will be faster... Deleting a bunch or rows will not help performance at all unless you were touching those to-be-deleted rows. If you were not touching them, ...


0

Default InnoDB page size is 16K. Pages go into a larger 1MB data structure called an extent. MySQL allocates 4 extents at a time. All of these are stored in one large file, so boundary issues have minimal effect on InnoDB. You can read more about MySQL file space management. Modern hard drives have read caches, and they read ahead, so contiguous sectors ...


0

The NTFS cluster size governs the granularity of allocation of disk space to files. InnoDB (and other application software) basically don't care what the file system's cluster size is. When InnoDB reads or writes an 8K block, it just reads 8K. When it allocates an 8K block, it allocates two 4K clusters. That being said, if you're standing up a new file ...


1

If you are worried about table locks caused by DDL operations, like ALTER TABLE... ENGINE=InnoDB (what optimize does for InnodB) you must know that in 5.6, that process can be done fully online, and alternatively, and for lower versions, you can use an online alter table tool like pt-online-schema-change. With proper care, the process can be done fully ...


1

I have MySQL 5.6.21 on my laptop running Windows 8.1 and it is doing nothing. It still has that state. Please keep in mind that this state exists in the performance schema, in the table threads, in the column PROCESSLIST_INFO. Please note the MySQL Documentation on PERFORMANCE_SCHEMA.THREADS.PROCESSLIST_INFO The statement the thread is executing, or ...


0

ANALYTICAL EXPERIMENT Looking at CREATE TABLE and seeing that you are creating 300 partitions, I need you to perform an experiment. Please run the following script: DROP DATABASE IF EXISTS rolandos_test; CREATE DATABASE rolandos_test; USE rolandos_test SET @T1 = UNIX_TIMESTAMP(NOW()); CREATE TABLE CTM_MyISAM ( `ServerID` BIGINT NOT NULL, `ClientID` ...


1

Is it common to spread data of a single user operation across multiple rows? Think of the "entities": User, Review, Rating. A "user" may go to the restaurant many times, and give a "review" each time. The "review" may give ratings for multiple things, such as 'food' and 'location'. These are "Many-to-one" relationships, so Ratings has a review_id ...


0

Some bulk data loading tips from mysql document is possible useful. https://dev.mysql.com/doc/refman/5.6/en/optimizing-innodb-bulk-data-loading.html You can increase insert speed by some ways: - turn off autocommit - turn off unique check - turn off foreign check Hope this help !


0

Those settings are too high for 1GB of RAM! You may be swapping all the time, hence high I/O. Get more RAM, or change the settings down -- far down. Moving a table into MEMORY -- bad idea if you need persistence. Big value for tmp_table_size because of lots of on-disk tmp tables -- It would be better to figure out why the queries are so inefficient. ...


0

Is WP using MyISAM? Or InnoDB? If MyISAM, then set innodb_buffer_pool_size=0. If InnoDB, decrease innodb_buffer_pool_size to, say, 300M and key_buffer = 10M. table_open_cache and table_cache (they are the same thing) could be lowered to 300. Turn off the query_cache -- query_cache_type = 0 and query_cache_size = 0. innodb_io_capacity = 200 ...


0

More indexes --> slower INSERTs. (However, this may not be a critical issue.) More indexes --> slower UPDATEs when you modify an indexed column. Indexing a flag (or other low-cardinality field) --> almost never will the optimizer use that index, so it is a wasted index. (I'm guessing is_active is such.) However, a compound index that includes that flag ...


0

With regards to writes, you might want to change innodb_flush_log_at_trx_commit to either 0 or 2, see http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit There are other variables affecting performance, most notably innodb_buffer_pool_size that might be too low in your case thus IO reads might be more common ...


0

The number of pools does not change the total amount of pages innodb would use. A page exists on one of the pools, not a copy of it in each pool. That's why your 6GB database doesn't take much more than 6GB, the extra probably being for indexes. From the documentation: ...


0

You need to disable foreign key checks and unique checks #!/bin/bash DATABASES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select distinct(table_schema) from information_schema.tables where table_schema not in ("mysql","information_schema","performance_schema")') for D in $DATABASES do TABLES=$(mysql -u XXXXX -pXXXXX --skip-column-names -e 'select ...


1

Maybe you could do something like this: Add a new field called deleted. Do an update like UPDATE tablename SET deleted=1 WHERE `columnname` LIKE '-a%'. Set cron to delete this at night time.


0

A quick look shows 2 main problems: Many queries on the stage Waiting for table level lock indicates a concurrency problem due to the usage of a table-level-only lock engine, such as MyISAM. Moving, as you did, to InnoDB will mostly solve the problem, specially for the queries you are doing (counters): UPDATE topics SET views=views+2 WHERE tid=64896 ...


1

You have a memory problem, something very typical in a memory-bound system, and that can be confirmed on the line: 150207 17:31:42 InnoDB: Fatal error: cannot allocate memory for the buffer pool As InnoDB cannot allocate memory for its buffer pool, it fails, and MySQL cannot start if the InnoDB engine fails (MySQL 5.5). Nothing on your my.cnf seems out ...


0

For addtion, when you delete some rows in table. Table will be fragmented. Remove the fragment will make your table is shinked. Optimize table like Ike Walker said will help. Optimization table makes downtime for mysqld.


0

Some people tell me about mydumper. I have not used it before but it is very hopeful. About mydumper: http://imagexmedia.com/blog/2014/11/speeding-your-mysql-dumprestores-mydumper http://www.percona.com/blog/2014/03/10/new-mydumper-0-6-1-release-offers-several-performance-and-usability-features/


2

My first instinct would be to do multiple, smaller deletes by limiting the number of query results, and running the query multiple times: DELETE FROM `tablename` WHERE `columnname` LIKE '-%' LIMIT 1000000


3

The easiest solution is to simply not do that -- do a smaller delete, which can be more easily processed. In this case I would have recommended trying sequential deletes of the form: DELETE FROM `tablename` WHERE `columnname` LIKE '-a%'


20

I think we may have overcomplicated the answer that was in required in my case. I have no doubt that both Roland & Rick James are correct with their creation of a temporary table, injecting only rows that pass the filter NOT LIKE '-%' but the solution for me was "easier" because there was an important error I was unaware of until now and for that I ...


0

Better yet, turn it into a multiple-table DELETE. That is where you have a JOIN built into the DELETE. http://dev.mysql.com/doc/refman/5.6/en/delete.html shows two syntaxes for such; you probably want the second.


8

Roland's suggestion can be sped up some by doing both things at once: CREATE TABLE tablename_new LIKE tablename; ALTER TABLE tablename_new ENGINE = InnoDB; INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%' ORDER BY primary_key; RENAME TABLE tablename TO tablename_old, tablename_new TO tablename ; DROP TABLE ...


22

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko) The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace: Lots of Transactional Changes Very Long ...


0

Is there a problem? Or are you expecting to grow significantly? 10M/day = 120/second, which is high, but not necessarily the limit. innodb_flush_log_at_trx_commit = 1 is the safest, but it is the slowest. A value of 2 will give you a boost in performance. Batching INSERTs is also a performance boost; however you may not be able to do so because of how ...


0

WHERE playable_character = 0 AND date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now() Start with the "=" item, then do the range: INDEX(playable_character, date_published); "Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you ...


0

Something like this? Using SHOW VARIABLES and SHOW GLOBAL STATUS: Expression: innodb_buffer_pool_size / _ram Meaning: % of RAM used for InnoDB buffer_pool Recommended range: 60~80% Expression: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests Meaning: Read requests that had to hit disk Recommended range: 0-2% What to do if out of range: ...


2

Take a look at my answers to similar questions here, here and here. Basically, it's very simple - a database is a dynamic entity with components on disk, in RAM and in the CPU at the same time. There are all sorts of buffers and caches being filled, emptied and flipping between disk, RAM and CPU. InnoDB is an MVCC engine which allows backup software to ...


0

I agree with "bad form". Plan A: Here's another approach when you do need to add another column(s). Create a new table with the same PRIMARY KEY ("Vertical partition"). (But not AUTO_INCREMENT.) Put the new column(s) in it. The JOIN to fetch the new data will be messy in your code, but only when you actually need those new columns. Also, make it a ...


1

When it comes to InnoDB, you are right to be concerned. Here is a Pictorial Representation of InnoDB (Made by Percona CTO Vadim Tkachenko) The whole left side of the diagram represents InnoDB's moving parts in memory. The critical piece here is the InnoDB Buffer Pool The InnoDB Buffer Pool holds three things Data Pages : If a page is dirty, it is ...


0

Row locks are held for the duration of a transaction. If there is contention, one of the first things you should do is make sure the code path between updating the row and committing is as tight as possible. Useful diagnostics to be able to see these locks: SHOW CREATE TABLE pics SHOW ENGINE INNODB STATUS SELECT * FROM information_schema.innodb_trx


0

What is the value of sort_buffer_size? 200M might be reasonable on your machine. (A sort might be involved in the index creation.) Keep in mind that it will rebuild all the indexes. Still 58 hours is unreasonable. What is the value of innodb_buffer_pool_size? It may be using that. It should be about 11G for that size machine, assuming you are using ...


0

Any UPDATE to a row requires the following (sooner or later): Read the 16KB block containing the row (unless cached in the buffer pool) Write the block back to disk (or cache it a long time) The "doublebuffer" is written once per transaction Do one write to the InnoDB log for transactional integrity. (The details here depend on various settings and ...


2

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc. How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running? My points are: (a) The benchmark is stressing the ...



Top 50 recent answers are included