Hi Ken, Here are the values:
kern.maxfiles: 24616 kern.maxfilesperproc: 22154 They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Luck 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 15:14:39 -0500 > From: Ken Menzel <[EMAIL PROTECTED]> > To: Varshavchick Alexander <[EMAIL PROTECTED]> > Cc: [EMAIL PROTECTED] > Subject: Re: Mysql performance question > > 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 > --------------------------------------------------------------------- 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
