Hello mike,
Saturday, November 29, 2003, 11:45:59 PM, you wrote:
m> At 11:14 AM 11/29/2003, you wrote: >>Hello Everybody, >> >>We have a relatively busy e-commerce shop running MySQL 4 + PHP 4. >> >>We have just upgraded to a Pentium 4 - 3ghz , with 1 gb ram because >>our last server was very slow. >> >>On this new server at peak times when we receive in the region of 150 >>to 200 connections at anyone time, our server grinds to a halt. In the >>processes the cpu is being used 99%! In the no peak periods it happily >>chugs along at between 1% - 30% which is fine. The memory is not even >>being used that much during the peak periods. >> >>I have played around with the config with no real noticeable >>differences. >> >>FYI Our database is roughly 25mb with 450 products. >> >>I have tried indexing some tables with no real joy either. >> >>Another related point, in the MySQL documentation it says in the >>"mysqladmin show extended status" that the Select_full_join value should >>be zero mine is very high, what does this mean? >> >>Please help I'm losing hair at a rapid rate! >>
m> Nev, m> 1) Is your MySQL server on the same machine as your web m> server? If so, then split them.
Yes I am considering this.
Also dual processors will speed things up some, perhaps by 30%.
m> 2) Is the problem related to reads or writes to the database?
What's the easiest way to check?
Chuck may be right in that your joins are not using indexes. Use "Explain select ...." to see which indexes are being used for your joins.
If indexes are being used, then investigate:
"Show Status" or "Show Status like key%" to get just some of the lines. Look for Handler_Write which shows the number of times a write was performed Max_Used_Connections, If Opened_Tables is large then increase table cache size. Select_Full_Joins: The number of joins that did not use indexes Select_Scan: Number of joins that did a full scan of the first table Table_Locks_Waited: # of times the operation had to wait to get a table lock
Also look at join_buffer_size which is the size of buffer used in non-index joins. If for some reason you can't use an index in the join, you may have to change this setting. Needless to say, you want to use an index if at all possible when running on a webserver.
Mike
m> 2a) If the problem is with reads, I'd recommend put a Limit 10 or m> 20 on your Select statements so they don't pull too much data at one time. m> Chances are they are only going to look at the first 10 choices anyway. To m> get the next page, increment the page # and increment Offset m> which=(PageNum-1)*RowsPerPage. m> 2b) If the problem is with writing to MyISAM tables have you m> looked at InnoDb for the tables with a heavy # of updates? m> 3) I've also found that 20%-25% of the CPU time is spent loading m> the PHP script from the disk. Every time the PHP page loads, it needs to m> re-read the PHP file(s) from disk. You can speed this up considerably by m> creating a small RAM drive and put your PHP script files on that. Change m> your PHP configuration to look for the RAM disk first. m> 4) You can try a PHP accelerator. There are several free ones out m> there.
Yeah I will try this.
m> 5) You can of course replicate the data to 2 machines and use a
m> load balancer to handle read queries. Updates would be made to one database.
m> 6) If all else fails, shave your head and tell the ladies you are
m> Vin Diesel looking for a new co-star in XXX-2 and you'll forget all about
m> your MySQL problem. <g>
Yes this sounds the best solution, I wish I could forget!
m> Mike
Thanks for the suggestions
-- Best regards, Nev mailto:[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
