Re: What is best hardware for server performance
On Tue, Oct 21, 2003 at 08:59:31PM -0600, Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. So MySQL is using 99% of the CPU? Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? If the CPU is truly the bottleneck and you can't improve the performance by tweaking queries and settings, then faster CPUs will help. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). It's hard to say. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 39 days, processed 1,493,160,737 queries (439/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
Well I probably can tweak the queries, but there are a LOT of them. It is for http://www.ecommstats.com so we get a TON of requests that have to processed with probably ~20 queries (selects, inserts, updates), then there is a maintenance program that runs every few minutes to clean things up and calculate some things which does several more queries and goes through to processe data. Most of the time it's ok, and it can handle it, but during some peak times, requests queue up and as long as there are requests in the queue, it's pinned. So I'm just trying to figure out how to best handle it. Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? There is maybe 1 LIKE query and I'm sure that's not causing any issues. Travis Jeremy Zawodny wrote: On Tue, Oct 21, 2003 at 08:59:31PM -0600, Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. So MySQL is using 99% of the CPU? Any idea why? Are you doing too many table scans? Too much computation in the queries? Lots of LIKE %foo% queries? If the CPU is truly the bottleneck and you can't improve the performance by tweaking queries and settings, then faster CPUs will help. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). It's hard to say. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
I have several queries that are taking at least a full minute to process... From a web page, user submits parameters to a perl program which performs multiple queries, then spits the info out formatted for the web. Does anyone have a quick dirty method of locating a bottle neck slowing these queries down? I am hitting multiple tables within the same database, all through the same connection. Ie perform Q1, store results, perform Q2, store results, etc. I am still fairly new to MySQL. I am currently running 4.1 for some advanced features (GROUP_CONCAT). Any/all suggestions are helpful. Thanks Patrick Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
Would innodb tables help? Travis Daniel Kasak wrote: Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). Travis Faster more CPUs then. If the CPU usage were hovering around, say, 75%, you could assume you have an I/O issue that you could solve by upgrading to SCSI. But if the CPU is at 99% while MySQL is running a query, upgrading your CPU(s) is the only hardware upgrade path. You should also check, of course, that MySQL has indexes in the right fields, and that you're queries are linking on numeric fields ( as opposed to character fields ). Also, check that you have the query cache enabled. It _really_ speeds things up. And maybe post back with some specs and table defs and queries and the output of: describe query sql text goes here -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is best hardware for server performance
So I think these are the only options, but if someone has another idea I'd be glad to hear it. Our current server just isn't handling the load anymore so, it has single processor Option 1: Single server with dual processor and change from IDE to SCSI Option 2: One server with mysql on it, a second one to handle application (collecting and reporting on data) Which do you think would be better? Travis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
Travis Reeder wrote: So I think these are the only options, but if someone has another idea I'd be glad to hear it. Our current server just isn't handling the load anymore so, it has single processor Option 1: Single server with dual processor and change from IDE to SCSI Option 2: One server with mysql on it, a second one to handle application (collecting and reporting on data) Which do you think would be better? Travis I would do some more benchmarking to find out where your bottleneck is. You don't give any hardware specs, so it's hard to say... Run 'top' and see how much CPU / memory is being used by each ( mysql and 'application' ). If you CPU usage isn't high, maybe you would benefit from adding some more RAM. MySQL will run a LOT faster if all the tables can fit in RAM. Also check out the options in my.cnf to see that you're actually using your resources. Maybe MySQL is limited by options in here? Personally, I would go with a dual CPU system with PLENTY of RAM, and some SATA disks. But as I said, you may get away with just giving it more RAM. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). Travis Daniel Kasak wrote: Travis Reeder wrote: So I think these are the only options, but if someone has another idea I'd be glad to hear it. Our current server just isn't handling the load anymore so, it has single processor Option 1: Single server with dual processor and change from IDE to SCSI Option 2: One server with mysql on it, a second one to handle application (collecting and reporting on data) Which do you think would be better? Travis I would do some more benchmarking to find out where your bottleneck is. You don't give any hardware specs, so it's hard to say... Run 'top' and see how much CPU / memory is being used by each ( mysql and 'application' ). If you CPU usage isn't high, maybe you would benefit from adding some more RAM. MySQL will run a LOT faster if all the tables can fit in RAM. Also check out the options in my.cnf to see that you're actually using your resources. Maybe MySQL is limited by options in here? Personally, I would go with a dual CPU system with PLENTY of RAM, and some SATA disks. But as I said, you may get away with just giving it more RAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is best hardware for server performance
Travis Reeder wrote: It seems mostly to be mysql pinned, not the app. like 99% mysql until all data is processed and keeps going up when data coming in is more than can be processed. What could I change in my.cnf to get better performance? I just have a default mysql install (4.0). Travis Faster more CPUs then. If the CPU usage were hovering around, say, 75%, you could assume you have an I/O issue that you could solve by upgrading to SCSI. But if the CPU is at 99% while MySQL is running a query, upgrading your CPU(s) is the only hardware upgrade path. You should also check, of course, that MySQL has indexes in the right fields, and that you're queries are linking on numeric fields ( as opposed to character fields ). Also, check that you have the query cache enabled. It _really_ speeds things up. And maybe post back with some specs and table defs and queries and the output of: describe query sql text goes here -- Daniel Kasak IT Developer * NUS Consulting Group* Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
