Aaron Williams wrote: > > Hello again list. I sent this message yesterday and wasn't able to > get an answer. Just curious to see if any of the gurus out there care > to share some insight on a mid-level MySQL setup, and can offer some > starting points for learning how to troubleshoot the same. > > | Begin message: > > I have a dedicated server (a Sun E4500 CPUs 4x400mhz w/ 4 gigs of ram > and 8gigs of swap, which is connected to a 100+gig dothill RAID with > its own controller. The only function of this server is to run MySQL. > The tables run upwards of around 13-15 gigs each. The nature of the > data forces me to search the entire contents (looking for unique > values) around 3000 times a day. The server is running Solaris 2.8, > with MySQL 3.9.43 compiled in 64bit mode. All tables are currently > MyISAM tables. (Will probably be going with InnoDB, but large file > support was -just- released). > > All of the documents (manuals, online forums, etc) I have looked > through include the "huge" my.cnf configuration, but it isn't large > enough for my hardware. So, in my limited knowledge of my.cnf > settings, here is what I have currently: > > set-variable = key_buffer=3096M > set-variable = max_allowed_packet=1M > set-variable = table_cache=512 > set-variable = sort_buffer=1024M > set-variable = record_buffer=512M > set-variable = thread_cache=32 > # Try number of CPU's*2 for thread_concurrency > set-variable = thread_concurrency=16 > set-variable = myisam_sort_buffer_size=64M > > Can anyone show me a .cnf that would offer more performance? > > Also, I can't seem to get MySQL to use any of the other processors. I > know that Solaris 2.8 has SMB and threads in it, as running dnetc > tests show all four processors working together. However, running > heavy CPU load MySQL selects (eg: select count(distinct(foo)) ) top > shows a steady 75% idle, with a running iostat of near nothing, so it > isn't disk bound. Running multiple intenses of the queries shows the > same results, 75% idle, meaning only one CPU is being used. > > My installation of MySQL was compiled locally from source, with the > following configuration options: > > ./configure --without-innodb --without-bench --without-debug > > Anything with that configuration that would be limiting me to just one CPU? > > Any and all advice is welcomed and appreciated. > > -- > > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Aaron Williams [EMAIL PROTECTED] > Black Raven.com http://www.backraven.com > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >
Hi, If you run a single query, MySQL will use only one processor. MySQL multithreading allows you to run multiple queries in parallel, one query per thread. Have you identified your bottleneck (CPU, filesystem I/O, swapping) ? What does 'mysqladmin extended-status' tell you ? (Allocating 3Gb of key_buffer only makes sense if you really need them). Is your query optimized ? What does EXPLAIN tell you ? It is hard to help you without any details since optimising my.cnf parameters not only depend your hardware configuration but also on your database usage pattern. Regards -- Joseph Bueno NetClub/Trader.com --------------------------------------------------------------------- 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
