GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql performance on freebsd 7

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql performance on freebsd 7

    I have a question regarding MySQL performance on FreeBSD 7.

    I'm using mysql 5.1.24-rc from ports in jail environment.

    I have a table in MEMORY engine with 150000 records in it.

    CREATE TABLE IF NOT EXISTS param_stat_short_level_1_1 (
    param int(11) NOT NULL,
    object int(11) NOT NULL,
    parent_level_0 varchar(32) NOT NULL,
    `value` int(11) NOT NULL,
    min int(11) NOT NULL,
    max int(11) NOT NULL,
    total int(11) NOT NULL,
    counter int(11) NOT NULL,
    modification_name timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    day_tag date NOT NULL,
    KEY param (param),
    KEY object (object),
    UNIQUE param_object (param, object)
    ) ENGINE=MEMORY DEFAULT CHARSET=latin2;

    I'm performing a query, always a different param, and object values, value is rand(1,10000)

    INSERT INTO param_stat_short_level_1_0 SET param = '10311', object = '10311', value = '4283' ON DUPLICATE KEY UPDATE param = '10311', object = '10311', value = '4283', min = IF(4283max,4283,max), counter = counter+1;

    My point here is that MySQL on Linux performs 150000 queries in an average of 18.1099, when the same queries on FreeBSD takes 28.7643.

    Did I misconfigured something or this are the proper result and FreeBSD just could not give more?

    My configs below, thanks for Your time.

    [root@p1 ~]# cat /boot/loader.conf
    kern.ipc.semmni=512
    kern.ipc.semmns=1024
    kern.ipc.semmnu=512
    kern.ipc.nmbclusters=32768
    kern.maxdsiz=2073741824 # 2GB
    kern.dfldsiz=2073741824 # 2GB
    kern.maxssiz="536870912" # 512MB

    [root@p1 ~]# cat /etc/sysctl.conf
    kern.ipc.shmall=2097152
    kern.ipc.shmmax=4294967296
    kern.ipc.semmap=256
    kern.ipc.shm_use_phys=1
    kern.ipc.somaxconn=2048
    kern.ipc.nmbclusters=32768

    [root@p1 ~]# cat /disks/1/jails/mysql1/home/mysql/db/my.cnf
    [mysqld]
    set-variable = max_connections=1024
    set-variable = max_connect_errors=10000
    set-variable = max_user_connections=1024

    set-variable = thread_concurrency=16
    thread_cache = 10

    query_cache_limit = 64M
    query_cache_size = 1024M
    query_cache_type = 1

    key_buffer = 1024M
    key_cache_age_threshold = 300
    key_cache_block_size = 1024
    key_cache_division_limit = 100

    set-variable = max_heap_table_size=1G
    set-variable = tmp_table_size=1G

    set-variable = event_scheduler=ON

  • #2
    anyone?

    Comment

    Working...
    X