I have a dedicated server that I use zen cart on and I am seeing some bottle necks with regards to load speed etc... I looked at my sql .cnf file and its pretty well empty.

I am curious what settings everyone uses for mysql configuration. I have 4 gigs of ram. I would like to get this set for optimal settings. Any advice available?

Thank You

I have used a program called mysqltuner and here is what it says is going on with my database

MySQLTuner 1.3.0 - Major Hayden [email protected]
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.1.56
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM -ndbcluster
[--] Data in MyISAM tables: 190M (Tables: 709)
[--] Data in InnoDB tables: 2M (Tables: 138)
[--] Data in MEMORY tables: 0B (Tables: 3)
[!!] Total fragmented tables: 183

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 13h 7m 27s (4M q [21.773 qps], 25K conn, TX: 6B, RX: 804M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 449.2M (12% of installed RAM)
[OK] Slow queries: 0% (0/4M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/60.0M
[OK] Key buffer hit rate: 100.0% (112M cached / 10K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 490K sorts)
[!!] Temporary tables created on disk: 27% (79K on disk / 288K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 10K opened)
[OK] Open file limit used: 10% (110/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
[OK] InnoDB buffer pool / data size: 8.0M/2.3M
[OK] InnoDB log waits: 0

This my current configuration file for mysql and I know it is not right

[mysqld]
bind-address=127.0.0.1
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
#query_cache_size=256M
#query_cache_type=1
#query_cache_limit=2M

Default to using old password format for compatibility with mysql 3.x

clients (those using the mysqlclient10 compatibility package).

old_passwords=1

Disabling symbolic-links is recommended to prevent assorted security risks;

to do so, uncomment this line:

symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Hosts that are here guard their time spent in tuning mysql settings
This is most likely why you are not receiving any responses

Are you aware that a setting of:

[B]#[/B]query_cache_size=256M

that is commented out ...

If you want to use it, use:

query_cache_size=256M

Yep I seen that and fixed that yesterday.

My settings I am using now are

thread_cache_size = 16K
query_cache_size = 128M
query_cache_limit = 5M
key_buffer = 64M
long_query_time = 5
table_cache = 1K
table_definition_cache = 4K
open_files_limit = 3K
tmp_table_size = 64M
max_heap_table_size = 64M

Everything seems to be running a bit faster now.

Ajeh:

Are you aware that a setting of:

[B]#[/B]query_cache_size=256M

> that is commented out ...
> 
> If you want to use it, use:
> ```
query_cache_size=256M

These 2 settings is something I am playing with that I have not gotten right as of yet...

tmp_table_size = 64M
max_heap_table_size = 64M

Any clue what the optimal settings would be for this to help zen cart run more efficient?

We really are not the best to ask about server info, but if you google those there are a lot of articles on these ...

Turn off query cache on MySQL server (it has scalability problems and it's pruned too often to be useful - unless new version of Zen Cart does not update products_view counter in products table).
Move all tables to InnoDB.
Use Percona Server or MariaDB instead of stock MySQL. If You can use Percona Server 5.6 or MariaDB based on MySQL 5.6 - version 5.6 of MySQL introduced some important optimizations.
Use innodb_trx_at_commit = 2 (especially if You don't have a SSD drive).
Set innodb_buffer_pool to 150% of size of all Your InnoDB tables.
Don't play with thread_cache.
Depending on Your configuration adjust table_cache, table_definition_cache, open_files_limit (You will have to watch MySQL counters to verify if those numbers are set too high/too low) - google how to do it.
You're set and in 95% You don't have to adjust anything else. And if You feel You're in those 5% that have to adjust MySQL more hire a DBA because on Your own You're doomed to do more bad than good.

data_digger:

unless new version of Zen Cart does not update products_view counter in products table
Zen Cart stopped updating products_viewed in the products table as of v1.5.0, released in 2011.

EDIT: My bad. Actually, it's still enabled in 1.5.0, 1.5.1, 1.5.2, but easily disabled by merely editing /includes/extra_datafiles/products_viewed_counter.php and changing the define for LEGACY_PRODUCTS_VIEWED_COUNTER to 'off' instead of 'on'.

*For tuning MySQL, see: http://www.zen-cart.com/content.php?262-webserver-tuning-tips

DrByte:

Zen Cart stopped updating products_viewed in the products table as of v1.5.0, released in 2011.

Then for Zen Cart >= 1.5.0 it might be worth to have MySQL query_cache enabled.