Hello All,
I am trying to make my server fast as my clients are complaiing that sites are opening veyr slowly.
See what I am trying to do:
1)mysql optimization
2)apache tunning
3)increasing RAM up to 2GB and no of processors to 4 (Intel XEON 2.8 GHZ)
4)keeping separate servers for mysql(mysql) and http.
5)RAID 5 configuration on server avoiding crash
6) The link between the database server and web server shold be fast(Any suggestions?)
etc etc...
It seems that i did mysql optimization ... RAM is 2gb and no of processors are 4 intel Xeon ..
We have separate servers for database and http ..
Now still we are facing problem of slow web pages and slow server..
Now I want RAID 5 to be configuraed ..does it increase the speed?
And the link between the mysql and apache server ..what we can do for it to make queries fast?
Any othre suggetion from masters in the field?
Any suggestions to make server really fast?...
Please help !!!

Raid 5 is kind of slow compared to the other raid configurations. I personally suggest a 2.6.7 kernel on your dual xeons, it will help with performence a great deal. What is the server os? WHat are loads like?

atul,

before doing upgrades and/or software tuning - you need to find - what is the bottle neck of your server. Play with "top" and with "ps auwx" - it could give you idea what is loading your server. Watch for process making big load and for how many instances of single application running. This give you idea what are the software you need to justify. There are lots of options ... post here ps auwx & top output we could suggest something.

Watch for how many swap been used - will give you idea do you need more RAM.

Check for your HD input/output rate - so you know do you need upgrade HD. Sometime just add one more drive and move mysql bases there will help. Or add backup battery unit to your RAID card and turnon lazy write (this could dramatically imrpove hds performance). And yes, raid5 isnt the fastet raid.

If you want to get separate server for mysql - you need to watch for kind of queries are running at the server. Let say if you have a lot small queries - separate server could make it slower since it will spend more time for connection and passing the data, but if queries are running over huge tables and they process lots of temporary data before get result - separte sql server could improve it. I had both experience for separate mysql server - sometime it does good job sometime not.

Hello Mr.Security and farpost
Thanks !! So u mean RAID 5 isn't good solution for my server !!
I run RH 9 with WHM 9.4 on it....
Upgrading to 2.6 kernel will improve my performance drastically ? ...
Yes farpost !!
I have a separate mysql server !!! why we did so because of very high load on it..
The bottle neck of problem is mysql qureies !!
They are select queries from users to only 2 tables on database !! What exactly the problem is !!!
When users uses select query !! the query seaches the whole table alomost 20000 indexes !! etc and then after finding the proper entry updates that !!!
I am n't programmer !!! but we need to troubleshoot this and also the slow server problem!!
What do u suggest me ?
I need suggestion expert like u!! What to do ?
What not to do?
I want a running site for at leat 5 months non stop !!
What can be done?
If raid 5 not good solutijon then what else for it?
Thanks in advance!!

atul,

i guess you mean 20.000 records (rows) not indexes? 20,000 records DB is quite small one, so either kill the developer or justify mysql... i guess you can't kill him.. so lets play with mysql

I cant write good performance adjusting guide in short time (go thru www.mysql.com , there are should some), but in short:

watch for mysql processes and
mysqladmin show status - could help either (look how many temporary tables it creates and how much of them writes to HD).
increase buffers (key & sort).

check for fields used for searches - creating indexes by this fields could help

take a look into mysql log file - there could be some useful info too.

if mysql performance adjustment will not help - add more RAM.

if it will not help, get RAID card with lots of memory and battery backup and turn on lazy write (as I got, evey table scan ends by record update - so it both lots of reads & writes. Lots of memory could fix read problems and lazy write will fix write problems).

if this not help, setup raid1+0

if this not help, find and kill developer :-)

p.s. 2.6 kernel upgrade will improve but not drastically.
and don't use raid5 for sure - it will slow down your case.

Hello farpost !!
Thank sveyr much ..
Can u just take a look at this query !!!
select srno,siteurl from cash_sites where username!='tekiki' and credits>0 and status=1 and hour_hit
These are the queries to cash_sites in database !! these are real cause of problem on server !!!
U said that "check for fields used for searches - creating indexes by this fields could help"
so u mean we need to add more fields to make search better ... and fast !!!
And those rows number is sorry it is 500000 !! could be more !!
so everytime select query dose the same searched so many !!
Any suggestion !!!

atul,

i would create an index for every field used after "where"- username, credits, status, hour_hit.....
No, dont add more fields - it could just increase table scan time.

also check the fields get updated, if you drop indexes for this fields - it could increase performance.

But i would play with /etc/my.cnf settings first. Increase sort buffer - could help either.

HEllo ,
This mysqld section in my my.cnf file on my server:

port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
set-variable = max_connections=400
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

We have thse setting :
Fedora Core 1 WHM 9.4 CPU inetel Xeon (2.4 Ghz , 4 number)
Mem:

total used free shared buffers cached
Mem: 1031976 1009824 22152 0 108996 683036
-/+ buffers/cache: 217792 814184
Swap: 2096440 1272 2095168
Total: 3128416 1011096 2117320

And disk usage:
Current Disk Usage
Filesystem Size Used Avail Use% Mounted on
/dev/sda6 2.0G 272M 1.7G 15% /
/dev/hda1 74G 33M 70G 1% /backup
/dev/sda1 99M 13M 81M 14% /boot
/dev/sda8 43G 219M 41G 1% /home
none 504M 0 504M 0% /dev/shm
/dev/sda7 1012M 33M 928M 4% /tmp
/dev/sda3 9.9G 2.3G 7.1G 25% /usr
/dev/sda2 9.9G 8.7G 738M 93% /var

What to set sort buffer value for this ? or what do u suggest the variables values set to? Should I enable max_user_connection to limit the perticular user to certain number of connections?
My client also wants me to set RAID on this server .This is only a mysql server!!! What could be the better choice ?
Are my my.cnf values are proper to my 1GB memory..
Any suggestionms !!!

i would increase

sort_buffer_size = 2M
myisam_sort_buffer_size = 128M
read_buffer_size = 4M
query_cache_size= 32M

what is your mysql version?

Hello ,
Server version is 4.0.20
Yes I pointed out something!!!
I am currently using my-large.cnf file as my.cnf file ..
Now I will copy my-huge.cnf file as my.cnf .In that sort_buffer size is 256M .. Let's see whether it works? Should I do that ?
farpost,but what about raid ? I also want to implement raid on this server? Will u help me for that ? I want performance gain in server ?
Please any suggestion !!!

dont just overwrite file (or make my.cnf backup atleast). dont forget you need to restart mysqld after my.cnf changes or learn how to change dynamic variables on the fly..

better play with 4 varables I posted about. change them, restart mysqld , see what happens...

read:

http://dev.mysql.com/doc/mysql/en/MySQL_Optimisation.html

http://dev.mysql.com/doc/mysql/en/Server_system_variables.html

http://dev.mysql.com/doc/mysql/en/Server_parameters.html

about the RAID.

implement either raid1 or raid1+0. raid1+0 requires at least 4 drivers and it will increase performance.

Thanks farpost,
I have read the links, and configured it !!!They are very informative !!! Thanks very much !!!
Again I asked u before !! Dose using
set-variable=max_user_connections= {To some value}..
And limiting the users connetions in one hour , or one day wiill improve perfoemance of server??/
Any suggestion??

max_user_connection deals with MySQL users, it's not about web user. So, dont set it any or you will loose some data.

It limits users connection at one time.

Originally posted by farpost
about the RAID.

implement either raid1 or raid1+0. raid1+0 requires at least 4 drivers and it will increase performance.

Hi, could you please explain to me why Raid1 raises performance?