Memory Management on Linux Centos MySQL PHP InnoDB

Question

Does this server have enough memory, if so or if not, how can you tell? What changes would you recommend?

I'm looking to get some understanding on how I should be managing memory for a virtual machine LAMP stack or more specifically, how I can be configuring to maximize the memory available. I'm not looking for answers like "you need more memory" or "stop using innodb" or "you need to switch to fastcgi for serving PHP". Without changing any of the stack, I'm looking for answers on how to tell if the server has enough memory, and how to tune its performance. A good rule of thumb would be great like, your swap size should be equal to your system memory (this is just an example of something that would help me I have no clue if that's true).

Some more background information on what is happening, I have servers similar to this that started crashing, and then I have servers running the same stack and web applications with increased memory that have solved the issue. Now, I know that is because they have more memory and can "handle" the load, but I feel like blindly increasing server size without understanding the underlying mechanics is probably not a good way to go. Also, intuitively I feel that a 512MB web server should handle just about anything and I know I've run on much less before.

130201 21:04:27  mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130201 21:04:27  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
130201 21:04:28  InnoDB: Started; log sequence number 0 4945108
130201 21:04:28 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Number of processes running now: 0
130201 21:08:48  mysqld restarted
InnoDB: Error: pthread_create returned 11
130201 21:08:57  mysqld ended

130201 21:26:19  mysqld started
130201 21:26:35  InnoDB: Error: cannot allocate 8404992 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 8154720 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
130201 21:26:58  mysqld ended

I know now that the above was due to a crash, probably because MySQL didn't have enough memory OR because InnoDB took up too much and didn't have enough for the OS to remain functional. Below is a top of one of my typical servers under low activity. I have little idea how to interpret this. For example, is 100mb free okay? How come there are so many apache threads using over 300m of memory and how is that sustainable? How much should my InnoDB buffer pool be? Any help or guidance would be appreciated!

top - 01:56:34 up 3 days,  4:05,  1 user,  load average: 0.00, 0.00, 0.00
Tasks:  74 total,   3 running,  71 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    524288k total,   424172k used,   100116k free,    11680k buffers
Swap:   499992k total,   102684k used,   397308k free,    56552k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                   
26702 apache    15   0  321m  46m 6068 S  0.0  9.1   0:59.08 httpd                                                      
26701 apache    15   0  316m  41m 6312 S  0.0  8.1   0:32.04 httpd                                                      
26705 apache    15   0  313m  30m 6292 S  0.0  5.9   0:31.81 httpd                                                      
26707 apache    15   0  311m  37m 6372 S  0.0  7.3   0:27.49 httpd                                                      
26704 apache    15   0  311m  23m 5820 S  0.0  4.6   0:28.30 httpd                                                      
20799 apache    15   0  310m  35m 6488 S  0.0  7.0   0:27.43 httpd                                                      
26703 apache    15   0  307m  32m 6240 S  0.0  6.4   0:27.78 httpd                                                      
11123 apache    15   0  307m  30m 5984 S  0.0  5.9   0:10.26 httpd                                                      
26410 apache    15   0  297m  21m 5704 S  0.0  4.3   0:17.26 httpd                                                      
 7871 apache    15   0  296m  23m 5616 S  0.0  4.6   0:09.18 httpd                                                      
26708 apache    15   0  296m  20m 5616 S  0.0  4.1   0:25.38 httpd                                                      
26706 apache    15   0  295m  22m 5636 S  0.0  4.4   0:25.78 httpd                                                      
 1332 root      18   0  279m 5452 3632 R  0.0  1.0   0:00.23 httpd                                                      
 1305 mysql     15   0  195m  10m 3836 S  0.0  2.0   0:26.57 mysqld                                                     
10551 root      17   0 99.7m 1196  900 S  0.0  0.2   0:00.00 crond                                                      
10595 root      17   0 99.7m 1196  900 S  0.0  0.2   0:00.00 crond                                                      
10651 root      17   0 99.7m 1196  900 S  0.0  0.2   0:00.00 crond                                                      
10695 root      17   0 99.7m 1196  900 S  0.0  0.2   0:00.00 crond                                                      
10481 root      16   0 91068 3284 2564 S  0.0  0.6   0:00.00 sshd                                                       
10485 assistrx  15   0 91068 1744  976 S  0.0  0.3   0:00.00 sshd                                                       
 5739 root      15   0 90144 3268 2556 S  0.0  0.6   0:00.00 sshd                                                       
 5741 assistrx  15   0 90144 1720  980 S  0.0  0.3   0:00.00 sshd                                                       
 9687 root      15   0 90144 3388 2652 R  0.0  0.6   0:00.01 sshd                                                       
 1340 root      15   0 74848  700  592 S  0.0  0.1   0:00.04 crond                                                      
 9690 root      15   0 66104 1600 1180 S  0.0  0.3   0:00.00 bash                                                       
 1132 root      18   0 65968 1176 1032 S  0.0  0.2   0:01.48 xe-daemon                                                                                       

Answer 1

MySQL DB tuning can be daunting, but it’s doable. Check out this answer from RolandoMySQLDBA. His advice on how to measure the InnoDB buffer pool is perfect & I recommend the advice on turing on innodb_file_per_table because you really have never known what hell is like until you had to deal with one huge InnoDB crash file clogging up your system. The following queries come straight from RolandoMySQLDBA’s answer:

To get an idea of how big to make the innodb buffer pool in KB, run this query:

SELECT SUM(data_length+index_length)/power(1024,1) IBPSize_KB
FROM information_schema.tables WHERE engine='InnoDB';

Here it is in MB

SELECT SUM(data_length+index_length)/power(1024,2) IBPSize_MB
FROM information_schema.tables WHERE engine='InnoDB';

Here it is in GB

SELECT SUM(data_length+index_length)/power(1024,3) IBPSize_GB
FROM information_schema.tables WHERE engine='InnoDB';

I also use the tuning-primer script discussed here. Has truly helped me wrangle MySQL headaches. You need to have the DB up & running & active for at least 2 days for it’s data to be useful, so I run this on production servers after they have had at least 2 days of real world traffic.

Answer 2

Before we get into mysql tuning, you first need to check if memory allocation is failing due to ulimits or some other OS setting.

Can you please paste the outputs of the following commands -

1. ulimit -a
2. free -m

Those commands will print information about the memory limits set on your OS, and your physical memory specs.