You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by charles THIBAULT <ch...@gmail.com> on 2011/05/03 12:30:24 UTC
low performance inserting
Hello everybody,
first: sorry for my english in advance!!
I'm getting started with Cassandra on a 5 nodes cluster inserting data
with the pycassa API.
I've read everywere on internet that cassandra's performance are better than
MySQL
because of the writes append's only into commit logs files.
When i'm trying to insert 100 000 rows with 10 columns per row with batch
insert, I'v this result: 27 seconds
But with MySQL (load data infile) this take only 2 seconds (using indexes)
Here my configuration
cassandra version: 0.7.5
nodes : 192.168.1.210, 192.168.1.211, 192.168.1.212, 192.168.1.213,
192.168.1.214
seed: 192.168.1.210
My script
*************************************************************************************************************
#!/usr/bin/env python
import pycassa
import time
import random
from cassandra import ttypes
pool = pycassa.connect('test', ['192.168.1.210:9160'])
cf = pycassa.ColumnFamily(pool, 'test')
b = cf.batch(queue_size=50,
write_consistency_level=ttypes.ConsistencyLevel.ANY)
tps1 = time.time()
for i in range(100000):
columns = dict()
for j in range(10):
columns[str(j)] = str(random.randint(0,100))
b.insert(str(i), columns)
b.send()
tps2 = time.time()
print("execution time: " + str(tps2 - tps1) + " seconds")
*************************************************************************************************************
what I'm doing rong ?
Re: low performance inserting
Posted by charles THIBAULT <ch...@gmail.com>.
I have understood my error.
I was comparing bulk insert in MySQL with batch_insert in Cassandra
I made this two tests from a remote machine
I have made the same test with one insert per row in MySQL and it took
4mn28s
but the result I have with the stress utility it seems to be abnormal, isn't
it?
2011/5/3 mcasandra <mo...@gmail.com>
> Did you do a bulk upload with mysql from the same machine or separate
> insert/commit for each row? And did you run inserts from the same machine
> as
> the mysqld server?
>
> --
> View this message in context:
> http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/low-performance-inserting-tp6326832p6327957.html
> Sent from the cassandra-user@incubator.apache.org mailing list archive at
> Nabble.com.
>
Re: low performance inserting
Posted by mcasandra <mo...@gmail.com>.
Did you do a bulk upload with mysql from the same machine or separate
insert/commit for each row? And did you run inserts from the same machine as
the mysqld server?
--
View this message in context: http://cassandra-user-incubator-apache-org.3065146.n2.nabble.com/low-performance-inserting-tp6326832p6327957.html
Sent from the cassandra-user@incubator.apache.org mailing list archive at Nabble.com.
Re: low performance inserting
Posted by charles THIBAULT <ch...@gmail.com>.
Hi Sylvain,
thanks for your answer.
I'd make a test with the stress utility inserting 100 000 rows with 10
columns per row
I use these options: -o insert -t 5 -n 100000 -c 10 -d
192.168.1.210,192.168.1.211,...
result: 161 seconds
with MySQL using inserts (after a dump): 1.79 second
Charles
2011/5/3 Sylvain Lebresne <sy...@datastax.com>
> There is probably a fair number of things you'd have to make sure you do to
> improve the write performance on the Cassandra side (starting by using
> multiple
> threads to do the insertion), but the first thing is probably to start
> comparing things
> that are at least mildly comparable. If you do inserts in Cassandra,
> you should try
> to do inserts in MySQL too, not "load data infile" (which really is
> just a bulk loading
> utility). And as stated here
> http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html:
> "When loading a table from a text file, use LOAD DATA INFILE. This is
> usually 20 times
> faster than using INSERT statements."
>
> --
> Sylvain
>
> On Tue, May 3, 2011 at 12:30 PM, charles THIBAULT
> <ch...@gmail.com> wrote:
> > Hello everybody,
> >
> > first: sorry for my english in advance!!
> >
> > I'm getting started with Cassandra on a 5 nodes cluster inserting data
> > with the pycassa API.
> >
> > I've read everywere on internet that cassandra's performance are better
> than
> > MySQL
> > because of the writes append's only into commit logs files.
> >
> > When i'm trying to insert 100 000 rows with 10 columns per row with batch
> > insert, I'v this result: 27 seconds
> > But with MySQL (load data infile) this take only 2 seconds (using
> indexes)
> >
> > Here my configuration
> >
> > cassandra version: 0.7.5
> > nodes : 192.168.1.210, 192.168.1.211, 192.168.1.212, 192.168.1.213,
> > 192.168.1.214
> > seed: 192.168.1.210
> >
> > My script
> >
> *************************************************************************************************************
> > #!/usr/bin/env python
> >
> > import pycassa
> > import time
> > import random
> > from cassandra import ttypes
> >
> > pool = pycassa.connect('test', ['192.168.1.210:9160'])
> > cf = pycassa.ColumnFamily(pool, 'test')
> > b = cf.batch(queue_size=50,
> > write_consistency_level=ttypes.ConsistencyLevel.ANY)
> >
> > tps1 = time.time()
> > for i in range(100000):
> > columns = dict()
> > for j in range(10):
> > columns[str(j)] = str(random.randint(0,100))
> > b.insert(str(i), columns)
> > b.send()
> > tps2 = time.time()
> >
> >
> > print("execution time: " + str(tps2 - tps1) + " seconds")
> >
> *************************************************************************************************************
> >
> > what I'm doing rong ?
> >
>
Re: low performance inserting
Posted by Sylvain Lebresne <sy...@datastax.com>.
There is probably a fair number of things you'd have to make sure you do to
improve the write performance on the Cassandra side (starting by using multiple
threads to do the insertion), but the first thing is probably to start
comparing things
that are at least mildly comparable. If you do inserts in Cassandra,
you should try
to do inserts in MySQL too, not "load data infile" (which really is
just a bulk loading
utility). And as stated here
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html:
"When loading a table from a text file, use LOAD DATA INFILE. This is
usually 20 times
faster than using INSERT statements."
--
Sylvain
On Tue, May 3, 2011 at 12:30 PM, charles THIBAULT
<ch...@gmail.com> wrote:
> Hello everybody,
>
> first: sorry for my english in advance!!
>
> I'm getting started with Cassandra on a 5 nodes cluster inserting data
> with the pycassa API.
>
> I've read everywere on internet that cassandra's performance are better than
> MySQL
> because of the writes append's only into commit logs files.
>
> When i'm trying to insert 100 000 rows with 10 columns per row with batch
> insert, I'v this result: 27 seconds
> But with MySQL (load data infile) this take only 2 seconds (using indexes)
>
> Here my configuration
>
> cassandra version: 0.7.5
> nodes : 192.168.1.210, 192.168.1.211, 192.168.1.212, 192.168.1.213,
> 192.168.1.214
> seed: 192.168.1.210
>
> My script
> *************************************************************************************************************
> #!/usr/bin/env python
>
> import pycassa
> import time
> import random
> from cassandra import ttypes
>
> pool = pycassa.connect('test', ['192.168.1.210:9160'])
> cf = pycassa.ColumnFamily(pool, 'test')
> b = cf.batch(queue_size=50,
> write_consistency_level=ttypes.ConsistencyLevel.ANY)
>
> tps1 = time.time()
> for i in range(100000):
> columns = dict()
> for j in range(10):
> columns[str(j)] = str(random.randint(0,100))
> b.insert(str(i), columns)
> b.send()
> tps2 = time.time()
>
>
> print("execution time: " + str(tps2 - tps1) + " seconds")
> *************************************************************************************************************
>
> what I'm doing rong ?
>
Re: low performance inserting
Posted by Roland Gude <ro...@yoochoose.com>.
Hi,
Not sure this is the case for your Bad Performance, but you are Meassuring Data creation and Insertion together. Your Data creation involves Lots of class casts which are probably quite Slow.
Try
Timing only the b.send Part and See how Long that Takes.
Roland
Am 03.05.2011 um 12:30 schrieb "charles THIBAULT" <ch...@gmail.com>:
> Hello everybody,
>
> first: sorry for my english in advance!!
>
> I'm getting started with Cassandra on a 5 nodes cluster inserting data
> with the pycassa API.
>
> I've read everywere on internet that cassandra's performance are better than MySQL
> because of the writes append's only into commit logs files.
>
> When i'm trying to insert 100 000 rows with 10 columns per row with batch insert, I'v this result: 27 seconds
> But with MySQL (load data infile) this take only 2 seconds (using indexes)
>
> Here my configuration
>
> cassandra version: 0.7.5
> nodes : 192.168.1.210, 192.168.1.211, 192.168.1.212, 192.168.1.213, 192.168.1.214
> seed: 192.168.1.210
>
> My script
> *************************************************************************************************************
> #!/usr/bin/env python
>
> import pycassa
> import time
> import random
> from cassandra import ttypes
>
> pool = pycassa.connect('test', ['192.168.1.210:9160'])
> cf = pycassa.ColumnFamily(pool, 'test')
> b = cf.batch(queue_size=50, write_consistency_level=ttypes.ConsistencyLevel.ANY)
>
> tps1 = time.time()
> for i in range(100000):
> columns = dict()
> for j in range(10):
> columns[str(j)] = str(random.randint(0,100))
> b.insert(str(i), columns)
> b.send()
> tps2 = time.time()
>
>
> print("execution time: " + str(tps2 - tps1) + " seconds")
> *************************************************************************************************************
>
> what I'm doing rong ?