List:Cluster« Previous MessageNext Message »
From:Johan Andersson Date:October 5 2010 11:25am
Subject:Re: Very Slow Insert Performance
View as plain text  
Hi,

Are you using many threads or single thread insert?
Cluster is a parallel data store so to get good speed you should insert 
from many threads in parallel and/or use batching (insert into x 
values(a,b) (1,1), (2,2), (3,3) ... )

If you use auto_increments a lot, increase  ndb_autoincrement_prefetch_sz.


BR
johan

Reza Iskandar Achmad wrote:
> Hello Yves,
> These are the configs. Anyway, how do I know current insert rate?
>
> Thanks,
> reza
>
> #---- Begin my.cnf ---#
> [mysqld]
>         ndbcluster
>         default-storage-engine=ndbcluster
>         datadir=/srv/mysql/data
>         basedir=/srv/mysql
>         ndb-connectstring=172.21.0.1
>         ndb_cluster_connection_pool=8
>         slow_query_log=1
>         long_query_time=3
>         log_queries_not_using_indexes=1
>         ndb_use_exact_count=0
>         ndb_index_stat_enable=0
>         ndb_force_send=1
>         ndb_autoincrement_prefetch_sz=100
>         engine_condition_pushdown=1
>         skip-name-resolve
>         #safe-show-database
>         skip-external-locking
>         #skip-bdb
>         skip-external-locking
>         back_log = 50
>         long_query_time = 3
>         max_connections = 3500
>         key_buffer_size = 512M
>         myisam_sort_buffer_size = 24M
>         join_buffer_size = 1M
>         read_buffer_size = 1M
>         sort_buffer_size = 4M
>         table_cache = 20000
>         thread_cache_size = 10000
>         wait_timeout = 60
>         connect_timeout = 60
>         tmp_table_size = 512M
>         max_heap_table_size = 512M
>         max_allowed_packet = 24M
>         net_buffer_length = 16384
>         thread_concurrency = 16
>         concurrent_insert = 2
>         table_lock_wait_timeout = 30
>         read_rnd_buffer_size = 786432
>         bulk_insert_buffer_size = 8M
>         query_cache_limit = 2M
>         query_cache_size = 512M
>         query_cache_type = 1
>         query_prealloc_size = 262144
>         query_alloc_block_size = 65536
>         transaction_alloc_block_size = 8192
>         transaction_prealloc_size = 4096
>         max_write_lock_count = 1
>         max_write_lock_count = 1
>
> [mysqld_safe]
>         nice = -10
>         open_files_limit = 65535
>
> [mysqldump]
>         quick
>         max_allowed_packet = 16M
>
> [myisamchk]
>         key_buffer = 64M
>         sort_buffer = 128M
>         read_buffer = 64M
>         write_buffer = 64M
> [mysql_cluster]
>         ndb-connectstring=172.21.0.1
>                   
> #-- END OF my.cnf ---#
>
>
> #--- config.ini ----#
> [ndbd default]
>         NoOfReplicas=2
>         DataMemory=57G
>         IndexMemory=20G
>         datadir=/srv/mysql/data
>         #LockPagesInMainMemory=1
>         #LogLevelStartup=15
>         ODirect=1
>         NoOfFragmentLogFiles=2000
>         MaxNoOfConcurrentOperations=1000000
>         MaxNoOfLocalOperations=1100000
>         TimeBetweenGlobalCheckpoints=1000
>         TimeBetweenEpochs=100
>         DiskCheckpointSpeed=10M
>         DiskCheckpointSpeedInRestart=100M
>         RedoBuffer=32M
>         # MaxNoOfLocalScans=64
>         MaxNoOfTables=1024
>         MaxNoOfOrderedIndexes=2000
>         MaxNoOfAttributes=6000
>         #NoOfUniqueHashIndexes=400
>         #NoOfTriggers=6000
>         MaxNoOfExecutionThreads=8
>
> [mysqld default]
>
> [ndb_mgmd default]
>
> [tcp default]
>         SendBufferMemory=8M
>         ReceiveBufferMemory=8M
>
> [ndb_mgmd]
>         hostname=ndb-mysqld-01
>         LogDestination=FILE:filename=/var/log/mysql/ndb_cluster.log
>         datadir=/srv/mysql/data
> [ndbd]
>         HostName=ndb-node-01
> [ndbd]
>         HostName=ndb-node-02
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
> [mysqld]
>
>
>
>
> On 10/5/10 4:44 AM, "Yves Trudeau" <y.trudeau@stripped> wrote:
>
>   
>> Hi Reza,
>>   what is the current insert rate, schema, config.ini, my.cnf?
>>
>> Regards,
>>
>> Yves
>>
>>
>> On Tue, 2010-10-05 at 03:00 +0700, Reza Iskandar Achmad wrote:
>>     
>>> Hello All,
>>>
>>> We're performing a database import from standalone MySQL to Cluster. The
>>> import procedure is using dump file. The problem is insert is very slow,
>>> takes ages to finish.
>>>
>>> Our Cluster is in Gigabit Network, RAID 5 disks, nbdmtd. How can I
>>> investigate and find the solution for this situation?
>>>
>>> Thanks,
>>> reza
>>>
>>>
>>>
>>>       
>
>
>
>   

Thread
Very Slow Insert Performance Reza Iskandar Achmad4 Oct
  • Re: Very Slow Insert PerformanceStewart Smith2 Nov
Re: Very Slow Insert PerformanceReza Iskandar Achmad5 Oct
  • Re: Very Slow Insert PerformanceJohan Andersson5 Oct
    • Re: Very Slow Insert PerformanceReza Iskandar Achmad8 Oct