New answers tagged

0

Let's do a comparison MyISAM Caching vs InnoDB Caching MyISAM only caches index pages from the .MYI files into a global buffer called the MyISAM keycache (sized by key_buffer_size). MyISAM Data does not cache data global. It only does so per DB Session (sized by read_buffer_size and read_rnd_buffer_size) InnoDB has a very elaborate framework for managing ...


0

The problem is related with the internal representation of "timestamp" fields, probably caused by a bug in the database conversion procedure, occurred while upgrading from a previous version. Exporting and importing the database "mysql" has fixed the problem in my debian server. During the repair procedure, it's preferable to stop Apache and any other ...


0

Given that the other answer might be a problem for you, I would suggest you try pt-archiver instead specifically using the --columns option, of course in combination with --source and --dest option you may be able to pull this off somehow. Good luck!


0

What you do is to a) take a mysqldump of your database: mysqldump --user my_user --password=<pass> --host=my_db schema_name > my_schema.sql b) then use a tool such as sed to substitute MyISAM for InnoDB: sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' my_schema.sql c) restore the dump on your 5.6 instance: mysql --user=my_user --password=<pass&...


0

You are running MySQL 5.7 with PHP on the one server. This shows me that your server is not dedicated to MySQL. PHP and MySQL are evidently competing for RAM. I also do not see any attempt to set the InnoDB buffer pool size. The default value for innodb_buffer_pool_size is 128M (134217728). Please keep in mind something about max_allowed_packet. As the ...


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 ...


0

I had the same problem with Maria DB 10.1.12, then after reading the documentation I found that there is an option to perform the operation "in-place" which eliminates the copy of the table. With this option the alter table is very fast. In my case it was: alter table user add column (resettoken varchar(256), resettoken_date date, resettoken_count int), ...


0

NUMA is not unrelated to buffer pool preallocation. That is the reason why innodb_buffer_pool_populate was introduced in the first place. See https://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/ That is also the reason why it took around 7-8 minutes to start mysql with the older Percona release. Now the upstream implementation replaced ...


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

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/...


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.


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.


0

Find the file #sql-8aa_25 and remove it. It is probably in the same directory where the database is. If you did a kill -9, mysqld did not get a chance to remove the tmp file.


0

EXPLAIN may be saying 2, but the real number could be 1 or 10000. Don't trust EXPLAIN. Here's how to get a more accurate count of rows touched by a query: FLUSH STATUS; SELECT ...; SHOW SESSION STATUS LIKE 'Handler_%';


0

I would do 1 of two things: Use a zip drive or similar to copy the db to another system. With disks being so cheap - 32GB USB stick available in Europe for €12, can't you use one of those? Then, copy your datadir onto it, restore it on another machine. These days, even the most feeble laptop (or a decent tablet) should be able to cope with a db of 32GB. You ...


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 ...


0

At TwinDB we built a web interface to mysqlfrm. It's free and easy to use. To recover table structure from you just need to upload the .frm file. Here are steps. 1. Open https://recovery.twindb.com/ . Click on "Recover Structure" In next submenu click on "from .frm file" On the next view click on "Browse" and select an .frm file on a local disk. Click ...


0

Try using exists operator which should perform a search and stop when the first record that matches the condition is found: select 1 from dual where exists ( select * from someTable where owner_id = 0 and reviewed = 0 );


0

LIMIT provides a window into the result set. LIMIT 1 is equivalent to LIMIT 0, 1; return 1 row starting at offset 0. Explain plan reports the estimated number of rows the LIMIT is running against. However, only one match should be examined. Testing on your SQL Fiddle shows the query runs against the INDEX (KEY) which should be the fastest access method. ...


0

With that many values you are likely to run into estimation errors during query plan generation. This will lead to it choosing worse (slower-performing) plans than it might otherwise. With that many values I would suggest INSERTing them into an actual table, with index(es), which is JOINed to in the query. Logically the two are the same. Practically, ...


0

Plan A: ALTER IGNORE TABLE users ADD UNIQUE(email, alt_email, mobile and alt_mobile); This will go through the table, copy it over, ignoring any dups. I am, however, hesitant to say that it will properly handle NULLs in those columns. Plan B: CREATE TABLE new LIKE users; ALTER TABLE new ADD UNIQUE(email, alt_email, mobile and alt_mobile); INSERT ...


0

Do you really need the entire database to be restored? If you don't, my 2c: You can extract specific tables to do your restore on "chunks". Something like this: zcat your-dump.gz.sql | sed -n -e '/DROP TABLE.*`TABLE_NAME`/,/UNLOCK TABLES/p' > table_name-dump.sql I did it once and it took like 10 minutes to extract the table I needed - my full restore ...


0

I hope this is not the real query: SELECT * FROM t LIMIT ....,1. There is no ORDER BY, so mysql can deliver any row it feels like. You want all the columns. There may be a faster way. Please provide SHOW CREATE TABLE and the actual SELECT. LIMIT 101886,1 collects 101886+1 rows, throws away 101886 rows, and delivers the one. This is abuse of OFFSET. ...


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

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.


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

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'; +----------------------------...


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

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, ...


0

The first thing you want to do is ascertain the size of the table from 2 perspectives Operating System INFORMATION_SCHEMA Please run the following MYSQL_USER=root MYSQL_PASS=rootpass MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}" DB=databasename TB=tablename SQL="SELECT data_length+index_length FROM information_schema.tables" SQL="${SQL} WHERE ...



Top 50 recent answers are included