Someone help me out here. My VPS is running with 512Mb RAM and I am trying to optimize it as much as I can. So far I have installed eaccelerator (yeah!!) which seems to work fine.
Now I am trying to optimize my system's MySQL. Like all true-geeks, I jump at the slightest chance to tweak my budget VPS to squeeze out the last ounce of speed from it. Anyway....
...I went to this website (many others on the Net like it):
http://www.hightekhosting.com.au/myaccount/knowledgebase.php?action=displayarticle&id=154
where it gave the code for my.conf that optimises the SQL queries.
I proceeded to modify my VPS's (Lunix) /etc/my.cnf.
The result is that when I login to Plesk, I get this message:
ERROR: PleskMainDBException
MySQL query failed: Unknown table engine 'InnoDB'
0: common_func.php3:155
db_query(string 'select param, val from misc')
1: common_func.php3:585
get_param(string 'mysql41_compatible')
2: common_func.php3:482
db_set_names()
3: common_func.php3:461
db_connect_real(string 'localhost', string 'admin', string '**********', string 'psa')
4: common_func.php3:441
db_connect()
5: auth.php3:121
The good news is that I had earlier (wisely of course) backedup the my.cnf file. So by restoring the original file, and by restarting my VPS container, my Plesk will work fine. I've done it. So what's my problem then? By restoring my original my.cnf file, I'm back to square one. But I want to OPTIMIZE my MySQL and the link above says it's a tried-and-tested code to optimize MySQL. But when I try it, my Plesk doesn't work. I want to have my Plesk work (can login) and I want to ENJOY the benefits of the optimizing code for my MySQL. In other words, I want to have my cake and eat it too. Heh.
What am I doing wrong? If what I am trying to do, doesn't work for me, can someone else suggest a way for me to optimize my MySQL databases so that my Moodle runs faster?
Here's my original my.cnf code (in magenta):
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
#skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-bdb
set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
AND HERE'S THE SUGGESTED MY.CNF CODE FROM
http://www.hightekhosting.com.au/myaccount/knowledgebase.php?action=displayarticle&id=154
(in blue text)
my.cnf configuration for server with 512Mb Ram
For servers with 512Mb of Ram (including VPS), this my.cnf configuration is ready-to-go.
Simply replace your /etc/my.cnf file with this configuration (ensure you backup existing my.cnf first!!)
[mysqld]
#skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 650
key_buffer_size = 144M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 512
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 8
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 48M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 4
[mysqld_safe]
nice = -10
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
These are tried and tested configurations used by Hightek Hosting in many VPS and Dedicated servers.
NOTE: These configurations MAY NOT BE SUITABLE for your application. Hightek Hosting cannot be held responsible for any damage, loss of date, time or use of service by using the above configuration
