By dunklea on

Hi there, I am currently trying to troubleshoot some performance issues I am having with my site, namely MySQL issues.

My website is http://www.gooverseas.com. It is currently in the test phase but feel free to go take a look. One thing you might notice is the slow initial load times. It varies but sometimes it can take between 3 to 12 seconds to load a single page. Before the site goes live I need to configure my server to consistently serve fast and stable load times.

Through my own testing and analysis I think the problem is mainly related to memory. I have plenty of bandwidth and my CPU consumption is low. This leaves memory.

The Facts:
I am currently running on SliceHost with 256MB of dedicated memory and 512MB of swap. Using the free -m command line I currently see this:
total used free shared buffers cached
Mem: 256 238 17 0 1 53
-/+ buffers/cache: 183 72
Swap: 511 47 464

On other days though I have seen even less free memory and more swap being used.

I have put a lot of effort into configuring my server settings to get the most of out my resources. I have edited my my.cnf file settings to look like this:

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M

My apache.conf Settings:

Timeout 45
KeepAlive On
KeepAliveTimeout 10
MaxKeepAliveRequests 200


StartServers 5
MinSpareServers 10
MaxSpareServers 20
MaxClients 75
MaxRequestsPerChild 2500

Within Drupal I have enabled all caching features under the Performance menu.

Other things to consider: I am also in the process of installing Boost, Authcache and Cache Router to improve caching performance. Are there any other modules I should consider installing?

Thanks for taking a look and offering any comments you have. The issue might be simple needed more memory, but I also want to be sure that I am getting the most out of my resources before I do so.

Thanks again!

⋅ Categories: Drupal 6.x

Comments

seanray’s picture

seanray commented

As you have known the issue is related to memory, why not add more memory to it? 256M memory in a dedicated server for Drupal is really a low configuration.

manogolf’s picture

manogolf commented

I arrived here searching for the same solution. What are optimal settings for mysql when limited to 256mb of RAM? Guess I'll continue my search but offer the following before heading off.

The more caching you employ the less cache is available. Caching tools are great but can quickly lead to diminishing returns. If not careful mysql will be in a constant battle with php over available cache. It's a fine line to be sure.

I'll be watching for tried and proven tips. offerings, etc. Meanwhile I'm watching top and mem is digging into swap again :(

Seems the REAL solution is somewhere around four nodes, for my host that equals 1024MB memory. Four times the money I want to spend every month, but what to do? I'm not going back to shared hosting.

manogolf’s picture

manogolf commented

I found a post with recommendations for optimizing one node, alas. Be sure to make backups of your apache.conf and my.cnf in case things don't work out so well. Modify as needed (file paths, etc.).

Apache Config.

Timeout 200
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 3
MinSpareServers 5
MaxSpareServers 15
StartServers 5
MaxClients 20
MaxRequestsPerChild 2000
HostnameLookups Off

MySQL Config.

[client]
port                    = 3306
socket                  = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket                  = /var/run/mysqld/mysqld.sock
nice                    = 0

[mysql]
no-auto-rehash
safe-updates

[mysqld]
skip-locking
skip-innodb
skip-bdb
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
bind-address = 127.0.0.1
max_connections = 400

key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 5
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
thread_stack = 1M
read_rnd_buffer_size = 1M
net_buffer_length = 1M
thread_stack = 1M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

I've only started monitoring these settings and haven't gathered enough data yet to see if they help.

Any feedback is surely welcome!

Jerry