Database Problem

Hi,
I run a popular PTC website but lately I am getting lot's of database problems. My RAM Memory & Disk Space are fine. the problem is that users are receiving very much "Cannot Connect" message when browsing through the site. This is what our server administrator said:

  Hello,

When I checked I could see that the table "tb_users" in database "buxp00_buxpv2" was getting too many locked states. I have repaired that table. But after the repair also, that table was getting corrupted soon.
==================
[root@buxp00 buxp00_buxpv2]# myisamchk -c tb_users.MYI
Checking MyISAM file: tb_users.MYI
Data records: 40810 Deleted blocks: 0
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
myisamchk: warning: Size of indexfile is: 406528 Should be: 335872
myisamchk: error: Size of datafile is: 6989016 Should be: 6989752
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found 95 keys of 40810
- check record links
myisamchk: error: Keypointers and record positions doesn't match
myisamchk: warning: Found 40810 parts Should be: 40849 parts
MyISAM-table 'tb_users.MYI' is corrupted
==================

Usually with MyISAM table, you will get (long) table level read lock, then a write lock (it was blocked by the previous read lock), then for read lock (it was blocked by previous, write, still pending). This is how MyISAM works, by design. You should NOT allow long-running concurrent statements, or just wait. 

In order to avoid this, you can proceed with following solutions.


1. Optimize tables, indexes and queries.

2. Do not use persistent connections in general (there might be conditions where they may be useful, but usually making a connection is so fast that this is better than keeping all those connections open).

3. Optimize your configuration to make optimal use of available memory.

4. Do not keep connections to your db server open when you don't need them (e.g. after querying your database your application initiates a download).

5. Keep the number of connections in your application to a minimum (avoid nested queries).

Here, we have already optimized the MySQL configuration(Solution 3). Please contact your database administrator and optimize the MySQL queries as mentioned in the above suggestions.

Thank you for your understanding.

Regards,
Alwin C.
SoftLayer Support 

Code (markup):

Now I am looking for someone who can solve the problem and I am willing to pay for it. The website is http://buxp.info

If interested, PM me with your rates and how you would solve it

Best Regards

 

Can you let me know about the platform that the site is on? Also, are you using pconnect or a singleton database connector?

Off the bat, I would suggest you migrate to innodb tables, instead of myisam. However, if this is a large database, it is probably going to take some proper tuning to get the performance you need.

 

The server is ran on Plesk panel, the site is in php.

Unfortunately I am not at all an expert in Mysql/Databases to know anything about the issue itself lol

 

Ok thats common problem in Buxto and securebux clone site it occurs when thr are to many refs for the upline the database need to be restructured

 

And you know how to do that?

 

Hi,
Following might help:
> Instead of connecting to mysql using a config file at beginning, connect only when you need to run a query. run the query and close the connection immediately.
> Add indexes to your database tables. Form your queries so they follow the indexes. This will speed the query.
ex: table1 has indexes field1, field3. Then query should be "select data from table1 where field1='some' and field3='some'";
Using "select data from table1 where field3='some' and field1='some'"
will render the indexes useless, although it's the same query returning same result, but slower.
> avoid having queries inside a loop, specially if field data in query is entered by members.
> Use "limit" in queries, so as soon as required number of results are found, query stops and returns the result. ex:
"select id from table where username='some' and password='some' limit 1"
As soon as 1 match is found, query stops scanning the table.
Using: "select id from table where username='some' and password='some' " will continue the query till the end of table even if the required match was found in the very beginning. With lots of records this will slow things a lot and the "read" lock is on all the time.
Similarly: "update table set name='some' where username='some'" will keep the write lock on till end of table is scanned even though only 1 record needed to be changed.
Use: "update table set name='some' where username='some' limit 1" instead.

These changes in php code alone could help a lot.
regards

 

Ok, now I need someone who can enter these changes and restructure our script/db for us. Contact me if you are capable to do that.