Aaron Williams wrote : > > > Joseph Bueno wrote: > > > >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. > > Right, that I understand. Even while running up to 30 queries it pegs the > first CPU at max, and no other CPUs are getting any data to process. > > >Have you identified your bottleneck (CPU, filesystem I/O, swapping) ? > > At this point, it is the CPU. Running a constant iostat -dxz 2 to show disk > usage, it is near zero for most of the time. It will occasionally jump up a > bit briefly, but nothing near what would be causing a bottleneck. > > >What does 'mysqladmin extended-status' tell you ? (Allocating 3Gb of > >key_buffer only makes sense if you really need them). > > Which part of the extended-status would you like to see? All of it? > > In response to Bill Adams and to this question: nearly the entire table is > indexed. The purpose of this table is to store RADIUS accounting records. > The data is inserted into the table once per day, and is then bits of the data > are extracted from it in large sections three times per day. The key_buffer, > according to the docs, should be 80% of the physical system memory. I was > a bit conservative and set it to be 75%.. 3 of 4 gigs. As data is > only inserted once, > and nearly all of the use of this table is for SELECTing, we indexed > nearly all of > it for speed reasons. The insert is done by perl scripts called from > cron in the middle > of the night, so speed, at that point, isn't nearly as important as > it is when called by > humans during the day hours. > > >Is your query optimized ? What does EXPLAIN tell you ? > > We just bought this new server last month. Prior to it, we were doing the same > queries on a 2x200mhx with 2gigs of ram, with the same RAID setup. So, as you > can imagine, the queries themselves are extremely optimized. For several months > we tried, and succeeded mostly, at optimizing the scripts and the > queries, instead of > just throwing more hardware at the problem. > > >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. > > Ok, you have the hardware configuration, and I have described somewhat of how > the database pattern of use is above. But, just to recap everything: > > Hardware: > > Sun E4500, 4x400mhz 4 gigs of RAM > DotHill 100+ GIG RAID w/ controller, currently getting 16-18 megs per > second io. > > Database setup: > > MyISAM tables. > Roughly 10-13 gig .MYIs (data) > Roughly 2-3 gig .MYDs (indexes) > > About 1 million rows are inserted once per day in the early hours. > Tables are rolled into new tables at the beginning of each month. > Then, periodically throughout the day SELECTs are run on the data, > for all days from the beginning of the month up through and including > today. As you might imagine, the queries get slower and slower as the > month goes on, which is expected. > > There are two styles of queries done, a select unique (which has > shown to be faster than 'select foo group by foo'.. which goes > against the docs, but has proven faster over and over again). The > second style is used to just search one day. I use temp tables for > this, so it runs really fast already. > > Again: It is not really the queries I am worried about, but are > provided to show the pattern of use. I am very confident with the > actual syntax of the queries. My main concern is the my.cnf and why > MySQL isn't using the other CPUs on the machine, even with many > queries running. > > In response to Bill Adams: > > > InnoDB might help because it does have row-level locking. > > Right, we will definitely be looking at InnoDB now that it supports > file sizes large enough for us. > > > AFAIK, 3.23.x only supports one CPU per thread/select. > > Understandable. MySQL is only going to be able to send one query to > one CPU, no doubt there. Our problem is that even with 30 queries, > MySQL is still only sending them to the first CPU. > > I appreciate the help so far, and will gladly supply any other > information needed to help you narrow down my best options. > > Also, I mentioned above that select unique is faster than a group by. > Another thing I have benchmarked and proven, at least on our setup, > that goes against documentation, is with the Perl DBI. While it may > be off-topic of this forum, I know many use it with MySQL. > fetchrow_array has proven to be faster than fetchrow_arrayref, which, > according to the docs, should be faster. Just thought it interesting. > You find such stuff when squeezing very last ounce of performance > from your software, or at least trying to. > > -- > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > Aaron Williams [EMAIL PROTECTED] > Black Raven.com http://www.backraven.com > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Hi, Here is what I understand: your queries are already very optimized, you have already played with database scheme and application optimisations so there is little chance to find any gain there. Your setup seems to be right according to disc I/O and RAM since all indexes fit in RAM and you are not doing much disc I/O. So, your 4 CPUs should be used and, since it is not the case, the only reasons I can figure out are: - Some of your queries are locking the tables. It seems strange since you specify that only SELECTs are run during the day. Anyway, have you checked that this is not the case with 'mysqladmin processlist' ? - If you do have table locking problems, you should consider switching from MyISAM to a table format that support row level locking as InnoDB (but I have never tried that myself, I am still using MyISAM on our servers). - According to MySQL manual, there is a Solaris specific parameter in my.cnf: 'thread_concurrency'. Have you checked this parameter ? (I have no experience with this parameter since I have not used Solaris lately and I am using MySQL with Linux). Hope this helps -- 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
