0

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.

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

3 Answers 3

0

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'
4
  • Would you agree then, that having the 10% memory usage is indicative of inefficient setup in the my.cnf file?
    – Kevin
    Mar 2, 2012 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, 2012 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' Mar 2, 2012 at 22:33
  • Thanks for the very helpful query. Our total indexes are about 5.5 gigs on all our databases
    – Kevin
    Mar 2, 2012 at 23:47
1

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!

0

[..] 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?

1
  • Sorry, that's over my head right now. What is the best way to find that information?
    – Kevin
    Mar 2, 2012 at 23:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .