Hello I have a server: # 2x Intel Xeon Quad 5405 # 8192 MB FB DDR2 RAM # 500 GB 7.200 RPM And with these statistics is loading very slow: top top - 22:18:51 up 18:38, 1 user, load average: 24.64, 25.30, 24.41 Tasks: 237 total, 25 running, 197 sleeping, 4 stopped, 11 zombie Cpu(s): 58.3%us, 19.8%sy, 0.0%ni, 20.2%id, 0.0%wa, 0.0%hi, 1.6%si, 0.0%st Mem: 8181620k total, 4966048k used, 3215572k free, 472184k buffers Swap: 16386260k total, 0k used, 16386260k free, 2203440k cached 22189 mysql 15 0 1278m 628m 3432 S 227.8 7.9 126:10.09 mysqld 16877 nobody 18 0 1085m 183m 2140 S 20.8 2.3 1:01.34 httpd 17030 nobody 18 0 899m 186m 2432 S 16.3 2.3 1:00.42 httpd 16854 nobody 18 0 1022m 182m 2124 S 11.1 2.3 0:58.48 httpd 20696 amorem 15 0 0 0 0 Z 9.4 0.0 0:00.29 php <defunct> mysql \s Threads: 30 Questions: 516507 Slow queries: 0 Opens: 546 Flush tables: 1 Open tables: 503 Queries per second avg: 142.054 netstat -apn | grep :80 |wc -l 2492 Can anyone recomand me a solution? Extra ram? Where i sthe problem? Maybe i am under attack? Thank you
Your not using Swap, so more RAM isn't going to do anything. Is this just a LAMP web server? If so, what sort of sites are running on it? Are you using a caching program? How much have you optimized the MySQL installation (my.cnf, query, table, and database optimization)?
I got the server with Centos on it and i was install the Cpanel. It runs only a invision forum. For optimize i am using this my.cnf: [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking skip-innodb skip-bdb key_buffer_size = 512M max_allowed_packet = 8M sort_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 32M net_buffer_length = 8K thread_stack = 64K thread_cache_size = 8 table_cache = 768 max_connections = 300 query_cache_size = 32M query_cache_limit = 32M tmp_table_size = 45M max_heap_table_size = 45M thread_concurrency = 4 thread_stack = 64K join_buffer_size = 8 log-slow-queries = /var/log/mysql/mysql-slow.log server-id = 1 skip-networking [mysqldump] quick max_allowed_packet = 1M [mysql] no-auto-rehash safe-updates [isamchk] key_buffer = 10M sort_buffer_size = 2M [myisamchk] key_buffer = 10M sort_buffer_size = 2M Code (markup):
If you want to debug the slow queries better try using these: Replace: log-slow-queries = /var/log/mysql/mysql-slow.log With: log-queries-not-using-indexes log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 Don't leave these going permanently. They eat up resources. Also check the mysql error log and make sure the slow query log is writable. Should be owned by mysql with mysql as the group as well. I use Innodb almost exclusively so I don't have a good answer if something needs to be adjusted in the my.cnf file. Also, without seeing the tables and the usage, it would be a complete crap shoot anyway.
Just looking at the specification, your server hardware seems odd. Why would you need all the processing power of dual quad-core CPUs, and lots of RAM, then throttle it's potential with a single 7200 rpm SATA drive? The Disk IO is NEVER going to be able to cope with the processing potential of this server and the disk sub-system will likely always be a bottle-neck. It's a very strangely configured server and your hosting company should know better. Maybe it's because CPUs & memory are cheap compared to good hardware controllers and SAS drives and they can pretend they are providing you with a "powerful" server just because it has over-specified CPUs in comparison to the rest of the hardware. You'd probably be better off with a single dual or quad-core CPU, similar amounts of RAM, and then invest the saving is a decent disk sub-system running, say RAID 10, with a good hardware RAID controller with battery back-up and lots of on-board memory. If you can swing it, go for SAS disks running at 15K. Databases can put a lot of stress on a server and a fast disk system is critical if the database is going to be heavily used, but they don't put that much strain on the CPU. All the time is spent sending and collecting data from the databaes and CPU power is largely irrelevant. One of our shared MySQL database servers, operating around 100 busy databases uses a single P4 CPU. The sites using this server are extremely fast and the server load is small, but the big difference is that this server has a very fast disk system so the major performance bottleneck is taken care of (we will be upgrading it - not for performance but due to the age - we just haven't got round to it yet).
Thanks for your reply. Is a sata 2 raid solution faster than one single sas disk? And which raid do you recommend me? Raid 1,5,10?
RAID 10 is best for databases in just about every case. It's expensive though, so RAID 5 on a solid hardware controller is usually acceptable for lower budget / lower usage setups. If you're using a 1U server, you may as well go with RAID 10 since you can probably only put 4 drives in there anyway. It's very difficult to compare SATA to SAS from a perspective like this. There are tons of things to consider, like the hard drive controller, disk sizes, speed, the actual interface being used, the file storage system, the block size, etc... Generally SAS drives can handle a much higher throughput than SATA drives. A lot of this is dependant on the rotational speed, and the fact that SAS drives are usually run from an independent controller. Another thing to consider is that SAS drives are considerably more reliable than the best SATA drive out there. As to the actual question, a 2 disk RAID 1 array should be able to beat a single SAS drive on reads, but would probably get stomped on writes. Are you using a hard drive controller, or just connecting directly to the motherboard?