Hot answers tagged

4

The 80% is just a general rule of thumb for helping people start with a more sensible value than the default. Buffer pool size calculation can actually be more complicated. Ideally frequently accessed data pages should remain in the buffer pool and you don't want too much eviction to happen because of its impact on performance. This is again generic but a ...


2

There is a open bug report on this for MySQL 5.6.14 Bug #71520 Constantly increasing Innodb_row_lock_current_waits value From the bug report, note this entry [25 Jun 2015 6:58] Zhenye Xie in my environment, I got a strange value. (-1 as uint64) mysql> show status where Variable_name = 'Innodb_row_lock_current_waits'; +----------------------------...


2

Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem from creeping back into the data. You should define the 4 columns as a non-unique index now since this process will involving lots of comparisons using them. ...


2

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.) Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


2

This is a common issue as you scale to some point. There could be many causes; let's first search for the cause; then the solution. long_query_time=0 turn on the slowlog run for a few hours turn off the slowlog (or raise the cutoff to 1) run pt-query-digest to find the 'worst' couple of queries. Those queries may be zillions of 10ms queries, or it may be ...


1

Clearly the data you are trying to start does not belong to the configuration you have in the my.cnf. If you look at the beginning of the log, you will see that there is a mismatch between the files in the system and the InnoDB catalog, meaning that the engine was running before: 2016-07-19 18:11:00 3416 [ERROR] InnoDB: Table dbhipcom/cardplus_erro in the ...


1

I prefer to say "70% of available RAM". So, 70% * (80-20) = 56. But, in reality, it is hard to predict how much 'other' memory mysqld will use. Approaching from the other side, the buffer_pool does not need to be much more than the total of all the Data_length + Index_length as seen from SHOW TABLE STATUS. Here is a longer discussion.


1

If you want the table created in that data directory with .ibd file, do this chown -R mysql:mysql /home/egonzalez/Documentos Then, run this CREATE TABLE t2 (c1 INT PRIMARY KEY) ENGINE=InnoDB DATA DIRECTORY = '/home/egonzalez/Documentos'; The option innodb_file_per_table is enabled by default in MySQL 5.7 The reason for the error in the first place ? ...


1

MySQL has other cases of -1 being stored into an UNSIGNED field. Report it via http://bugs.mysql.com , then don't worry about it.


1

How indexes work in InnoDB. This discussion is limited to InnoDB. (Let me establish some ground work, then I will get to your question.) The PRIMARY KEY is a BTree where the leaf nodes contain all the columns of the row. That is, the PK and the data coexist in the same BTree. Each secondary key is a BTree where the leaf nodes contain a copy of the ...


1

innodb_log_buffer_size should not be set to 4G. The default of 8M is usually sufficient; let's compromise on 100M, ok? case when exists ( select ... ) then ( select ... ) else 0 end --> coalesce( ( select ... ), 0 ) "Creation time" comes out either as YMD format or DMY; did you want that inconsistency? Indexes needed: INDEX(classDesc, LookupId, ...


1

According to release docs innodb_buffer_pool_populate was mapped to innodb_numa_interleave starting with Percona 5.6.27-75.0, so based on what you see, it appears it is no longer preallocating the buffer pool, only setting the numa policy. Regarding innodb_buffer_pool_dump_at_shutdown and load at startup, you might want to have a look at http://dev.mysql.com/...



Only top voted, non community-wiki answers of a minimum length are eligible