Hello everybody,
I'm not so familiar with server and my last admin make my server crashed, so I would like to ask You to help me tweak my server.
Let me start from the begining:

HW:
Athlon64 3700+
2gb memory
sata disk

General usage:
IPB + IPG +ICB wit other small addons:
average 150 users online, peak 220-250,
3k users
70k post,

also on this server I have:
small SMF forum, average 3users online
small 2 wordpresses with only few visits monthly
small Joomla cms - average 20 visits daily
small oscommerce shop - average 100 visits monthly :(

SOftware installed:
Apache 2.2.6
PHP 5.2.4+ eaccelerator 0.9.5.2
mysql 4.1

here are my configs:

my.cnf


What should I do to tweak my server?

Thanks in advance,
BR

YacentY

[mysqld]

default-character-set=latin2

init-connect = "SET NAMES latin2"

language=polish

skip-bdb

#skip-innodb

key_buffer = 192M

#16MB

myisam_sort_buffer_size = 34M

join_buffer_size = 1M

read_buffer_size = 1M

sort_buffer_size = 2M

table_cache = 1528

thread_cache_size = 286

tmp_table_size = 64M

max_allowed_packet = 16M

query_cache_limit = 2M

query_cache_size = 256M

#64M

query_cache_type = 1

query_prealloc_size = 16384

query_alloc_block_size = 16384

tmpdir=/tmp

#myisam-recover



[mysqld_safe]

open_files_limit = 8192


[mysqldump]

quick

max_allowed_packet = 10M


[myisamchk]

key_buffer = 64M

sort_buffer = 64M

read_buffer = 16M

write_buffer = 16M


[client]

default-character-set=latin2

my apache conf - threads section

<IfModule mpm_prefork_module>

	StartServers		 30

	MinSpareServers	  10

	MaxSpareServers	  30

	MaxClients		  150

	MaxRequestsPerChild   0

</IfModule>

key_buffer should be right around 25% of the memory since it's not a dedicated. I would increase it to 256M to start with though. Add to mysql config

read_rnd_buffer_size = 1M

Save the my.cnf and restart mysql.

Make sure your ibf_sessions table is type HEAP. You can change it in phpmyadmin.

these 2 values changed - but all my tables are MyISAM - how to exchange table to HEAP?

thanks in advance

BR
YacentY

alter table ibf_sessions engine=heap

[quote name='bfarber' post='1674036' date='Sep 14 2007, 09:16 AM']key_buffer should be right around 25% of the memory since it's not a dedicated. I would increase it to 256M to start with though. Add to mysql config

read_rnd_buffer_size = 1M

Save the my.cnf and restart mysql.

Make sure your ibf_sessions table is type HEAP. You can change it in phpmyadmin.

how do i do this?

thank you

If you don't know how you are probably best off asking your host to do it. If you are on shared hosting, I wouldn't expect them to make any changes on your behalf. If you have a dedicated, you would edit the file /etc/my.cnf, save, and restart mysql.

[quote name='bfarber' post='1674036' date='Sep 14 2007, 04:16 PM']Make sure your ibf_sessions table is type HEAP. You can change it in phpmyadmin.
I am running on shared server. Can I use it too, or it's recommended only for dedicated servers?

You can use that on a shared server.

Thanks.

Er... mysql server must be restarted then or not?

No, changing the table type does not require mysql to be restarted

OK, now I have this situation:

ibf_sessions table has been switched to HEAP - when I tried to optimize database and export, in both cases I got error message (I wasn't able optimize nor export) and I had to switch ibf_sessions table back to MyIsam. Is it right procedure or can I optimize (export) database without this table?

You should be able to export the table. You can only optimize MyISAM tables with MySQL, so that's normal.

All right ;)


I want to export whole database, all tables togheter. When I export gzip, file is big about 16 MB. When I exported whole database (ibf_sessions table HEAP included) downloading was suddenly aborted around 3,5 MB. I switched that table to MyIsam and gzip was successfuly exported in right size (16 MB).

 

You can only optimize MyISAM tables with MySQL, so that's normal.

 

You should be able to export the table.

Hmm, couldn't say why that would happen, but the table type should not affect ability to export.

OK never mind :)

I switched table to MyIsam, exported whole database and then switched it to Heap again.

Which tables could be switched to Memory for better performance?

ibf_sessions is already there... what about ibf_search_results or ibf_admin_sessions, etc...?

Thanks.

Yes, both of those could. Essentially, any tables that won't get huge and do not hold critical data you can't afford to lose (since they are cleared when mysql restarts).

we tried to change ibf_search_results to HEAP on 3.0.5
it shows

#1163 - The used table type doesn't support BLOB/TEXT columns

any ideas please

please help?

[quote name='bfarber' timestamp='1189782987' post='1674036']

Make sure your ibf_sessions table is type HEAP. You can change it in phpmyadmin.

Is this still valid?

You know for anyone wanting their server optimized, I would save all the heartache and get IPS to optimize your server for you. I had them do this for my dedicated server last week. The price was pretty reasonable and saved me tinkering with stuff I didn't have a clue with.

3DKiwi

Hello ! :)

How did you get IPS to optimize your server ? Is your board hosted by IPS ?

[quote name='3DKiwi' timestamp='1281864768' post='2006058']
stuff I didn't have a clue with.
Can you tell us more, please.

Thank you ! :)

No my server isn't hosted with IPS.

I just got in touch by email and asked them about making my server more reliable. They came back with a price. I agreed and they did it.

3DKiwi

[quote name='3DKiwi' timestamp='1281981510' post='2006889']
No my server isn't hosted with IPS.

I just got in touch by email and asked them about making my server more reliable. They came back with a price. I agreed and they did it.

3DKiwi


may we know for exemple how resonable was this price and wich kind of tweaks were performed ?

[quote name='Leooooo' timestamp='1282007021' post='2007097']
may we know for exemple how resonable was this price and wich kind of tweaks were performed ?


I would be interested in it too :)