Re: optimising a create table as select
> Thanks once again.
My pleasure.
> memory increased and the resize the innodb_buffer_pool_size.
That may, or may not, be necessary. I have seen workloads where the data is 100 times as big as the data, yet because of the access patterns, there would have been no advantage in increasing the buffer_pool_size.
If you wish to provide
SHOW VARIABLES;
SHOW GLOBAL STATUS; -- after mysql has been up for at least a day.
RAM size
Then I will do some obscure arithmetic to see if you need to buffer_pool to be bigger yet.
If you have a large dataset and slave, you might rethink the cost effectiveness of using a cloud service.
> I cut my teeth on Oracle 7
Most of MySQL is simpler than Oracle 7. And cheaper. And (mostly) as good.
> 1. multi-versioning where reads don't block writes and writes don't block reads, with cloud technologies and read only replicas, the significance of this changed.
InnoDB employs MVCC, which allows many (but not all) things to be non-blocking. The big thing that is likely to be blocking (in MySQL) is DDL (ALTER, etc). Often there are workarounds (such as manually chunking).
> Absolute consistency
Percona XtraDB Cluster _may_ be better at assuring that than ordinary Replication. It is a little like RAC, but even work reasonably across a WAN.
I hope you have spread your servers across "availability zones" (for Business Continuity).
> Obviously for Banks etc...
I gather your application is like "banking".
> I suggest you look at Percona's tools -- there are several that deal with checking consistency (after the fact).
If you provide the VARIABLES, I will point out a few that are important for InnoDB consistency.