Tag Info

Hot answers tagged

2

Moving to Galera would be an option. Then your application has to be aware of 'deadlock detected try restarting transaction' errors. Another possibility is to set up a read slave and configure your application to use this slave for read_only queries. If one slave isn't enough you can add more and use them with a loadbalancer of any kind. Examples are ...


2

In SQL Server you can do this with a recursive CTE, but unfortunately this isn't supported by mySQL. Instead, you need a recursive function/sp which calls itself to get the full tree, such as the following: set @@session.max_sp_recursion_depth=255; delimiter $$ create procedure get_bu_tree ( in parent int ) begin create table if not exists ...


1

This is a very common question, and unfortunately the answer is that we've fallen into one of the most serious deficiencies that MySQL has. In MSSQL, we could use recursive CTEs to accomplish this type of result. Just about every other RDBMS has some implementation that will allow for recursive querying. Unfortunately, in order to accomplish this cleanly ...


1

If you are using MySQL 5.6, InnoDB is already the default. Please do not use storage-engine because it is deprecated and probably explains the error. You should use default-storage-engine or default_storage_engine in my.cnf instead [mysqld] default-storage-engine = InnoDB GIVE IT A TRY !!!


1

You should be able to migrate to MySQL Cluster (NDB Cluster), as new versions support foreign keys already and it is easy for scaling the load. However, as stated by Rick, maybe it would be good first to make sure, if dealing with response times by putting additional servers is the only option here. Please also note MySQL Cluster keeps all data in memory, ...


1

Last Sentence of MySQL Documentation on Traditional InnoDB Auto-Increment Locking says You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter. Therefore, if the second INSERT had failed and rolled back, it makes the gap. You may have to set ...


1

if you have the mysql-utilities installed you can use mysqldbcopy this happily works as a shell_exec command in php too. example: /* copy db from source to destination - this is actually pretty fast */ $command = "mysqldbcopy --source=user:pass@localhost --destination=user:pass@localhost test:test_2"; $output = shell_exec($command);


1

I have mentioned ALGORITHM=INPLACE in two other posts Mar 27, 2014 : When tables are locked does MySQL queue queries? Feb 12, 2015 : How to evolve MySQL schema while maintaining integrity In the second post, I named some caveats on using online DDL The online schema change feature uses the folder mapped in tmpdir. Make sure the copy of the table can ...


1

IMHO and in the described use-case, you will never use more than one core. The reason is that your workload is IO bound, not CPU bound. As your 3 connections are creating a new Index, each of those needs to read the whole table from disk: this is what is taking time, not computing the Indexes.


1

Are you using MyISAM? Or InnoDB? Or both? [!!] Query cache is disabled This is "good", not "bad". [!!] Table cache hit rate: 0% (400 open / 78K opened) How many tables to you have? Thousands? Please elaborate on why so many. (This is usually a design flaw.) Meanwhile, increase table_open_cache to, say, 2000. What MySQL version are you ...


1

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...


1

I don't have enough reputation to comment on the previous answers. So I thought I'd elaborate a bit. 1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted. ON UPDATE defaults to RESTRICT, which means the UPDATE on the parent record will fail. 2) ON DELETE action defaults to RESTRICT, which means ...



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