这VM CPU太weak怎么Identify? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Java - 爪哇娇娃

这VM CPU太weak怎么Identify?

这VM CPU太weak怎么Identify?# Java - 爪哇娇娃

l*u2013-09-17 07:09

1 楼

I am going to import very large stock market data from multiple .txt files (
over 3000 files) into sql database. I was wondering which is the most
efficient way to do that?
I used bulk insert and it takes me forever time (estimated 7 days)

r*s2013-09-17 07:09

2 楼

Perf问题，
我总觉着是VM CPU太weak，
可能Ops放太多VM share一个CPU,
怎么Identify这个问题？
至少让我不用担心VM的问题？
现在那CPU动不动就99.9%.

B*g2013-09-17 07:09

3 楼

SSIS

(

【在 l****u 的大作中提到】

: I am going to import very large stock market data from multiple .txt files (
: over 3000 files) into sql database. I was wondering which is the most
: efficient way to do that?
: I used bulk insert and it takes me forever time (estimated 7 days)

g*g2013-09-17 07:09

4 楼

动不动就99.9%不就证明CPU不够给力了吗？一般cpu monitoring是通过脚本，用另外的
机器来管理的。
常用的命令是top。

【在 r*****s 的大作中提到】

: Perf问题，
: 我总觉着是VM CPU太weak，
: 可能Ops放太多VM share一个CPU,
: 怎么Identify这个问题？
: 至少让我不用担心VM的问题？
: 现在那CPU动不动就99.9%.

j*n2013-09-17 07:09

5 楼

No dude, SSIS is slower than bcp...
if I am doing this, i'd like to have dba to extend the db size to at least
100GB more with multiple data files on different disks; also i'd like to set
up db to simple recovery model, and set bcp commitment to every 500,000
rows; oh remember remove all indexes from the table first.
anything else?

【在 B*****g 的大作中提到】

: SSIS
:
: (

e*t2013-09-17 07:09

6 楼

没啥好办法。
让ops尝试把其他的VM换到别的node去，如果还是不能改善你application的
performance，那就只有2条路：
1. scale vertically, 上更强劲的新机器.
2. scale horizontally, 上更多的机器，增加你application的throughput.

【在 r*****s 的大作中提到】

: Perf问题，
: 我总觉着是VM CPU太weak，
: 可能Ops放太多VM share一个CPU,
: 怎么Identify这个问题？
: 至少让我不用担心VM的问题？
: 现在那CPU动不动就99.9%.

p*l2013-09-17 07:09

7 楼

Pretty much these things. Specifying batchsize is important.

set

【在 j*****n 的大作中提到】

: No dude, SSIS is slower than bcp...
: if I am doing this, i'd like to have dba to extend the db size to at least
: 100GB more with multiple data files on different disks; also i'd like to set
: up db to simple recovery model, and set bcp commitment to every 500,000
: rows; oh remember remove all indexes from the table first.
: anything else?

e*t2013-09-17 07:09

8 楼

他这个就在hypervisor上，直接用hypervisor的UI看就好了。

【在 g*****g 的大作中提到】

: 动不动就99.9%不就证明CPU不够给力了吗？一般cpu monitoring是通过脚本，用另外的
: 机器来管理的。
: 常用的命令是top。

c*t2013-09-17 07:09

9 楼

1. Bulk Insert is faster than SSIS if you turn on the table lock.
2. If you can make the data in all files are sorted, you can save the time
on index rebuild.
3. No matter Simple Recovery or Bulk Mode, SQL Server will start writing
huge data to log file after the first file, the size usually be 2~3 times
the data you are importing, so I suggest you shrink the log file after each
bulk-insert each file. (MSSQL will not write too much data to the log file
if the table is empty)

i*c2013-09-17 07:09

10 楼

write a class,do sql insert only

j*72013-09-17 07:09

11 楼

I think the most important is to load the files in parallel, other
considerations include:
set DB recovery mode to be Simple
pre-allocate database files and optimize the disk system
are you going to load to a single table? can consider a partitioned design
perhaps.

G*d2013-09-17 07:09

12 楼

maybe because of tons of reason
RAID?
recovery model?
even with SSIS, it should not take such long time...
check your log/locks and if you have profiler running you can monitor it.