List:Cluster« Previous MessageNext Message »
From:Reza Iskandar Achmad Date:October 8 2010 1:09pm
Subject:Re: Very Slow Insert Performance
View as plain text  
Hello Yves,

We are using 8 thread for the SQL nodes. Yes probably increasing
ndb_autoincrement_prefetch_sz, using INFILE while loading the records or
splitting INSERT value into rows would increase the performance.

Thanks for your advice.

Regards,

reza


On 10/5/10 6:25 PM, "Johan Andersson" <johan.andersson@stripped> wrote:

> 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