Your welcome Alexander! OK the value for table_cache is 8572 * 3 = 25716 file descriptors, how big is kern.maxfiles and kern.maxfilesproc? These need to be set above 26000 to allow all those tables to be opened! I know I have missed this sometime, or not reset them after a kernel compile.
Also you don't need 4.0.1 for innodb tables, they work fine on 3.23.49 and LINUX_THREADS option from the ports package I think is only available for 3.23.xx versions. The only problems I have had with 4.0.1 have been self inflicted. There are some bugs in some of the newer features, 4.0.2 should be out soon. I have been kicking around the idea of using 4.0.1 in production as none of the bugs so far really affect my application. You can see the online change log for a list of changes and fixes since 4.0.1 at http://www.mysql.com/doc/N/e/News-4.0.2.html Good Luck, Ken ----- Original Message ----- From: "Varshavchick Alexander" <[EMAIL PROTECTED]> To: "Ken Menzel" <[EMAIL PROTECTED]> Cc: "Simon Green" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, February 19, 2002 11:31 AM Subject: Re: Mysql performance question > Thanks a lot for the advices. The value for table_cache is 8572, and I'm > indeed accessing a lot of tables. So as I gather it the main effect should > be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS > and using innodb tables, true? Now what can you say about reliability > issue, aren't these products currently in the development stage and can > they be used for the production server? > > > Alexander Varshavchick, Metrocom Joint Stock Company > Phone: (812)118-3322, 118-3115(fax) > > > On Tue, 19 Feb 2002, Ken Menzel wrote: > > > Date: Tue, 19 Feb 2002 10:07:22 -0500 > > From: Ken Menzel <[EMAIL PROTECTED]> > > To: Varshavchick Alexander <[EMAIL PROTECTED]>, > > Simon Green <[EMAIL PROTECTED]> > > Cc: [EMAIL PROTECTED] > > Subject: Re: Mysql performance question > > > > Hi Simon, > > A couple of things, unless you have compiled WITH_LINUX_THREADS > > from the /usr/ports/databases then adding more processors will be > > unlikely to help. The native threads lib on FreeBSD runs a threaded > > app on 1 processor currently, use the LINUX_THREADS option in the > > ports to get around this if you would like to add more processors. > > Do you have softupdates on also? Have you read 'man tuning'? Are you > > accessing alot of tables/DBs? If yes what is your table_cache value? > > What is in 'SHOW STATUS' and 'SHOW VARIABLES'? > > > > Also MySQL 4.0.1 has a query cache that is incredible! It works > > great. 6000 queries per minute is pretty good performance. Have you > > looked at using heap tables where possible? > > > > Another suggestion if everything is hitting the same table/database > > have you tried innodb tables? They work very well under high loads. > > I would study all the 'SHOW STATUS' output and see if you can spot > > anything in the manual page for each of the variables. > > Best of Luck, > > Ken > > ----- Original Message ----- > > From: "Varshavchick Alexander" <[EMAIL PROTECTED]> > > To: "Simon Green" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Tuesday, February 19, 2002 8:30 AM > > Subject: RE: Mysql performance question > > > > > > > Here is the hardware: > > > > > > 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 10000 rpm > > 18.2GB, > > > Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, > > > > > > FreeBSD says when booting: > > > > > > ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device > > > > > > Tell me please if any other info is needed. > > > > > > Alexander Varshavchick, Metrocom Joint Stock Company > > > Phone: (812)118-3322, 118-3115(fax) > > > > > > > > > On Tue, 19 Feb 2002, Simon Green wrote: > > > > > > > Date: Tue, 19 Feb 2002 13:22:04 -0000 > > > > From: Simon Green <[EMAIL PROTECTED]> > > > > To: 'Varshavchick Alexander' <[EMAIL PROTECTED]>, > > [EMAIL PROTECTED] > > > > Subject: RE: Mysql performance question > > > > > > > > What disk drive have you got? > > > > We have found that this can help. > > > > > > > > Simon > > > > > > > > -----Original Message----- > > > > From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] > > > > Sent: 19 February 2002 13:14 > > > > To: [EMAIL PROTECTED] > > > > Subject: Mysql performance question > > > > > > > > > > > > Hi people, > > > > > > > > May be anybody can advice from the personal experience tweeking > > which > > > > options both in the mysql configuration and server hardware can > > help in > > > > increasing mysql performance speed? As it is now, mysql is > > configured to > > > > occupy about 600M RAM, and queries per second avg goes up to about > > 100, > > > > yet under the top load it seems to be not enough. Here are the > > config > > > > options: > > > > > > > > set-variable = key_buffer=512M > > > > set-variable = max_allowed_packet=1M > > > > set-variable = table_cache=512 > > > > set-variable = sort_buffer=8M > > > > set-variable = record_buffer=1M > > > > set-variable = myisam_sort_buffer_size=64M > > > > set-variable = thread_cache=16 > > > > set-variable = max_write_lock_count=10 > > > > set-variable = thread_concurrency=8 > > > > > > > > The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 > > platform, > > > > with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 > > system. > > > > > > > > All mysql queries are rewritten so the INSERT/UPDATE's has > > > > been reduced or replaced by DELAYED as much as possible, to ease > > > > mysql locking. The mysql tables are indexed, all documentation > > from the > > > > main mysql site having been studied. And under the top load the > > mysql > > > > starts locking anyways. > > > > > > > > So what can be the best ways of helping it: > > > > > > > > - Changing mysql options, may be increasing key_buffer some more > > or what > > > > else? > > > > > > > > - Adding 2 more CPU's to the server (it can bear up to 4 CPU); > > > > > > > > - May be there can be some other ways? > > > > > > > > Thanks in advance! > > > > > > > > Alexander Varshavchick, Metrocom Joint Stock Company > > > > Phone: (812)118-3322, 118-3115(fax) > > > > > > > > > > > > > > > > > > > > > > > > > > -------------------------------------------------------------------- > > - > > > > Before posting, please check: > > > > http://www.mysql.com/manual.php (the manual) > > > > http://lists.mysql.com/ (the list archive) > > > > > > > > To request this thread, e-mail > > <[EMAIL PROTECTED]> > > > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > > -------------------------------------------------------------------- > > - > > > Before posting, please check: > > > http://www.mysql.com/manual.php (the manual) > > > http://lists.mysql.com/ (the list archive) > > > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > > To unsubscribe, e-mail > > <[EMAIL PROTECTED]> > > > Trouble unsubscribing? Try: > > http://lists.mysql.com/php/unsubscribe.php > > > > > > > > > > > > -------------------------------------------------------------------- - > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > -------------------------------------------------------------------- - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
