List:General Discussion« Previous MessageNext Message »
From:Justin Date:August 31 2007 8:28pm
Subject:Re: servers full potential / FT searches locking tables
View as plain text  
Alright.. I think I see what's is happening after this latest lockup..

here's what I think is happening..

When a replace into query locks a table for a few seconds there are a boot 
load of connections to the db, and then when the table is unlocked the 
connections start to filter through and usually they all finish and de-queue 
nicely but this last time it seemed there were 400-500 constant connections 
never actually going away.. the query it's self finished. but there was one 
right behind it to take it's place..

Almost like it's giving it's self a dos..  Is there any settings I can 
adjust on the server to help with this? or would it be more on the code 
side.

As always when I restart the instance of mysql all goes back smoothly so it 
makes me wonder if it's something in the mysql config that is lagging for 
some reason.

thanks.


----- Original Message ----- 
From: "Michael Dykman" <mdykman@stripped>
To: "Justin" <mysql@stripped>
Cc: <mysql@stripped>
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 <mysql@stripped> 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.bruneau@stripped>
> To: "Justin" <mysql@stripped>
> Cc: <mysql@stripped>
> 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=1
>
>


-- 
 - michael dykman
 - mdykman@stripped

 - All models are wrong.  Some models are useful.

Thread
servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
    • Re: servers full potential / FT searches locking tablesRolando Edwards27 Aug
      • Re: servers full potential / FT searches locking tablesJay Pipes27 Aug
        • Re: servers full potential / FT searches locking tablesJustin27 Aug
          • Re: servers full potential / FT searches locking tablesnigel wood27 Aug
    • Re: servers full potential / FT searches locking tablesJustin27 Aug
  • Re: servers full potential / FT searches locking tablesMathieu Bruneau28 Aug
    • Re: servers full potential / FT searches locking tablesJustin28 Aug
      • Re: servers full potential / FT searches locking tablesMichael Dykman28 Aug
        • Re: servers full potential / FT searches locking tablesJustin28 Aug
        • Re: servers full potential / FT searches locking tablesKen Peng29 Aug
          • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
            • Re: servers full potential / FT searches locking tablesKen Peng30 Aug
              • Re: servers full potential / FT searches locking tablesMichael Dykman30 Aug
        • Re: servers full potential / FT searches locking tablesJustin1 Sep
          • Re: servers full potential / FT searches locking tablesJustin3 Sep
            • Re: servers full potential / FT searches locking tablesBaron Schwartz3 Sep
              • Re: servers full potential / FT searches locking tablesJustin4 Sep
                • Memory Issue would someone confirmJustin4 Sep
                  • Re: Memory Issue would someone confirmJustin4 Sep