ALTER TABLE foo Engine=NDB performance

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ALTER TABLE foo Engine=NDB performance

Matthias Rieber
Hi,

I did some test experiments with MySQL Cluster and noticed some unexpected
performance penalty while writing to a remote ndb.

1. sql and ndb node are on the same host

"alter table engine=ndb" needed about 4 minutes for about one million
rows. After enabling another data node the data has been transferred to
the other node with 40Mbits/s.

2. sql and ndb node are on different hosts, network latency is about 0.3ms

"alter table engine=ndb" needed more than 30 minutes. The bandwith between
the sql and the data node was only about 2-3Mbit/s.

I expected that it will be slower, but not that it's gonna be almost 20
times slower. Is that the expected behaviour? Is there an error in my
config?

Regards,
Matthias



-- Version: on: 7.4.6
-- /etc/my.cnf:

[mysql_cluster]
ndb-connectstring=ip1,ip2

[mysqld]
ndbcluster=1
ndb-force-send=1
ndb-index-stat-enable=0
ndb-use-exact-count=0
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=64M
innodb_support_xa=1
default_storage_engine=InnoDB
innodb_buffer_pool_size=700M
innodb_file_per_table=1
optimizer_switch=engine_condition_pushdown=on

-- /var/lib/mysql-cluster/config.ini

[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M
[ndb_mgmd default]
datadir=/var/lib/mysql-cluster

[ndb_mgmd]
hostname=ip1
[ndb_mgmd]
hostname=ip2

[ndbd default]
NoOfReplicas=2
LockPagesInMainMemory=1
DataMemory=10G
IndexMemory=1G
ODirect=1
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
RedoBuffer=32M
MaxNoOfTables=1024
MaxNoOfOrderedIndexes=256

[ndbd]
hostname=ip1
[ndbd]
hostname=ip2

[mysqld]
hostname=ip1
[mysqld]
hostname=ip2


--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER TABLE foo Engine=NDB performance

Johan Andersson-3
Hi,

you may want to set :
 ndb-batch-size=*32M*
in my.cnf (
http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-program-options-mysqld.html#option_mysqld_ndb-batch-size
)

but then you may hit problems with MaxNoOfConcurrentOperations (and that
you have to increase that then.).

It will most likely make dramatic differences. You can also read more here:
http://johanandersson.blogspot.se/2012/04/mysql-cluster-how-to-load-it-with-data.html

Good luck.

BR
johan


On Fri, Jun 5, 2015 at 10:08 PM, Matthias Rieber <[hidden email]>
wrote:

> Hi,
>
> I did some test experiments with MySQL Cluster and noticed some unexpected
> performance penalty while writing to a remote ndb.
>
> 1. sql and ndb node are on the same host
>
> "alter table engine=ndb" needed about 4 minutes for about one million
> rows. After enabling another data node the data has been transferred to
> the other node with 40Mbits/s.
>
> 2. sql and ndb node are on different hosts, network latency is about 0.3ms
>
> "alter table engine=ndb" needed more than 30 minutes. The bandwith between
> the sql and the data node was only about 2-3Mbit/s.
>
> I expected that it will be slower, but not that it's gonna be almost 20
> times slower. Is that the expected behaviour? Is there an error in my
> config?
>
> Regards,
> Matthias
>
>
>
> -- Version: on: 7.4.6
> -- /etc/my.cnf:
>
> [mysql_cluster]
> ndb-connectstring=ip1,ip2
>
> [mysqld]
> ndbcluster=1
> ndb-force-send=1
> ndb-index-stat-enable=0
> ndb-use-exact-count=0
> innodb_flush_log_at_trx_commit=0
> innodb_log_file_size=64M
> innodb_support_xa=1
> default_storage_engine=InnoDB
> innodb_buffer_pool_size=700M
> innodb_file_per_table=1
> optimizer_switch=engine_condition_pushdown=on
>
> -- /var/lib/mysql-cluster/config.ini
>
> [tcp default]
> SendBufferMemory=2M
> ReceiveBufferMemory=2M
> [ndb_mgmd default]
> datadir=/var/lib/mysql-cluster
>
> [ndb_mgmd]
> hostname=ip1
> [ndb_mgmd]
> hostname=ip2
>
> [ndbd default]
> NoOfReplicas=2
> LockPagesInMainMemory=1
> DataMemory=10G
> IndexMemory=1G
> ODirect=1
> NoOfFragmentLogFiles=300
> MaxNoOfConcurrentOperations=100000
> SchedulerSpinTimer=400
> SchedulerExecutionTimer=100
> RealTimeScheduler=1
> TimeBetweenGlobalCheckpoints=1000
> TimeBetweenEpochs=200
> DiskCheckpointSpeed=10M
> DiskCheckpointSpeedInRestart=100M
> RedoBuffer=32M
> MaxNoOfTables=1024
> MaxNoOfOrderedIndexes=256
>
> [ndbd]
> hostname=ip1
> [ndbd]
> hostname=ip2
>
> [mysqld]
> hostname=ip1
> [mysqld]
> hostname=ip2
>
>
> --
> MySQL Cluster Mailing List
> For list archives: http://lists.mysql.com/cluster
> To unsubscribe:    http://lists.mysql.com/cluster
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER TABLE foo Engine=NDB performance

Matthias Rieber
Hi,

On Fri, 5 Jun 2015, Johan Andersson wrote:

> Hi,
>
> you may want to set :
>  ndb-batch-size=*32M*
> in my.cnf (
> http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-program-options-mysqld.html#option_mysqld_ndb-batch-size
> )
>
> but then you may hit problems with MaxNoOfConcurrentOperations (and that
> you have to increase that then.).

Actually I've already found your very helpful blog posts. I think I've
tried that already, but I've double checked. No improvement, however, I
noticed that the table contains a blob(mediumtext). After converting the
column to varchar(255) the speed was very good. The mediumtext column was
already truncated to 255 characters, so the amount of data was unchanged.

I've tried to increase ndb-blob-[read|write]-batch-bytes but it doesn't
seem to have any effect. I'm aware that the MySQL Cluster is not meant to
be used with blobs, but is it possible to improve it?

Btw, why is the upper limit of ndb-batch-size 31536000 (number of seconds
of one year)?

Thanks for your help!

Regards,
Matthias

>
> It will most likely make dramatic differences. You can also read more here:
> http://johanandersson.blogspot.se/2012/04/mysql-cluster-how-to-load-it-with-data.html
>
> Good luck.
>
> BR
> johan
>
>
> On Fri, Jun 5, 2015 at 10:08 PM, Matthias Rieber <[hidden email]>
> wrote:
>
> > Hi,
> >
> > I did some test experiments with MySQL Cluster and noticed some unexpected
> > performance penalty while writing to a remote ndb.
> >
> > 1. sql and ndb node are on the same host
> >
> > "alter table engine=ndb" needed about 4 minutes for about one million
> > rows. After enabling another data node the data has been transferred to
> > the other node with 40Mbits/s.
> >
> > 2. sql and ndb node are on different hosts, network latency is about 0.3ms
> >
> > "alter table engine=ndb" needed more than 30 minutes. The bandwith between
> > the sql and the data node was only about 2-3Mbit/s.
> >
> > I expected that it will be slower, but not that it's gonna be almost 20
> > times slower. Is that the expected behaviour? Is there an error in my
> > config?
> >
> > Regards,
> > Matthias
> >
> >
> >
> > -- Version: on: 7.4.6
> > -- /etc/my.cnf:
> >
> > [mysql_cluster]
> > ndb-connectstring=ip1,ip2
> >
> > [mysqld]
> > ndbcluster=1
> > ndb-force-send=1
> > ndb-index-stat-enable=0
> > ndb-use-exact-count=0
> > innodb_flush_log_at_trx_commit=0
> > innodb_log_file_size=64M
> > innodb_support_xa=1
> > default_storage_engine=InnoDB
> > innodb_buffer_pool_size=700M
> > innodb_file_per_table=1
> > optimizer_switch=engine_condition_pushdown=on
> >
> > -- /var/lib/mysql-cluster/config.ini
> >
> > [tcp default]
> > SendBufferMemory=2M
> > ReceiveBufferMemory=2M
> > [ndb_mgmd default]
> > datadir=/var/lib/mysql-cluster
> >
> > [ndb_mgmd]
> > hostname=ip1
> > [ndb_mgmd]
> > hostname=ip2
> >
> > [ndbd default]
> > NoOfReplicas=2
> > LockPagesInMainMemory=1
> > DataMemory=10G
> > IndexMemory=1G
> > ODirect=1
> > NoOfFragmentLogFiles=300
> > MaxNoOfConcurrentOperations=100000
> > SchedulerSpinTimer=400
> > SchedulerExecutionTimer=100
> > RealTimeScheduler=1
> > TimeBetweenGlobalCheckpoints=1000
> > TimeBetweenEpochs=200
> > DiskCheckpointSpeed=10M
> > DiskCheckpointSpeedInRestart=100M
> > RedoBuffer=32M
> > MaxNoOfTables=1024
> > MaxNoOfOrderedIndexes=256
> >
> > [ndbd]
> > hostname=ip1
> > [ndbd]
> > hostname=ip2
> >
> > [mysqld]
> > hostname=ip1
> > [mysqld]
> > hostname=ip2
> >
> >
> > --
> > MySQL Cluster Mailing List
> > For list archives: http://lists.mysql.com/cluster
> > To unsubscribe:    http://lists.mysql.com/cluster
> >
> >
>

--
email: [hidden email]
xmpp: [hidden email]
isn: 100*1415

--
MySQL Cluster Mailing List
For list archives: http://lists.mysql.com/cluster
To unsubscribe:    http://lists.mysql.com/cluster

Loading...