Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Awesome speed improvement


Daemonj

Recommended Posts

From version 4.0.1 on, MySQL Server features a Query Cache. When in use, the query cache stores the text of a SELECT query together with the corresponding result that was sent to the client. If the identical query is received later, the server retrieves the results from the query cache rather than parsing and executing the query again.

 

Note: The query cache does not return stale data. When data is modified, any relevant entries in the query cache are flushed.

 

The query cache is extremely useful in an environment where (some) tables don't change very often and you have a lot of identical queries. This is a typical situation for many Web servers that generate a lot of dynamic pages based on database content.

 

Add the following entries to your my.cnf file under the [mysqld] block.

query_cache_type = 1

query_cache_size = 10m

 

The query_cache_type enables query caching and the size specifies the size of the cache. Feel free to increase this number as your server's memory permits. Obviously the larger the cache the better.

 

In addition, to reduce unnecessary overhead, add the following entries in the [mysqld] block:

skip-innodb

skip-bdb

 

The above assumes that you using the default table type of myisam and not using innodb and bdb table types. If all you have in your databases is the osC database then you will be fine.

 

For additional performance options, see the different configuration examples provided in the mysql installation (e.g. huge.cnf and large.cnf). Each is based upon the amount of memory the server has and you will be better off using a smaller configuration than using too large of a configuration.

 

Be sure to keep an eye on your swap usage after making any of the above changes to ensure you have the proper settings.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

Am I correct to assume that this assumes you have control of the server and can add the entries to the mysqld block? Also if the server is not yours, can you still try to take up the servers memory? If your on a shared server, I doubt others or the hosting company would like that one much.

If I was crafty, this would be a funny signature.

Link to comment
Share on other sites

You are correct in that you need to have root access to the server, which generally means either a VPS account or a dedictated server.

 

Being on a shared server, there are usually not enough resources available between all of the domains in order to make the necessary changes worthwhile.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

why can i not find this file. What file am i looking for. I am running 4.0.16 of MySQL I have lloked all over my server and there is no My.conf file. WHat am i missing here. I really need this becuase i am VERY slow with a DB of over 356,000+ items for sale!!!

 

help

Link to comment
Share on other sites

The file is usually in /etc but you should be able to find it with either locate my.cnf or which my.cnf.

 

This modification is not dependent upon the number of items you have but the number of configuration settings you have.

 

To improve the performance of your server with that many items, you should adjust your mysql settings. Please review the mysql manual for performance tuning.

 

In addition, check the tips and tricks forum for a post regarding indexes to add.

 

Lastly, you should optimize your database and set Show Category Counts to false.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

  • 4 weeks later...

I am running Mysql 4.0.18 on RH 9. By default I did not have a my.cnf file.

You have to create one by:

 

Copy one of these files (based on your Db's requirements) :

 

/usr/share/mysql/my-huge.cnf

/usr/share/mysql/my-large.cnf

/usr/share/mysql/my-medium.cnf

/usr/share/mysql/my-small.cnf

 

to /etc/my.cnf to set global options.

 

Hope this helps.

Link to comment
Share on other sites

  • 5 months later...

Jim Ekleberry you are amazing!

 

WOW!!! That just cut my parse time by 1/2! That is a 50% increase in speed! YOU ROCK!!! I am so impressed by such as simple little thing!

 

Given that we own a Dedicated Dual Xeon 2.8 1MB L2 HT 1GB server. Load time for a 50 item list is at .059 seconds. Sending hot blondes Jim's way!

Link to comment
Share on other sites

  • 3 weeks later...

Hi Jim,

 

Just tried this with a Linux RHE server (apache 2.0) adjusted the my.cnf file to add the following lines:

 

query_cache_type = 1

query_cache_size = 10m

skip-bdb

 

So the file looked like this:

 

[mysqld]

socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

datadir=/var/lib/mysql

skip-innodb

query_cache_type = 1

query_cache_size = 10m

skip-bdb

 

[mysql.server]

user=mysql

basedir=/var/lib

 

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[client]

socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

 

However on retarting httpd it terminated and I had to copy back my old my.cnf file to get it working.

 

Any ideas where I went wrong?

 

Thanks

 

Kino

 

EDIT: Actually just tried again with the skip-bdb line taken out and all is well - thanks.

Link to comment
Share on other sites

This is the my.cnf setup recommended by a very experienced server admin I know:

 

Thanks Jim for sending me off to investigate this - much appreciated!

 

-------------------------------

Here is my copy of my.cnf

 

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-locking

skip-innodb

query_cache_limit=1M

query_cache_size=32M

query_cache_type=1

max_connections=500

interactive_timeout=100

wait_timeout=100

connect_timeout=10

thread_cache_size=128

key_buffer=16M

join_buffer=1M

max_allowed_packet=16M

table_cache=1024

record_buffer=1M

sort_buffer_size=2M

read_buffer_size=2M

max_connect_errors=10

# Try number of CPU's*2 for thread_concurrency

thread_concurrency=2 <--- Try 4 for dual pentiums

myisam_sort_buffer_size=64M

log-bin

server-id=1

 

[mysql.server]

user=mysql

basedir=/var/lib

 

[safe_mysqld]

err-log=/var/log/mysqld.log

pid-file=/var/lib/mysql/mysql.pid <-- Not necessary

open_files_limit=8192

 

[mysqldump]

quick

max_allowed_packet=16M

 

[mysql]

no-auto-rehash

#safe-updates

 

[isamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

 

[myisamchk]

key_buffer=64M

sort_buffer=64M

read_buffer=16M

write_buffer=16M

 

[mysqlhotcopy]

interactive-timeout

 

In case any of you are interested - use at your own risk as always.

 

You will need to restart mysql after making any changes and check the error log in case anything is being logged.

 

Best

 

Kino

Link to comment
Share on other sites

  • 3 weeks later...

I'm using MySQL 3.23.54. Is there a way I can do something similiar as the opriginal post, to increase parse time, for example the query cache directive in the my.cnf file?

Link to comment
Share on other sites

I tried adding "

key_buffer=50M

" to my my.cnf config file to increase parse times and store performance, and restarted MySQL, but got the error:

0 DB_ERROR_NOT_CONNECTED

when bringing up the front page of the store via http.

 

I had to delete the server variable and restart. Anyone have an idea why the variable brings up the error?

 

I'm using MySQL 3.23.54.? Is there a way I can do something similiar as the opriginal post, to increase parse time, for example the query cache directive in the my.cnf file?

Link to comment
Share on other sites

HI, i've just upgraded to mysql ver. 4.1.8, and i've got a good performance improvement. I've tried to use your advice, but all i get is a slower site. can somebody guess why?

 

Hi Drako,

 

Not sure about that - but you might want to look into installing Turk MMcache or PHP Accellerator (not both) instead to work on improving performance?

 

Best

 

Kino

Link to comment
Share on other sites

Given that we own a Dedicated Dual Xeon 2.8 1MB L2 HT 1GB server. Load time for a 50 item list is at .059 seconds.  Sending hot blondes Jim's way!

 

Install PHP-Accelerator and see as your page times get slashed again :)

Link to comment
Share on other sites

  • 2 weeks later...

do you have to restart mysql after adding that code?

 

Also in the configuration.php What should these be set to for maximum performance?

 

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

define('STORE_SESSIONS', ''); // leave empty '' for default handler or set to 'mysql'

 

thanks!

Link to comment
Share on other sites

  • 4 weeks later...
do you have to restart mysql after adding that code?

 

Also in the configuration.php  What should these be set to for maximum performance?

 

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

define('STORE_SESSIONS', ''); // leave empty '' for default handler or set to 'mysql'

 

thanks!

 

This is a very good question... for the experts !

:thumbsup:

Link to comment
Share on other sites

Whether or not to use Persistent connections depends entirely on your situation, and in most cases is a bad thing (especially on shared servers, where this might not even be an option).

 

see this link for more info:

http://forums.devshed.com/archive/t-60359/...ctions-with-PHP

 

 

Stroring sessions as files is slightly faster than setting

define('STORE_SESSIONS', 'mysql');

 

but you lose some flexibility, and if your sessions are not being stored in a secure folder below your web root, there is a security issue.

 

 

To comment on mysql query caching and oscommerce - I'd also suggest that anybody interested in further slight improvements modify the code that updates the "products_viewed" column in the "products_description" table, since as this is updated every time a product is viewed, it makes the query cache useless on any queries that involve the products_description table.

 

It is quite a simple matter to create another table and update/insert into this, and change the admin side accordingly.

Link to comment
Share on other sites

  • 3 months later...
  • 1 year later...
  • 8 months later...

This has made a dramatic difference on one of my servers using the suggested optimum settings.

 

The server is only a 1GHz Duron, with 256Mb RAM and I was thinking about upgrading it to improve speed...the above optimization does more than I could have hoped for.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...