From: Justin (mysqlb0rker.com)
Date: Tue Aug 28 2007 - 12:35:43 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

heh.. ok I'll throw that at my system tech. and see what it's going to
take to get a 64bit version of an OS.
for now I'll limit to 2.5

thanks michael.

----- Original Message -----
From: "Michael Dykman" <mdykmangmail.com>
To: "Justin" <mysqlb0rker.com>
Cc: <mysqllists.mysql.com>
Sent: Tuesday, August 28, 2007 1:31 PM
Subject: Re: servers full potential / FT searches locking tables

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 <mysqlb0rker.com> 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" <mathieu.bruneauargontechnologies.ca>
> To: "Justin" <mysqlb0rker.com>
> Cc: <mysqllists.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/mysql?unsub=mdykmangmail.com
>
>

--
 - michael dykman
 - mdykmangmail.com

 - 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/mysql?unsub=mysqlb0rker.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]