Tag Info

New answers tagged

0

When you do your update, set the lastUpdate column equal to itself, that way it won't default in the current timestamp: update productsStock set productName="HP PRINTER 20000", lastUpdate = lastUpdate where product_reference="001";


1

The InnoDB buffer pool caches queries; if you have less usage on one of the nodes then you're going to have less cache used. If you're not properly load-balancing queries then the amount of cache in use on each node would be different. If you just turned on a node and added it to the cluster the amount of cache would be different than the others one that ...


1

Install percona-toolkit. Then you can use pt-online-schema-change. I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, ...


0

You are probably trying to import very large rows. The 5 MB default log file size (10MB in total for the log) may be too small in many cases. The default for 5.6 is 50MB (100Mb in total), which is probably a more sane default in most cases. If you do not have much load, the only problem of augmenting the log is having to handle a larger set of files, ...


0

There is one simple way around this, although admittedly, in certain circumstances you may not want to do this. Since this issue stems from an InnoDB internal reference, you can simply create this table with the same name, same columns, only using a different storage engine. I ran into this on a MySQL slave, and even though the master I was replicating from ...


1

Unless your WordPress site is huge, you are unlikely to see much performance improvement in switching to TokuDB, if any at all. TokuDB's 20x performance advantage is for an indexed insertion workload, where the secondary indexes do not fit in memory. You will be using less space on disk with TokuDB's compression.


0

Test the inner query to see how well it performs. If you are missing an index on the foreign key, performance may be very slow. The size of your result set can cause performance issues with inner joins. The inner join may need to be run once for each row in your result set. Not having enough memory can cause an issue as data may need to be re-read from ...


0

If your auto-increments do have a meaning outside of an arbitrary indentifier, maybe they shouldn't be auto_increments, but simple int or bigint unsigned. You can drop the auto_increment particle and just insert max(id)+1, but that would create you all sorts of concurrency problems (that the auto_increment solves for you automatically). You could store the ...


2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


0

Here is the quick fix: Just add a UNIQUE KEY on tagName in the cardTags table SAMPLE SCRIPT DROP DATABASE IF EXISTS testing; CREATE DATABASE testing; USE testing CREATE TABLE 61furiousFistPokemon( cardNumber int NOT NULL PRIMARY KEY, pokemonName VARCHAR(12), type VARCHAR(10), stage VARCHAR(10), evolvesFrom VARCHAR(12), HP INT, retreatCost INT, weakness ...


1

tagName column in table cardTags is not defined as primary key and you are declaring foreign key FOREIGN KEY (tagName) REFERENCES cardTags(tagName).


0

Very often it happens, when the foreign key and the reference key don't have same type or same length. Click here for more details.


1

It appears to be a change in the optimizer. I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key. On 5.6 It was using the secondary char1(8) index. Adding force index (primary) got it back to it's 3-5 minute count time.


0

You'll need to change the people_meta.person_id values before deleting the row from the people table, because the engine wouldn't know how to replace the values with the duplicates. update people_meta set person_id = 1 where person_id = 0; delete from people where id = 0;


0

If your MySQL server is handing all those connections parallely and you are trying to perform select and insert queries simultaneously you may end up with a lock. Your queries will be waiting to return the results on a locked tables. Check which queries are taking time by running SHOW PROCESSLIST Refine the queries that takes long time to compute, ...


1

OBSERVATION #1 Take a look back at your top output Cpu(s): 84.0%us, 5.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 4.5%hi, 5.0%si, 1.4%st See the last status variable 1.4%st ? What is that ? According to In Linux “top” command what are us, sy, ni, id, wa, hi, si and st (for CPU usage)? st, "steal time", is only relevant in virtualized environments. It ...


1

Improving performance is usually not a single stop solution. But looking at your conf and the results of mysql tuner you can do the following Increase innodb_buffer_pool_size by a LOT. That is usually quite critical for InnoDB tables. Further set the following two paramenters in you cnf file for InnoDB sort_buffer_size=2M join_buffer_size=2M You ...


1

Because you ran the mysql_upgrade on both master and slave, the ALTER TABLE was happening twice on slave side. The changes on master was propagated to slave, trying to add columns which you already added with manual mysql_upgrade. I don't think reset slave has anything to with the problem, unless you were looking to skip the entire block of statements done ...


1

You literally do not need a third party tool. STEP 01 Find out the path to the data by running mysql> SHOW GLOBAL VARIABLES LIKE 'datadir'; For this example, let's suppose it is C:\Program Files\MySQL\MySQL 5.6\data STEP 02 Pick a destination folder to copy the data directory For this example, let's pick D:\MySQLBackup STEP 03 Create batch file ...


0

Queries can become slow in such cases: server is shorted out with disk i/o server is shorted out with memory and swapped server have too small key_buffer_size and buffer should be refreshed prior to perform the query server have too small (join|sort)_buffer_size and on-disk temporary tables are created Any of the mentioned above can cause sporadical ...


0

During that period can you get a list of the sql processes running? show full processlist; Is this a server where you don't have root access to the actual OS like in AWS? If you do have root access and this is something you can research it might be unrelated to MySQL, i.e., some other process on the server is affecting performance. This might be out of ...


0

You will need to tune InnoDB with the following innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_buffer_pool_size = 512M innodb_log_file_size = 128M innodb_log_buffer_size = 32M You should then download and run mysqltuner.pl You should get output resembling this # ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden ...


1

No, rollbacks cannot be done in the background. Both that and the double write checks have to be done before accepting connections again (meaning that if you kill it, you will have to start again on restart). REDO process, however, can and is done in the background. What you can do is kill mysql (again) and restart it skipping that step: /etc/init.d/mysqld ...


0

If you are using transactions, please ensure that you use either a COMMIT or ROLLBACK statement before closing the connection. If this is already the case, you probably need to increase the thread_cache_size value in order to reuse as much as possible the existing connection threads instead of creating new ones from start. Please note the following values: ...


0

Well unfortunately your query is wrong. There is no location column in the ps table as you have declared, so you cannot use it as the key for joining the two tables together. Try this and use product_reference as your joining key, as it exists in both tables: SELECT p.product_reference, p.productName, ps.physicalStock, FROM products AS ...


0

Try to recover the database using force_recovery option 4 or higher but this can permanently corrupt the database as options higher than this will try to commit transactions which wasn't completed. If the recovery worked, and you wont like to recover each database by hand then start MySQL by skipping grant tables and dump all the databases. Install a fresh ...


0

My situation is slightly different. One of the data elements I need to store in each row is potentially very large. (The data field is a LONGBLOB for a document that may contain multiple embedded images. My sample database contains documents as large as 25 - 30 MB, but these documents could be larger in some cases.) None of the solutions I found online ...


0

The solution I found for myself is: stop both master and slave remove logs replace ib* files and /var/lib/mysql/mybase/* with files from prod backup start mysql on master start mysql on slave with --skip-slave-start, then SLAVE RESET; SLAVE START; Not sure if this is correct, but it works for now.


-1

MySQL is taking too much of the innodb buffer pool size for your table locking. Insert innodb_buffer_pool_size=2g in my.cnf file, which is most probably in /etc/mysql/



Top 50 recent answers are included