32bit, but I have all available memory..
MemTotal: 8179612 kB
MemFree: 43684 kB
on the box. I think the 4gb is only windows.
All my tables are in myisam
so if I was to set
key_buffer_size=5500M
That'd be acceptable?
----- Original Message -----
From: "Mathieu Bruneau" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, August 28, 2007 12:51 AM
Subject: Re: servers full potential / FT searches locking tables
> Your settings doesn't seem optimized much.
>
> So here first question, do you use 32bits or 64 bits platform? If you
> have
> 64 bits platform with 64 bits mysql and os you can boost most the
> settings
> to use almost the 8G of ram you have on the server. If you are using
> 32bits you will have to do some calculation so you don't go over ~2.6G
> (why not 4Gb?, go read on that on the net)
>
> So the 2 most importants settings are:
> key_buffer_size (mainly myisam table)
> and/or
> innodb_buffer_pool_size (innodb table)
>
> Depending if you're using more innodb or myisam (or a mix) you'll tweak
> those pamareters differently, it's usually however not recommended to
> go
> over 4Gb for the key_buffer_size. MyIsam only stores the key into that
> buffer, so you don't have much index, not worth taking it too big for
> no
> reason. Innodb however can cache data as well, and will benefit from
> the
> biggest value possible.
>
> The server generate statistic that you can look to know the effect of
> that. If you are using phpmyadmin in the variables and status part you
> can
> see the index usage to guide you.
>
>
> You can have a look at the different my.cnf that comes with mysql
> distribution they put comment in there with interesting value for
> thumbs
> rule. Here the except for key_buffer_size and innodb_buffer_pool_size:
> # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
> # Do not set it larger than 30% of your available memory, as some
> memory
> # is also required by the OS to cache rows. Even if you're not using
> # MyISAM tables, you should still set it to 8-64M as it will also be
> # used for internal temporary disk tables.
> key_buffer_size=2G
>
> # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
> # row data. The bigger you set this the less disk I/O is needed to
> # access data in tables. On a dedicated database server you may set
> this
> # parameter up to 80% of the machine physical memory size. Do not set
> it
> # too large, though, because competition of the physical memory may
> # cause paging in the operating system. Note that on 32bit systems you
> # might be limited to 2-3.5G of user level memory per process, so do
> not
> # set it too high.
> innodb_buffer_pool_size=2G
>
> Regards,
> --
> Mathieu Bruneau
> aka ROunofF
>
> ===
> GPG keys available @ http://rounoff.darktech.org
>
> Justin a écrit :
>> Ok.. Straight to the point.. Here is what I currently have.
>>
>> MySQL Ver 14.12 Distrib 5.0.27
>> RHEL vs 5
>> 584GB Raid 5 storage
>> 8GB of RAM
>> and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon)
>>
>> what my question is.. is am I utilizing the servers potential with the
>> following as my settings. The server is a dedicated MySQL server so I
>> want all power to go to the server. It just seems to be laggy at
>> times.
>> And I want to be sure I've optimized to the fullest potential
>>
>> My biggest issue is with FT searches. Tables get locked during larger
>> queries and I can't select anything when that happens. Is there any
>> way
>> not to lock the tables on a Full Text search? (does that make sense?)
>>
>> thanks again for any insight
>>
>> Justin.
>>
>> Here's a dump of the my.cnf and the phpmyadmin dump of vars.
>> ------------
>> /etc/my.cnf
>>
>> [mysqld]
>> datadir=/var/lib/mysql
>> socket=/var/lib/mysql/mysql.sock
>> wait_timeout=60
>> default-character-set=utf8
>> max_allowed_packet = 3000M
>> max_connections = 5000
>> ft_min_word_len=3
>>
>> server-id=1
>> log-error = /var/log/mysql/error.log
>> expire_logs_days = 3
>>
>>
>> # Default to using old password format for compatibility with mysql
>> 3.x
>> # clients (those using the mysqlclient10 compatibility package).
>> old_passwords=0
>>
>> [mysql.server]
>> user=mysql
>>
>> [mysqld_safe]
>> err-log=/var/log/mysql/mysqld.log
>> pid-file=/var/run/mysqld/mysqld.pid
>> ------------
>>
>> auto increment increment 1
>> auto increment offset 1
>> automatic sp privileges ON
>> back log 50
>> basedir /
>> binlog cache size 32,768
>> bulk insert buffer size 8,388,608
>> character set client utf8
>> character set connection utf8
>> character set database utf8
>> character set filesystem binary
>> character set results utf8
>> character set server utf8
>> character set system utf8
>> character sets dir /usr/share/mysql/charsets/
>> collation connection utf8_general_ci
>> collation database utf8_general_ci
>> collation server utf8_general_ci
>> completion type 0
>> concurrent insert 1
>> connect timeout 5
>> datadir /var/lib/mysql/
>> date format %Y-%m-%d
>> datetime format %Y-%m-%d %H:%i:%s
>> default week format 0
>> delay key write ON
>> delayed insert limit 100
>> delayed insert timeout 300
>> delayed queue size 1,000
>> div precision increment 4
>> engine condition pushdown OFF
>> expire logs days 3
>> flush OFF
>> flush time 0
>> ft boolean syntax + -><()~*:""&|
>> ft max word len 84
>> ft min word len 3
>> ft query expansion limit 20
>> ft stopword file (built-in)
>> group concat max len 1,024
>> have archive YES
>> have bdb NO
>> have blackhole engine NO
>> have compress YES
>> have crypt YES
>> have csv NO
>> have dynamic loading YES
>> have example engine NO
>> have federated engine NO
>> have geometry YES
>> have innodb YES
>> have isam NO
>> have merge engine YES
>> have ndbcluster NO
>> have openssl DISABLED
>> have query cache YES
>> have raid NO
>> have rtree keys YES
>> have symlink YES
>> init connect
>> init file
>> init slave
>> innodb additional mem pool size 1,048,576
>> innodb autoextend increment 8
>> innodb buffer pool awe mem mb 0
>> innodb buffer pool size 8,388,608
>> innodb checksums ON
>> innodb commit concurrency 0
>> innodb concurrency tickets 500
>> innodb data file path ibdata1:10M:autoextend
>> innodb data home dir
>> innodb doublewrite ON
>> innodb fast shutdown 1
>> innodb file io threads 4
>> innodb file per table OFF
>> innodb flush log at trx commit 1
>> innodb flush method
>> innodb force recovery 0
>> innodb lock wait timeout 50
>> innodb locks unsafe for binlog OFF
>> innodb log arch dir
>> innodb log archive OFF
>> innodb log buffer size 1,048,576
>> innodb log file size 5,242,880
>> innodb log files in group 2
>> innodb log group home dir ./
>> innodb max dirty pages pct 90
>> innodb max purge lag 0
>> innodb mirrored log groups 1
>> innodb open files 300
>> innodb support xa ON
>> innodb sync spin loops 20
>> innodb table locks ON
>> innodb thread concurrency 8
>> innodb thread sleep delay 10,000
>> interactive timeout 28,800
>> join buffer size 131,072
>> key buffer size 8,388,600
>> key cache age threshold 300
>> key cache block size 1,024
>> key cache division limit 100
>> language /usr/share/mysql/english/
>> large files support ON
>> large page size 0
>> large pages OFF
>> lc time names en_US
>> license GPL
>> local infile ON
>> locked in memory OFF
>> log OFF
>> log bin OFF
>> log bin trust function creators OFF
>> log error /var/log/mysql/error.log
>> log queries not using indexes OFF
>> log slave updates OFF
>> log slow queries OFF
>> log warnings 1
>> long query time 10
>> low priority updates OFF
>> lower case file system OFF
>> lower case table names 0
>> max allowed packet 1,073,740,800
>> max binlog cache size 4,294,967,295
>> max binlog size 1,073,741,824
>> max connect errors 10
>> max connections 5,000
>> max delayed threads 20
>> max error count 64
>> max heap table size 16,777,216
>> max insert delayed threads 20
>> max join size 18446744073709551615
>> max length for sort data 1,024
>> max prepared stmt count 16,382
>> max relay log size 0
>> max seeks for key 4,294,967,295
>> max sort length 1,024
>> max sp recursion depth 0
>> max tmp tables 32
>> max user connections 0
>> max write lock count 4,294,967,295
>> multi range count 256
>> myisam data pointer size 6
>> myisam max sort file size 2,147,483,647
>> myisam recover options OFF
>> myisam repair threads 1
>> myisam sort buffer size 8,388,608
>> myisam stats method nulls_unequal
>> net buffer length 16,384
>> net read timeout 30
>> net retry count 10
>> net write timeout 60
>> new OFF
>> old passwords OFF
>> open files limit 25,010
>> optimizer prune level 1
>> optimizer search depth 62
>> pid file /var/lib/mysql/dbs.live.pid
>> port 3,306
>> preload buffer size 32,768
>> prepared stmt count 0
>> protocol version 10
>> query alloc block size 8,192
>> query cache limit 1,048,576
>> query cache min res unit 4,096
>> query cache size 0
>> query cache type ON
>> query cache wlock invalidate OFF
>> query prealloc size 8,192
>> range alloc block size 2,048
>> read buffer size 131,072
>> read only OFF
>> read rnd buffer size 262,144
>> relay log purge ON
>> relay log space limit 0
>> rpl recovery rank 0
>> secure auth OFF
>> server id 1
>> skip external locking ON
>> skip networking OFF
>> skip show database OFF
>> slave compressed protocol OFF
>> slave load tmpdir /tmp/
>> slave net timeout 3,600
>> slave skip errors OFF
>> slave transaction retries 10
>> slow launch time 2
>> socket /var/lib/mysql/mysql.sock
>> sort buffer size 2,097,144
>> sql big selects ON
>> sql mode
>> sql notes ON
>> sql warnings OFF
>> ssl ca
>> ssl capath
>> ssl cert
>> ssl cipher
>> ssl key
>> storage engine MyISAM
>> sync binlog 0
>> sync frm ON
>> system time zone EDT
>> table cache 64
>> table lock wait timeout 50
>> table type MyISAM
>> thread cache size 0
>> thread stack 196,608
>> time format %H:%i:%s
>> time zone SYSTEM
>> timed mutexes OFF
>> tmp table size 33,554,432
>> tmpdir /tmp/
>> transaction alloc block size 8,192
>> transaction prealloc size 4,096
>> tx isolation REPEATABLE-READ
>> updatable views with limit YES
>> version 5.0.27-standard
>> version comment MySQL Community Edition - Standard (GPL)
>> version compile machine i686
>> version compile os pc-linux-gnu
>> wait timeout 60
>> Open new phpMyAdmin window
>>
>>
>>
>>
>>
>>
>>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
