Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Join Date
    Nov 2010
    Posts
    21
    Plugin Contributions
    0

    Default Reccomended MySql Settings?

    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

  2. #2
    Join Date
    Nov 2010
    Posts
    21
    Plugin Contributions
    0

    Default Re: Reccomended MySql Settings

    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

  3. #3
    Join Date
    Nov 2010
    Posts
    21
    Plugin Contributions
    0

    Default Re: Reccomended MySql Settings

    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

  4. #4
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,761
    Plugin Contributions
    9

    Default Re: Reccomended MySql Settings

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

  5. #5
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    69,402
    Plugin Contributions
    6

    Default Re: Reccomended MySql Settings

    Are you aware that a setting of:
    Code:
    #query_cache_size=256M
    that is commented out ...

    If you want to use it, use:
    Code:
    query_cache_size=256M
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.5]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...
    Signup for our Announcements Forums to stay up to date on important changes and updates!

  6. #6
    Join Date
    Nov 2010
    Posts
    21
    Plugin Contributions
    0

    Default Re: Reccomended MySql Settings

    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.

    Quote Originally Posted by Ajeh View Post
    Are you aware that a setting of:
    Code:
    #query_cache_size=256M
    that is commented out ...

    If you want to use it, use:
    Code:
    query_cache_size=256M

  7. #7
    Join Date
    Nov 2010
    Posts
    21
    Plugin Contributions
    0

    Default Re: Reccomended MySql Settings

    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?

  8. #8
    Join Date
    Sep 2003
    Location
    Ohio
    Posts
    69,402
    Plugin Contributions
    6

    Default Re: Reccomended MySql Settings

    We really are not the best to ask about server info, but if you google those there are a lot of articles on these ...
    Linda McGrath
    If you have to think ... you haven't been zenned ...

    Did YOU buy the Zen Cart Team a cup of coffee and a donut today? Just click here to support the Zen Cart Team!!

    Are you using the latest? Perhaps you've a problem that's fixed in the latest version: [Upgrade today: v1.5.5]
    Officially PayPal-Certified! Just click here

    Try our Zen Cart Recommended Services - Hosting, Payment and more ...
    Signup for our Announcements Forums to stay up to date on important changes and updates!

  9. #9

    Default Re: Reccomended MySql Settings

    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.

  10. #10
    Join Date
    Jan 2004
    Posts
    66,364
    Blog Entries
    7
    Plugin Contributions
    274

    Default Re: Reccomended MySql Settings

    Quote Originally Posted by data_digger View Post
    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?...er-tuning-tips
    Last edited by DrByte; 26 Mar 2014 at 02:59 PM. Reason: updated
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. Reccomended customizable sidebox?
    By contriveit in forum Addon Sideboxes
    Replies: 2
    Last Post: 24 Feb 2013, 10:04 AM
  2. Ignores Meta Settings in Admin Product Settings
    By NewbietoEC in forum Templates, Stylesheets, Page Layout
    Replies: 1
    Last Post: 28 Jul 2010, 04:04 PM
  3. Optimze MySQL - specific server settings
    By DogTags in forum General Questions
    Replies: 10
    Last Post: 13 Sep 2007, 12:46 PM
  4. MySQL Cache, Processes Settings
    By NamSingh in forum Installing on a Linux/Unix Server
    Replies: 4
    Last Post: 17 Feb 2007, 04:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR