Today I achieved something which is a first in the MySQL
server as far as I'm aware of. I managed to run a query
with multiple threads. The query was:
ALTER TABLE t1 ADD COLUMN b int;
and the table had 4 partitions in it. So it used 4 threads
that each thread handled the copying of data from old
table to new table of one partition.
Currently it's designed for use by partitioned tables but
it should be very straightforward to do minor parallelisation
also of non-partitioned tables by e.g. breaking up in a scan
thread and a write thread.
It's nice to get started on this track and see how one can
make use of modern computers with a great deal of CPU power
if one can parallelise the applications. As an example a
dual socket box T5220 (2 Niagara II CPU's) can handle 128
threads in parallel.
5 comments:
That's great!
When we evaluated 5.1 I was surprised that it didn't do this already....
Parallel dispatch is one of the advantages for sharding/partitioning.
We ended up implementing our own partitioning engine though which supports PDS ...
Well, if you have myisam_repair_threads > 1 then when you repair, alter or load data infile on a table, then the table indexes may be created in parallel.
Does that work alongside your approach, or are the two mutually exclusive?
From what I can see MyISAM repair can be
done in parallel and this is independent
of this new approach (currently this
only affects ALTER TABLE and also only
alter table that uses copying of data
between tables. I will extend it further
later on.
Hi Mikael,
We are one of the heavy users of MySql partitions, have couple of few TB large data warehouses. This is huge welcome change from our point of view. It will be ideal if we can do something like data dictionary changes for adding , removing columns. This will remove huge copying of files in some cases where same could be achieved with simply modifying the data dictionary, Ex: Oracle db.
Post a Comment