> >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' ?
Yes. Only SELECTs are running. Early on in the month the server is mostly idle, as there is less data in the tables than at the end of the month. During this idle time I am able to do all of the tests that I need to do, including restricting all outside access to ensure only my SELECTs are running. > >- 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). Right. I have installed InnoDB on my development desktop machine (a Sparc 20 w/ Solaris 2.6) and I am happy with its speed. However, until just a week or so again InnoDB did not support files larger than 4 gig, which was a problem. We will be switching to InnoDB once we get this CPU problem sorted. One major drawback to InnoDB that I have noticed is the limited database size. With a MyISAM table, it grows as needed. Meaning that during the first part of the month, this table is really small, which allows for data processing to be really really fast. In the order of 3 orders of magnitude faster than the end of the month. With InnoDB, your database size is set, and if you have 3 megs of data in it, or 3 gigs, it will take the same amount of time to run. Granted: if there ARE 3 gigs, it will run a lot faster than 3 gigs of MyISAM database tables. >- 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). Yes, the docs say to set this to be double that of the CPUs. The my-huge.cnf file that comes with MySQL has this set to 8 by default. My current my.cnf file uses all of these values, doubled. So my thread-concurrency is currently set to 16. I will experiment more with this value, and see what results it yields by just modify it alone. I appreciate the help, it looks like I will just need to start experimenting with individual values and see what gives me the best results. Thanks again -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Aaron Williams [EMAIL PROTECTED] Black Raven.com http://www.backraven.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
