Database problems haunting me again

Hi Guys

I am back again with MySQL database problems. I previously had problems where my databases became so slow, that it was unusable. I then moved to VPS hosting and all searches were fast again. 6 months later and everything is slow again. I upgraded to s super fast and big VPS server, but everything is slow.

Here is what I have:

My server: CentOS Linux 5.94Ghz Processor, 4435mb Ram

I have one database with 76 tables. In size it totals 14gb. There is 140 million + records. All fields are different. Some tables have 2 fields while others have 121 fields. Fields are not the same throughout.

I have only 110 users using the databases. One search takes around 12 - 30 minutes to display results. (Sometimes even longer).

All required fields are indexed.

Is there any way in which I can make things faster or is that the way it is?

 

Most likely something is requiring very large full-table scans, and not using any cache. Can you identify and post the actual query that is causing the problems, as well as the structure of the table that is being queried.

 

try to reduce the mysql data

other alternative shift to amazon (which is not simple ) but i have heard they are very fast

other alternative buy a mysql data host seperate than vps

you need to experiment thats the only solution

In case you should have full backup

Query optimization is the way by which you can reduce time of search

atleast optimise some other queries where full search is not needed

Some queries donot need whole database try to optimise them which will in a way reduce load on server.

PM me if u think i can do something for you

Regards

Alex

 

I would first start by checking your fields have the best indexes, read about the EXPLAIN keyword in MySQL Documentation. Unused, or missing indexes are the number one cause of performance issues usually.

Then check your field types are optimised - for example if you have 140 million keys and you are using bigint as the field type, you would only need int precision - this needs half the amount of memory required to index and search. (4 bytes instead of 8)

You could also investigate table partitioning - again the MySQl DOcs have loads of info on this.

 

Hi guys

Thank you very, very much for your info and help.

As suggested by kmap I started looking at my data. I deleted unused fields, and also changed the field character lengths (which all was 255) down to the required maximum characters. I also deleted some missing indexes.

My database is now much, much faster and searches are conducted in less than a minute.

Thanks again for your help!