New answers tagged

0

UPDATE `GLOBAL_VARIABLES` SET `VARIABLE_VALUE`="InnoDB" WHERE `VARIABLE_NAME`="DEFAULT_STORAGE_ENGINE"


0

Looking strictly at the the binary log, there is a flow you can follow Please note what the MySQL Documentation says about binlog_cache_size: The size of the cache to hold changes to the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the ...


0

A Google of "peter zaitsev" file systems buffering double write gives several good references (Peter Zaitsev works for Percona who are big hitters in the MySQL world. Of these, I picked out 4 as being of particular interest[1, 2, 3, 4]. In the first reference, Zaitsev gives a great précis of the various reasons why partial page writes might occur (section ...


2

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries. First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko) STEP 01 : Make sure there is no InnoDB Run the following SELECT table_schema,table_name, (data_length+index_length)/POWER(1024,3) table_sizegb FROM information_schema.tables ...


0

That ALTER will copy the table over, requiring briefly enough space for a copy of the table. InnoDB does a pretty good job of keeping its BTrees somewhat free of wasted space. Hence the action won't do much good. Be sure that innodb_file_per_table is still on; otherwise you will be moving the table into ibdata1, which is quite hard to shrink. Certain ...


0

Restart the mysql services with innodb_file_per_table=on in my.cnf and rename table to another database and again to original data base then it will create file per table option


1

MyISAM tables have many problems and limitations, but one very convenient aspect to them in cases like this, is that the underlying .frm (table metadata) and MYI/MYD index and data files can be easily copied around since there are no transaction logs to worry about. If you don't care about the InnoDB tables at all, you could avoid the need to shrink the ...


2

The process you mentioned works fine but it takes much amount of time if the tables are bigger in size Here the way you can do it quickly .but it requires some down time depends on the table size . Take the full backup of 20 tables that are InnoDB. Drop those 20 tables that are InnoDB Stop mysql Delete the ibdata files Start the mysql services with ...


1

I can think of a perfect case for it, and we have tested thoroughly and run it in production...I call it the "fast lane" clustering strategy: If you do read-write splitting with a proxy like MaxScale, or your application is capable, you can send some of the reads for those seldom invalidated tables only to slaves that have the query cache turned on, and the ...


1

InnoDB uses "optimistic" MVCC - when you do some changes, it applies them to the real tables and may even write them to disk. Commit only ensures that all the writes were finished and makes the changes visible and is otherwise almost instantaneous. That means that the rollback has to undo all these changes, possibly rewriting the pages on disk again and it ...


0

Note: I posted this answer to a related question on stackoverflow. This solution is Linux and Systemd-specific, but in fact, can be adapted to any system that properly supports memlock calls and provides the capability to do so for processes that don't stay root. Update: This solution might not, in fact, work that well. See note at end. There exist a class ...


0

Apart of the very good advice given by Rolando, you can, on the system side, activate a swap-less setting using sysctl. I usually set vm.swappiness=10 on MySQL machine in /etc/sysctl.conf. It gives restricted access to the swap, but permits it if required. The default value of vm.swappiness is 60, which is very permissive.


3

1) InnoDB always saves the previous version of the row. Everything is copy on write therefore every update is a select -> copy -> write. For how long it is being kept is dependent on the open transactions (obviously as long as you have an open transaction that needs to be able to see an old version it cannot be removed) how busy your server is (purge lag) ...


3

When I see messages of that nature, I usually blame the redo logs. This is where is LSN is written. The problem may stem from not preparing the incremental backup. What seems to be missing is the use of the --prepare option. What this essentially does is create the backup rolled forward to a specific point in time. This is particularly true if there were ...


1

Amit, the problem is that this variable is set to reload a copy of the buffer pool dumped to disk during a stop/restart process. In order to achieve this you need to set innodb_buffer_pool_dump_at_shutdown to ON prior to restart your server, please check this link for further info: https://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.html ...


0

After digging the question, I went with a SELECT like this one: SELECT table_name from information_schema.tables WHERE table_schema = 'mydb' AND DATA_FREE > 0 ORDER by DATA_FREE DESC; So I have a list of table to process. a scripted form would be : #! /bin/bash [ -z "$1" ] && echo usage "$0 [database_name]" [ -z "$1" ] && exit 1 for T ...


2

MyISAM table is stored as a heap, rows are being placed inside the data file into an empty place they can fit. If you are only inserting then that means appending new rows, but when the rows are being updated or deleted, some gaps are created and then later filled by other rows so the order of full table scan (which is what dump does by default) is not ...


2

That smells a lot like a "many-to-many" relationship table. Is it? As for linking from a changelog -- space is not the issue; correctness is. A change log should have exactly what existed at the time the log was taken. What if the original table is updated? If the table in question is changed for any reason, I think you do not want the change reflected ...


3

(Sorry for the delay; there are some important issues.) The following things won't explain your mutex questions, but may improve performance such that the questions go away. Observations: Version: 5.5.41-MariaDB-wsrep 3.7 GB of RAM Uptime = 61d 21:40:40 You are not running on Windows. Running 64-bit version You appear to be running entirely (or mostly) ...


1

If the column is not indexed then it does not matter how many times (or with how many values) it is used, a table scan seems inevitable (unless there is some other indexed condition). But there is no need to scan multiple times. MySQL optimizer tries to find the most effective plan, if nothing better is possible, it iterates over the table and checks each ...


0

The relevant configuration is this: ibtmp1:12M:autoextend:max:512M You are restricting your temporary tables (and similarly your main innodb tablespace via innodb_data_file_path) to 512M total. This includes implicit (disk) temporary tables that might be created by various ORDER BY / GROUP BY / DISTINCT operations. MySQL reports this failure relative ...


1

The subject is quite broad and this could (actually does) fill a full book even. If you're interested in more details I recommend reading some. I'll try to summarise the baseline. InnoDB is using pages to store data. Normally it is 16k but is configurable on some MySQL forks (Percona for example). This is the smallest item which is read from or written to ...



Top 50 recent answers are included