Re: NDB slow - Mikael Ronström - com.mysql.lists.cluster

4 messages in com.mysql.lists.clusterRe: NDB slow

From	Sent On	Attachments
Mattias Nilsson	19 Oct 2004 02:22
Mikael Ronström	19 Oct 2004 03:48
Mattias Nilsson	19 Oct 2004 05:13
Heikki Tuuri	21 Oct 2004 11:23

Subject:	Re: NDB slow
From:	Mikael Ronström (mik...@mysql.com)
Date:	10/19/2004 03:48:49 AM
List:	com.mysql.lists.cluster

Hi Mattias,

Your test should write 10000 * 40 * 150 Bytes of REDO log in NDB => Total of 60 MByte REDO log written.

For MyISAM the writes goes into the file system cache in Linux and Linux will not write the file system cache until it needs the memory for other purposes. NDB flushes all log writes periodically to ensure they have actually hit the disk. (Config parameter for this is TimeBetweenGlobalCheckpoints). You have also configured InnoDB to not flush logs and I am not sure when InnoDB in that case actually flushes the logs.

So from the explanations below I would assume that both InnoDB and MyISAM is performing all actions of the inserts in memory (buffer pool of InnoDB fits the entire test set) during the test run whilst NDB is hitting on the disk fairly hard.

NoOfDiskPagesToDiskAfterRestartTUP is the config parameter that sets the speed of writing the checkpoints in NDB which by default is 3.2 MByte per second which with a 50 MByte DataMemory is too fast. It is quite enough to write it at close to the minimum speed by setting it to 2 (160 kB /second)

NoOfDiskPagesToDiskAfterRestartACC Same applies here (default is 20 => 1.6 MByte).

This decreases the I/O load by 4 MB/sec.

TimeBetweenLocalCheckpoints is another parameter you can change. This is set to start a new local checkpoint after 4 MByte of writes. Setting it to 25 means 128 MB of writes is needed before a local checkpoint is started.

These settings should remove most of the I/O load during the inserts without endangering the system reliability.

For test purposes if you want to see the impact of I/O in NDB you can set the configuration parameter Diskless to 1. This effectively removes all disk writes (but also recovery from disk) from the test.

Rgrds Mikael

2004-10-19 kl. 11.22 skrev Mattias Nilsson:

Hi all

Just doing my initial tests on NDB, and running into some seriously low numbers. I am running 4.1.7-gamma (source) on a Dual P3 600 with 768MB.

I have configured my cluster with only one storage (1 replica) and one API; [NDBD DEFAULT] NoOfReplicas: 1 [MYSQLD DEFAULT] [NDB_MGMD DEFAULT] [TCP DEFAULT] [NDB_MGMD] HostName = cluster1 [NDBD] HostName = cluster1 DataDir: /var/lib/mysql-cluster/ DataMemory = 50M IndexMemory = 50M [MYSQLD]

My /etc/my.cnf: [mysqld] # Enable NDB Cluster ndbcluster # Set InnoDB Parameters innodb_data_home_dir = /data/innodb_data/ innodb_data_file_path = db1:200M:autoextend:max:50000M innodb_buffer_pool_size=128M innodb_additional_mem_pool_size=4M innodb_log_group_home_dir=/logs/innodb_logs innodb_log_arch_dir=/logs/innodb_logs innodb_log_archive=0 innodb_log_files_in_group=3 innodb_log_file_size=128M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_file_io_threads=4 innodb_lock_wait_timeout=30 # Limit connections max_connections=50

My test case table: CREATE TABLE test1 ( main_id bigint(20) unsigned NOT NULL default '0', sub_id bigint(20) unsigned NOT NULL default '0', col1 smallint(5) unsigned NOT NULL default '0', col2 tinyint(3) unsigned NOT NULL default '0', col3 tinyint(3) unsigned NOT NULL default '0', col4 smallint(6) NOT NULL default '0', col5 smallint(5) unsigned NOT NULL default '0', col6 tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (main_id, sub_id, col2,col3,col4), KEY index2 (sub_id,col3,col4) ) ENGINE=NDB;

Test data is 10000 inserts, each of 40 rows, fed via the mysql client.

I try this with both InnoDB, MyISAM and NDB. Timings are nearly identical on MyISAM and InnoDB, but it takes more than twice the time using NDB.

I have also noticed some serious I/O during import into NDB, almost 10 times to InnoDB and MyISAM. My machine is not swapping.

Can someone give me any notes/ideas/explanations on why NDB is being so slow, and why it's generating so much I/O (in-memory?). Am I missing something?

Mikael Ronström, Senior Software Architect MySQL AB, www.mysql.com

Clustering: http://www.infoworld.com/article/04/04/14/HNmysqlcluster_1.html

http://www.eweek.com/article2/0,1759,1567546,00.asp