Website very slow, timing out. db connection slow

[211655]

Hi,

My db has about 1500 products (not many)....

 

 

My site is very very slow.

What are my option. i get maximum timeout 60 seconds error too. I do have some contribution like who is online, visitor stats, sts etc...

 

Please suggest what should i do. what should i take out. and how can i increase the database and site optimization. speed and so on.

 

should i increase any limit in php and mysql for any thing. I need to have the site faster than now.

 

Please suggest...

 

Thanks

[211655]

what will haooen if i set this to true in my confugure.php

 

 

define('USE_PCONNECT', 'false'); // use persisstent connections?

 

 

thanks

[Guest]

will make it worse. it is most likely your host has overloaded the server

[211655]

it happens when i log in to admin page and the whole site gets slow.

any other thing u think i will do.

 

thanks for the tip, i wont use that, what is persistnat conn by the way. really appreciate all suggestions.

[Guest]

from the MySQL Docs:

 

 

Persistent connections is a concept, which is designed to improve performance for some application. Instead of connection being established for each operation, it is opened once and kept in pool for the applications lifetime.

 

Persistent connections concept allows elimination of several types of overheads, e.g. authentication is performed only once, and so extra network traffic is saved. Some internal structures have to be allocated to handle extra connection. Finally there could be some connection level caches, which need to be filled up for proper performance.

 

MySQL is tuned to keep all of these overheads low. Authentication in MySQL is rather fast, which takes only one roundtrip and small amount of traffic. The slowest operation involved is connection creation. Creation of the OS level thread is eliminated with thread cache. Freed threads are not freed to OS but kept in the pool to be reused by new connections. Most of the caches in MySQL are kept global. Even tables opened by connection are kept in the table cache to be recycled. As MySQL has fast connections handles, in most cases performance benefit of using persistent connection is low.

 

Are there any drawbacks of persistent connections?

Unfortunately there are. At first each connections takes up resources. This includes MySQL internal resources (memory, table cache elements, connections) as well as OS level resources - threads, open files and sockets. Some operating systems are quite OK with this, while others may show performance degradation. MySQL resources are often more important. By having the same amount of memory that would be used for persistent connections, you usually can configure MySQL to use larger buffers, which often gives extra performance. Will this outweigh the benefit from persistent connections? It completely depends on the application.

 

Persistent connections can also lead you to the trouble if you're not using them carefully enough. Some issues described here are handled automatically by good persistent connection support, while others are not. If you've started transaction but did not complete it, the result could be unpredictable - based on what next connection user will do first commit or rollback transaction. Exactly the same issue happens with locked tables. They could be locked stale leading to various issues. Moreover other thread specific settings are left - connection specific variables (sort_buffer,record_buffer...), server level variables (@my_variable), state dependend functions found_rows(), last_insert_id() etc.

 

If you're planning to use persistent connections and are using features similar to the ones listed above, you should check if your persistent connections support library handles these issues and if it does not, you need to fix them manually.

 

Due to the nature of persistent connections, biggest benefit by using them can be achieved over slow network connections and with a few quick queries per connection. If you have complex queries, which take long time to execute or more than 3-5 queries per connection, persistent connections will give you almost no benefit.

 

Conclusion: Instead of blindly enabling persistent connections in MySQL you may wish to benchmark your application to see if persistent connections really give you the performance benefit, and if it is large enough to justify for the limitations introduced by them.

[211655]

thanks john

 

 

now what other thing i should look into to increase the speed.

[211655]

hi john,

 

what about maximum settings for max timeout error i get for 30 seconds. shoudld i increase that to have it fixed.

[Guest]

not really, that should not be a problem. probably a memoryt problem and too many users on sql. what all contributions did you add in the admin area?

[211655]

auto backup

vistior stats

who is online

etc...

[211655]

actually someting is wrong.

 

on my product_info.php page

 

it is sending sql request to database in excess of 500 till 1000 or more....

 

what is wrong here

[211655]

Nothing much.

 

Just mainlythese

 

auto backup that runs every 15 minutes and backup db.

online orders. i have about 6000 orders. it loads all of them up with 30 on each page. may be thats the issue.

 

 

But john my main issue is not that.

 

As i write, i am on the phone with my host. We are moving to brand new dedicated server today. same configuration as of the old one. my sql, php.

windows 2003 server.

 

pure dedicated with me access of remote deskop as well...

 

 

All of my test are on the new site. old is even worst.

 

what happens is that whenver one person goes to any product page, it send about 500 queries to db.

I downloaded the new osc ms 2.2 and use that product info and it did same so i think may be it sends that many in any case.

 

 

Issue is that it is very very slow.

 

may be in admin, i can change the admin orders to show only recieved or processing at the time of load, but webiste it self suddenly gets slow and mysql takes 50 percent of the cpu of 2gb ram.

 

thats bad. i get on avergae 80-200 users online or even less and that shouldnt be the case.

 

also while i moved... except slowness, one more issue came up. i dont get orders email, neither send to customer nor me.

 

same kind of server as before, same php and same mysql version.

 

(not: database is also moved to the same machine, it was somehwree else before)

 

now i have both running on same box..

 

any thoughts of that and also email. i need to move the site today and appreciate all ur help i can get.

[211655]

and i use mysql administrator 1.1 to monitor that ....

[211655]

and where can i change in order.php to show only order which are recieved or processing. if i need t see all or others, i will sleetc from the right top drop down.

[user99999999]

Did you remove category counts, enable cache, and add indexes?

 

http://www.oscommerce.com/community/contributions,1282

 

http://www.oscommerce.com/community/contributions,491

[211655]

no cache is diables, no counts i dont show them and i never added indexes. its been still bad.

 

dave and john, however we moved the site to the new dedicated server even email wasnt working coz the site was slow before.

 

its still not as great as it was. admin pages suck, specially order one, it takes 2 minutes to just load up the page.

 

same with categories and adding products. the site is doing ok but when u see the product info, it is sending out 500 queries. can u check ur store if it does too. i used mysql adminsitration 1.1 to view.

 

what will these indexes do. i will work on them.

 

also all tips are appreciated.

 

thanks friends

[♥toyicebear]

An url to have a look at would make helping you easier....

 

You might try to disable the also purchased module and see if that helps improve your product_info pages load time.

[user99999999]

no cache is diables

 

Enable it, this will cache the categories and mfg box in a file instead of rebuilding it on every page request.

 

i never added indexes.

Its mandatory for databases with 1000's of records.

[211655]

but why my orders page in admin is slow.

[211655]

and ho maqny queries u guys do on ur page.

[♥Vger]

BUMPING OF POSTS IS NOT ALLOWED - and you are about the worst I have come across.

 

Your problems are not osCommerce related, at least not directly. You have a large site with several mods running on it and that is consuming resources.

 

You need to stop running db backups every 15 minutes - totally unnecessary.

 

Vger

[211655]

Read above: I already have taken that out.

there is no backup running, no visitor stats or so.... something else is causing this that whenever i go to product info its slow and also if i go to orders page it slows down the site.....

 

what other may have been causing it.

[Guest]

As Vger stated, your contributions you have added are most likely the culprit. I have many sites with what you have and they run on the same server, so I know something with 6000 orders does not kill the server. most have 2000 to 5000 products, some xfer 125 GB bandwidth a month, and still the server load is less than .1 on average

[211655]

Thanks John and Vger,

Yes,

I just brought in one of my copies of orders page and it all worked fine. Thanks again for your help guys.

 

I will create indexes as suggested. I think it will help.

 

SOLUTION PROVIDED.