Author Topic: tokudb my.cnf suggestion (worked with my hardware)  (Read 25292 times)

sudo

  • Guest
tokudb my.cnf suggestion (worked with my hardware)
« 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

Offline nzeLover

  • Decent Indexer
  • ***
  • Posts: 58
  • Helpful: +6/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #1 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..

// nzeLover
---
Using nZEDb with Ubuntu 14.04 LTS on 2 x dedicated servers.

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #2 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.

sudo

  • Guest
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #3 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...

Offline nzeLover

  • Decent Indexer
  • ***
  • Posts: 58
  • Helpful: +6/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #4 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.
// nzeLover
---
Using nZEDb with Ubuntu 14.04 LTS on 2 x dedicated servers.

sudo

  • Guest
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #5 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

Offline randsome

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #6 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.

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #7 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;'"

Offline randsome

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #8 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.

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #9 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. :)

Offline randsome

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #10 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.
[/img]

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #11 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. :)
« Last Edit: 2013-06-22, 09:00:40 pm by jonnyboy »

Offline nzeLover

  • Decent Indexer
  • ***
  • Posts: 58
  • Helpful: +6/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #12 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..
// nzeLover
---
Using nZEDb with Ubuntu 14.04 LTS on 2 x dedicated servers.

Offline randsome

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #13 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.
« Last Edit: 2013-06-23, 10:02:08 am by randsome »

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
Re: tokudb my.cnf suggestion (worked with my hardware)
« Reply #14 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.