Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Website very slow, timing out. db connection slow


Recommended Posts


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...



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.



Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...