Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a website running on 2 servers: 1 database server, 1 webserver. Our understanding was that we had 4GB RAM on the webserver and 8GB RAM on the database server.

The site is database intensive with many queries on each page.

Side note, we've recently found out the database server is running 32-bit, which we think means the full 8GB of RAM isn't able to be utilized. So, let's assume 4 GB is all that is on the database server.

The site isn't performing optimally and we're trying to understand how to tweak it.

It is currently close to peak traffic and this is what our server loads/memory looks like:

Our webserver: Server load 0.49 (4 CPUs) Memory Used 59.72% (2,471,180 of 4,137,968) Swap Used 0.01% (120 of 2,096,440)

Our database server: Server load 2.8 (8 CPUs) Memory Used 10.76% (893,004 of 8,300,376) Swap Used 0.01% (116 of 2,096,440)

Here's the question(s):

Does it indicate some inefficiencies that our database server is running at a 2.8 load, but memory is only at 10% (even if only 4GB of RAM, this is still only 20%)?

If so, is this something we should be addressing in the my.cnf file?

What is a good way to think about memory? Is it better to have 'memory used' be higher, indicating that it is utilizing the resources or is it better to be lower? Does the very low 10% indicate efficiency or lack of efficiency?

Thanks in advance as always for any insight.

share|improve this question
    
Try the tool "iostat" and see if you have io waits. And post here the output. Maybe we can find other problems. –  Raffael Luthiger Mar 2 '12 at 22:29
    
If your running 32bit, wouldn't the DB process be limited to 2G of addressable memory? –  David Mar 3 '12 at 3:35

3 Answers 3

up vote 0 down vote accepted

A load of 2.8 on an 8 CPU server isn't really problematic; A load above 8 is though.

With any database, you will want at least all of your indexes to fit in the RAM, as well as have some more available for other MySQL tasks and caching frequently accessed data.

You might need to increase some values in your my.cnf, depending on whether you use InnoDB tables, MyISAM tables, or both kinds.

You really shouldn't be afraid of letting MySQL eat more RAM if it needs to, after all it is a dedicated database server.

The following query should give you the index size for a given database.

SELECT CONCAT( ROUND( SUM( index_length ) / ( 1024 *1024 ) , 2 ) ,  ' MB' ) AS 'Index Size' FROM information_schema.TABLES WHERE table_schema LIKE  'dbname'
share|improve this answer
    
Would you agree then, that having the 10% memory usage is indicative of inefficient setup in the my.cnf file? –  Kevin Mar 2 '12 at 20:25
    
Not necessarily; If you only have 100MB of indexes, that would mean around 300MB left for MySQL to use for other purposes, assuming MySQL is limited to use around 10% of your 4GB of RAM, so you'll need to adjust accordingly. –  gekkz Mar 2 '12 at 20:32
    
+1 for SELECT CONCAT( ROUND( SUM( index_length ) / ( 1024 *1024 ) , 2 ) , ' MB' ) AS 'Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'dbname' –  InChargeOfIT Mar 2 '12 at 22:33
    
Thanks for the very helpful query. Our total indexes are about 5.5 gigs on all our databases –  Kevin Mar 2 '12 at 23:47

This does not directly answer your question, but if you are not out of ram (swapping) and your load averages are not bad (they do not appear to be), then your issues are likely at the application level and not hardware related (barring any other hardware issues such as iSCSI, NAS, networking issues)

Have you done thorough database optimizations? How long are your queries taking to run?

You can figure this out by monitoring which queries are run the most, logging slow-queries (queries that take over 2 seconds for example), and using copious EXPLAIN statements to see your queries execution plans. The point is that RAM is not going to help you much if your queries are doing full table scans over large amounts of unordered data instead of just searching an index loaded in ram.

You can also help things by taking the query load off of the server from the front end as well .. using memcached for example.

There are tons of resources on the web for optimizing databases, adding indexes, etc.

Hope this helps and good luck!

share|improve this answer

[..] let's assume 4 GB is all that is on the database server [..] Memory Used 10.76% (893,004 of 8,300,376) [..]

You apparently run a 32Bit Bigmem-Kernel that addresses the full 8GB.

[..] database server is running at a 2.8 load

Did you check, if the load on your DB server results from I/O waits? Looks to me that your system is powerful enough regarding CPUs and RAM, but may suffer from a slow storage subsystem.

What does top show you for the %wa parameter in the CPU line?

share|improve this answer
    
Sorry, that's over my head right now. What is the best way to find that information? –  Kevin Mar 2 '12 at 23:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.