GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Terrible Performance... Will Pay For Help!

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Terrible Performance... Will Pay For Help!

    Hi All,

    I currently run a Website which gets around 100,000 hits a day. The CMS software is Joomla, I run Coppermine Photo Gallery, and PHPBB. The messageboard has around 280,000 posts... and on average it's around 300 users online.

    For some reason MYSQL performs very very slow. I have two servers. I use one as a dedicated Web Server, and the other as a dedicated database server. The specs for both servers identical:

    Each Server Has:

    2 X Dual Core Xeon 5110 /4MB Cache
    4 Gigs Of Ram
    2 X 300GB SAS Drives



    The load on the webserver never reaches more than 10%. I use Apache/ Windows Server 2003, and MYSQL 5.037 . Database server frequently uses 700mb of memory and load fluctuates between 20-50%. Sometimes the site runs fast, and at other times it runs very very slow. I was wondering if someone could assist me in optimizing MYSQL so my site could be blazing fast like it should be.


    Here is my my.ini file:


    #
    [client]

    port=3306

    [mysql]

    default-character-set=latin1

    [mysqld]

    # The TCP/IP Port the MySQL Server will listen on

    port=3306

    basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
    datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/"

    default-character-set=latin1
    default-storage-engine=INNODB

    max_connections=800

    query_cache_size=166M
    table_cache=1520
    tmp_table_size=256M
    thread_cache_size=38


    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=256M

    key_buffer_size=768M
    read_buffer_size=64K
    read_rnd_buffer_size=256K

    sort_buffer_size=256K


    #*** INNODB Specific options ***

    #skip-innodb

    innodb_additional_mem_pool_size=11M

    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=6M
    innodb_buffer_pool_size=500M
    innodb_log_file_size=100M
    innodb_thread_concurrency=10



    If you are willing to help me then private message me some contact information. E-Mail/AOL/YAhoo/ICQ is fine. I prefer instant message though.

    Thanks!

  • #2
    Another thing, whenever I try to increase the following settings:

    read_buffer_size=64K [ Tried 2M ]
    read_rnd_buffer_size=256K [Tried 2M ]
    sort_buffer_size=256K [I tried 6M]


    The database goes even slower. Why is that?

    Comment


    • #3
      What % of your tables are InnoDB?

      How much free memory does the server have when running at full capacity?

      Comment


      • #4
        Something I noticed about setting high values where not needed in your my.cnf is if MySQL cannot allocate resources given your hardware for the settings in your cnf then it will just use its own defaults instead or fail outright at any sort of reliability. Looking at your max connection count, I don't believe that your 100k hits a day require that many connections and to set your connection count that high is requiring 8x the default settings requirements. If you revisit the documentation regarding your buffers and max connections, i think it will do you a lot of good performance wise.

        Comment

        Working...
        X