Keeping the server load cool?

[Whoo]

Recently with the increase of traffic I have noticed that my server load has gone up (especially mySQL).

The reason I'm making this topic is because I want to discuss about some ways to minimize server load, and in my opinion this can be done easily.

Having too much data in your databases isn't good so therefor I have this idea. Having a huge download database isn't good, a good 40k downloads would be enough. As for recent searches it also seems fair enough to limit it at 50k.

Since you only need the newest downloads and the latest searches it is possible to just have the recent 40k downloads and the last 50k searches

Easiest way would be to create a PHP file with some queries:

1*Get the highest ID in the downloads tabel
2*Do a query to remove all the downloads with a lower id than Highest_ID - 40 000
3*Get the highest ID in the search tabel
4*repeat step 2 but then just with the searches tabel

Anyone any comments?

 

[TippiE]

You could tweak /etc/my.cnf aswell

 

[warzone]

perhaps remove the ID's that are too old to work. Like something thats like 2 - 3 years old, why bother keeping it when you know it will out and dead.

 

[JmZ]

well whoo, I recently did some "research" and looked deep into KDDL (the script basically everyone uses). It is really a very, very badly coded script which is the main reason I decided to find why it sucks at server load.

You are right with clearing old rows from your table but that will only count for half (if that) of the high SQL load. The other primary reason for it is just listing recent searches. Now if you cache them, say hourly, it will lower load A LOT.

As for downloads, there is no real need to remove old ones most of the time as they cause the least load (SQL queries used to list them and view them are fine).

 

[UltimA]

Just out of curiosity (If you dont mind) -
What are your server specs?
How many SQL queries are being generated per average pageload?
How many uniques are you receiving a day?

I could try and optimize it for you, if the server is high-spec, and the load is erratic.

 

[Whoo]

Just out of curiosity (If you dont mind) -
What are your server specs?
How many SQL queries are being generated per average pageload?
How many uniques are you receiving a day?

I could try and optimize it for you, if the server is high-spec, and the load is erratic.

What are your server specs?

How many SQL queries are being generated per average pageload?
Eh? :P

How many uniques are you receiving a day?
05 May, Mon 12595

06 May, Tue 12909

07 May, Wed 12943

08 May, Thu 13088

09 May, Fri 14026

10 May, Sat 14168

11 May, Sun 13300

12 May, Mon 13211

13 May, Tue 12625

@JMZ: Maybe ill look more into it for caching the recent searches. Let me know if you are interested in helping with this.

Greetz

 

[JmZ]

if you stop going afk on msn i will help you lol

Ultima: its kddl, like 10 queries max per page load usually

these get run on search for example:
- insert search query
- possibly update search query (if using searches field and unique queries)
- list downloads
- list recents
- pages possibly seperate
- LE

 

[Whoo]

if you stop going afk on msn i will help you lol

Ultima: its kddl, like 10 queries max per page load usually

these get run on search for example:
- insert search query
- possibly update search query (if using searches field and unique queries)
- list downloads
- list recents
- pages possibly seperate
- LE

Sorry busy with gf and stuff lately :P , I got a day off college tomorrow so well see lol

talk to ya soon

 

[warzone]

Whoo has a girl friend! Surprising stuff! :P