No, I'm afraid not. 32 bit architectures have a theoretical limit of 4G of memory space for the entire application: in actual practice, for a variety of reasons too complex to go into here (and are well documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS.
- michael dykman On 8/28/07, Justin <[EMAIL PROTECTED]> wrote: > 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] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
