nZEDb

nZEDb => General Talk => Topic started by: sudo on 2013-06-17, 07:17:01 pm

Title: tokudb my.cnf suggestion (worked with my hardware)
Post by: sudo on 2013-06-17, 07:17:01 pm
this helped my tokudb run much better than default...

Code: [Select]
# The MySQL server
[mysqld]
datadir = /var/lib/mysql
basedir = /opt/tokutek/mysql
user = mysql
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
thread_concurrency = 8
net_buffer_length = 2K
thread_stack = 240K
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: nzeLover on 2013-06-19, 06:39:32 am
Was that a 'fairly easy' task to get up and running - I think here about install etc ?

And thanks for the my.cnf sample..

Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: jonnyboy on 2013-06-19, 07:43:26 am
Is it faster now? When i ran it, it was slower than myisam and appeared to be locked up for 20000 sec before i killed it.
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: sudo on 2013-06-19, 03:06:27 pm
Is it faster now? When i ran it, it was slower than myisam and appeared to be locked up for 20000 sec before i killed it.

it was faster for a while, but it is still locking up...
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: nzeLover on 2013-06-19, 10:33:08 pm
So - For now, there is no reason to fiddle with it, because of those locking tables/locking up ?

I will wait until you smart people have found a good solution.. and perhaps some text on how to do the transform/install to TokuDB.
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: sudo on 2013-06-20, 05:37:46 am
So - For now, there is no reason to fiddle with it, because of those locking tables/locking up ?

I will wait until you smart people have found a good solution.. and perhaps some text on how to do the transform/install to TokuDB.

correct - avoid for now
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: randsome on 2013-06-22, 10:07:42 am
Is it faster now? When i ran it, it was slower than myisam and appeared to be locked up for 20000 sec before i killed it.

Curious - what are you using to gauge the differences in performance between myisam and tokudb? Thanks.
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: jonnyboy on 2013-06-22, 10:12:03 am
Is it faster now? When i ran it, it was slower than myisam and appeared to be locked up for 20000 sec before i killed it.

Curious - what are you using to gauge the differences in performance between myisam and tokudb? Thanks.

This command shows me the processlist for mysql:
Code: [Select]
watch -n .1 "mysql -uroot nzedb -e 'show processlist;'"
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: randsome on 2013-06-22, 10:56:38 am

This command shows me the processlist for mysql:
Code: [Select]
watch -n .1 "mysql -uroot nzedb -e 'show processlist;'"

Thanks so much! I just migrated to your tokudb vm instance and haven't observed anything odd but I'll keep an eye out. One thing, it doesn't want to consume every resource in its path. I'm running similar threads as with innodb and its barely using the 8GB assigned to the vm. With innodb, I was up to 20GB and it was easily using 18-19GB once running for any period. Also, the vm is 1/2 the size of the previous innodb instance after importing the database and copying over nzbfiles, covers, etc. Nothing particularly scientific about my process but thought it might interest some.
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: jonnyboy on 2013-06-22, 11:24:30 am

This command shows me the processlist for mysql:
Code: [Select]
watch -n .1 "mysql -uroot nzedb -e 'show processlist;'"

Thanks so much! I just migrated to your tokudb vm instance and haven't observed anything odd but I'll keep an eye out. One thing, it doesn't want to consume every resource in its path. I'm running similar threads as with innodb and its barely using the 8GB assigned to the vm. With innodb, I was up to 20GB and it was easily using 18-19GB once running for any period. Also, the vm is 1/2 the size of the previous innodb instance after importing the database and copying over nzbfiles, covers, etc. Nothing particularly scientific about my process but thought it might interest some.
Did you convert the tables to TokuDB? The vm tables are MyIsam. :)
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: randsome on 2013-06-22, 07:58:44 pm
Did you convert the tables to TokuDB? The vm tables are MyIsam. :)

:) I did - I used your script to do the conversion.
(http://i43.photobucket.com/albums/e388/ririzarry/Clipboard01_zps50e34eed.gif)[/img]
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: jonnyboy on 2013-06-22, 08:28:51 pm
And your performance is good? I'm re-powering the vm, now. I'll give it another run.

The reason that I would love to see this work, is simple. With large tables, the performance increases over that of MyIsam and InnoDB with the added benefit of smaller table sizes. This should allow for better performance with larger collections/binaries/parts tables.

Using the my.cnf posted above and increasing thread concurrency has greatly improved performance over what it was. But, it's still early. :)
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: nzeLover on 2013-06-22, 11:10:21 pm
And I indeed hope for some guiding, in form of text, on how to install/setup TokuDB on ubuntu server, so I and perhaps others can be helping with testing.

I indeed like the reading about the lower memory footprint for using the tokudb engine and large tables.

Thank you all for input in this thread..
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: randsome on 2013-06-23, 06:41:55 am
And your performance is good? I'm re-powering the vm, now. I'll give it another run.

The reason that I would love to see this work, is simple. With large tables, the performance increases over that of MyIsam and InnoDB with the added benefit of smaller table sizes. This should allow for better performance with larger collections/binaries/parts tables.

Using the my.cnf posted above and increasing thread concurrency has greatly improved performance over what it was. But, it's still early. :)

I'm also running the posted my.cnf and so far performance looks good. I've also been running the watch show processlist command you provided and I've yet to see anything hang - its humming along quite nicely.

Here's what my environment looks like in case it helps.

Host Machine: I'm running VMware Workstation 9.02 under Windows 7. The desktop has 32 GB RAM with an i7-2600K CPU overclocked to 3.4 GHz. The OS runs off an SSD drive.

VM: The vm has 8GB RAM and 8 cores assigned - enough to pin the host cpu at times. It sits on a dedicated WD Scorpio Black drive. An exception is in place so that antivirus doesn't scan vmware-vmx.exe (Workstation process). I'm using tmpfs for the tmp and tmpunrar directories.

Workstation tweaks: In VMware Workstation Preferences, Additional Memory is set to Fit All Virtual Machine Memory into reserved host RAM. The vm has been updated to virtual hardware version 9 and I've disabled memory page trimming.
Title: Re: tokudb my.cnf suggestion (worked with my hardware)
Post by: jonnyboy on 2013-06-23, 07:11:13 am
Using tmux scripts, non sequential after 6 hours a stage 7 query was at 21000 sec. I rebooted and I am running sequential, I hope not to dee that again.