MySQL Performance: MySQL => mysql performance on freebsd 7

Members

Help

Home

Home » Performance » MySQL » mysql performance on freebsd 7

Show: Today's Messages :: Show Polls :: Message Navigator

mysql performance on freebsd 7 [message #3104]

Tue, 20 May 2008 09:47

nijikon
Messages: 2
Registered: May 2008
Location: Cracow

Junior Member

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(4283<min,4283,min), max = IF(4283>max,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

escape from the execution is futile

Report message to a moderator