Load performance expectations

DwayneBerry

Hi, I have a 3 way cluster with 6.1.2 freshly installed on Redhat 6.4. I am new to vertica but have lots of other DB experience. I am loading a moderate sized DB onto it. Each machine in the cluster is identical. Hexacore AMD processors, 16GB mem, fast SSD drives (io tests shows 500MB/s speed capable), private 1Gb network. All machines are real, no virtualization. The file I am loading is about 150GB The command I am issuing to execute the load is COPY relationship FROM :input_file DELIMITER '|' NULL '' DIRECT; Pretty much copied from the example There are 3 integer columns in the table. Every row has 3 values (no nulls) There are about 8 Billion rows in the table It is taking 4.75 hrs to load this data, which seems like a long time. The machine running the load goes to about 80% CPU utilization and the network goes to about 20MB/sec. The other 2 nodes go to about 30% cpu utilization with network utilization around 10MB/s. All the machines report very little io via iostat. I have a private 10Gbe network available for these machines but do not want to deploy it until I have a base line on 1GB network. Do the numbers I am seeing appear correct to experienced vertica users. Is there anything I can do to reduce these times? Thanks for your time.

[Deleted User]

Hm... Depending on the performance of your CPU cores, those numbers do seem slow to me as well. (On the Intel side, I'd expect something like that performance out of a bunch of higher-end Atom processors.) The CPU-usage profile also seems a bit off to me. By any chance do you have CPU clock-scaling enabled on these machines? Vertica's threading model interacts quite poorly with some power-management technologies; sometimes they keep clock speeds low even if Vertica would like your CPUs to be going at full speed. Vertica's documentation recommends disabling all CPU power management in BIOS and in Linux for this reason. If you want more performance, you might want to try out Vertica's new parallel-load library: https://my.vertica.com/docs/6.1.x/HTML/index.htm#20146.htm (Vertica's original COPY implementation was designed to perform well with many small files. The parallel-load library is being developed to optimize for a single large initial file.) If your table has a lot of projections, that can also slow load performance. But it will also drive network traffic way up, so that doesn't sound like it's an issue here.

DwayneBerry

Adam, Thank you. It appears thst it was cpu scaling causing an issue. In the end I had to turn off all cool and quiet and all C1 states in the BIOS to get to to perform OK. OS configuration didn't seem to help. The CPU's are 3.3Ghz AMD 1100T processor (no slouches there) The time it take is now 89 minutes to load the largest file to the three nodes. The load pattern is very different from a utilization perspective. The main load node (runnig the load command) go to about 30% usage then peaks to 100% cpu for 5 secs then nack to 30% for 5 secs and then back to 100% and cycles this way. The network now varie much more in its usage (suspect somewhat data related as differnt files load give very differnt usgae patterns. It has peaked put to about 85MB/s but generaly runs at around 24MB/s. The client nodes? sit at 1% usage for 5 secs jump up to 100% usage then drop back to 1% for 5 secs and this continues for the load process (or as much as I watched) Does that sound like a more realistic time? Do you think I would be able to get it down further? What is the main mimiting factor? regards Michael. P.S. Is the community edition 6.1.2 still restricted to just 3 nodes?

[Deleted User]

Hi Michael, That sounds considerably more realistic for the load time. Though the usage pattern still seems a bit odd for me. Vertica tends to operate on blocks of data. I suspect what you're seeing is, Vertica spends 5 seconds parsing a block, then 5 seconds shipping that block around and waiting for the other nodes to receive. 85MB/sec is not 125MB/sec (== 1gbps) but it's getting there. Some lower-end gigabit network cards can only do around 80-90MB/sec... For other performance: Vertica's default parser is single-threaded; it's intended to efficiently load lots of small files concurrently (such as you'd get out of a typical cluster computation) rather than one huge file. If you have the option, you'll see much better performance if you load your data as many small files rather than one big file. (Even more so if the files aren't all on the initiator node; COPY lets you do that.) Alternatively, there's the parallel-parse loader from my previous post; it uses a different parsing algorithm that takes advantage of more CPU cores, so you should peak at much higher than 100% (== one core busy?). Also, for performance: Vertica COPY DIRECT has (roughly speaking) two phases. First it parses the file; then it sorts and compresses the data into projection storage. These two phases tend to use system resources rather differently, and may have different bottlenecks. The "load_streams" system table will give you a sense of where you are in the process. Adam P.S. Yes, the 6.1.2 CE still only allows 3 nodes. We will, however, gladly sell you an EE license if you'd like to run more :-)

DwayneBerry

Adam, Thanks. I should be more accurate. The cpu blips appear to have 2 cores go to 100% both on loading node and other nodes. On the loading node when the 2 thread 100% blip is not occuring all the cores on the machine report between 25% and 50% utilization Will move onto the Database designer and see what it thinks I should do next Thanks

Adam_1

I have a machine with 32 cores and Vertica underutilizes the resources during loading (average usage of all the CPUs is less than 20%). Is there any way to harness all the cores and accelerate the loading?

id10t

Hi!
 

Is there any way to harness all the cores and accelerate the loading?

With info, provided by you - NO. 
If your bottleneck is IO (and CPU is 80% IDLE at same moment), no matter what you will do with CPU it will not improve IO (and so loads too).