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
