Results 1 to 21 of 21
  1. #1

    MySQL taking up all resources

    My Server was running smoothly for about two years. I have one huge genealogy site there but suddenly MySQL started to eat up all resources. Without changing the queries or program running the data base.

    I started tweaking my.cnf which didn't make things better, then since the utility tuning-primer.sh suggested increasing memory I decided to buy an extra 4 Gb after the database is big. No improvement. Following the suggestions in the utility made things only worse, mysqltuner.pl didn't give any right answers either.

    Now my.cnf looks like
    ------------
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-networking
    safe-show-database
    query_cache_limit=1M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=200
    max_connections=2000
    interactive_timeout=10
    wait_timeout=600
    max_allowed_packet = 64M
    connect_timeout=20
    thread_cache_size=128
    thread_cache_size=128
    key_buffer=512M ## 128MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=2613
    query_cache_limit = 4M
    record_buffer=1M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    #collation-server=latin1_general_ci

    [mysql.server]
    user=mysql

    [safe_mysqld]

    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    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
    ----------------


    Anybody any ideas?

  2. #2
    I added more but the board didn't allow me to post everything at one time, so here is additional information


    mysqltuner gives the following output:

    ----------------
    >> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.57
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 505M (Tables: 2135)
    [--] Data in InnoDB tables: 59M (Tables: 277)
    [--] Data in MEMORY tables: 6K (Tables: 2)
    [!!] Total fragmented tables: 287

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 16h 10m 3s (190K q [3.270 qps], 9K conn, TX: 2B, RX: 39M)
    [--] Reads / Writes: 84% / 16%
    [--] Total buffers: 570.0M global + 10.2M per thread (2000 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 20.5G (258% of installed RAM)
    [OK] Slow queries: 0% (66/190K)
    [OK] Highest usage of available connections: 2% (59/2000)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/229.5M
    [OK] Key buffer hit rate: 99.2% (86M cached / 691K reads)
    [OK] Query cache efficiency: 51.4% (71K cached / 139K selects)
    [!!] Query cache prunes per day: 12640
    [OK] Sorts requiring temporary tables: 0% (93 temp sorts / 15K sorts)
    [!!] Temporary tables created on disk: 41% (8K on disk / 20K total)
    [OK] Thread cache hit rate: 99% (59 created / 9K connections)
    [OK] Table cache hit rate: 24% (2K open / 10K opened)
    [OK] Open file limit used: 44% (4K/10K)
    [OK] Table locks acquired immediately: 99% (104K immediate / 104K locks)
    [!!] InnoDB data size / buffer pool: 59.3M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    *** MySQL's maximum memory usage is dangerously high ***
    *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 32M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 59M)
    ---------------
    and tuning-primer.sh gives the following:

    ---------------
    -- MYSQL PERFORMANCE TUNING PRIMER --
    - By: Matthew Montgomery -

    MySQL Version 5.1.57 i686

    Uptime = 0 days 16 hrs 11 min 3 sec
    Avg. qps = 3
    Total Questions = 190369
    Threads Connected = 1

    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations

    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.1/...variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service

    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10.000000 sec.
    You have 66 out of 190390 that take longer than 10.000000 sec. to complete
    Your long_query_time seems to be fine

    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.1/...-recovery.html

    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 58
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine

    MAX CONNECTIONS
    Current max_connections = 2000
    Current threads_connected = 1
    Historic max_used_connections = 59
    The number of used connections is 2% of the configured maximum.
    You are using less than 10% of your configured max_connections.
    Lowering max_connections could help to avoid an over-allocation of memory
    See "MEMORY USAGE" section to make sure you are not over-allocating

    INNODB STATUS
    Current InnoDB index space = 16 M
    Current InnoDB data space = 59 M
    Current InnoDB buffer pool free = 0 %
    Current innodb_buffer_pool_size = 8 M
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory

    MEMORY USAGE
    Max Memory Ever Allocated : 1.12 G
    Configured Max Per-thread Buffers : 19.89 G
    Configured Max Global Buffers : 554 M
    Configured Max Memory Limit : 20.43 G
    Physical Memory : 7.91 G

    nMax memory limit exceeds 90% of physical memory

    KEY BUFFER
    Current MyISAM index space = 212 M
    Current key_buffer_size = 512 M
    Key cache miss rate is 1 : 124
    Key buffer free ratio = 86 %
    Your key_buffer_size seems to be fine

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 32 M
    Current query_cache_used = 30 M
    Current query_cache_limit = 4 M
    Current Query cache Memory fill ratio = 94.37 %
    Current query_cache_min_res_unit = 4 K
    However, 8515 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size

    SORT OPERATIONS
    Current sort_buffer_size = 4 M
    Current read_rnd_buffer_size = 1 M
    Sort buffer seems to be fine

    JOINS
    Current join_buffer_size = 1.00 M
    You have had 130 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.

    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.

    OPEN FILES LIMIT
    Current open_files_limit = 10000 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine

    TABLE CACHE
    Current table_open_cache = 2613 tables
    Current table_definition_cache = 256 tables
    You have a total of 2613 tables
    You have 2608 open tables.
    Current table_cache hit rate is 24%
    , while 99% of your table cache is in use
    You should probably increase your table_cache
    You should probably increase your table_definition_cache value.

    TEMP TABLES
    Current max_heap_table_size = 16 M
    Current tmp_table_size = 16 M
    Of 12238 temp tables, 41% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    TABLE SCANS
    Current read_buffer_size = 4 M
    Current table scan ratio = 28164 : 1
    read_buffer_size seems to be fine

    TABLE LOCKING
    Current Lock Wait ratio = 1 : 817
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=2'.
    --------------

    However certain actions which ran fine before don't work. Simple searches editing and adding data goes relatively fine but actions with a lot of joins don't.

  3. #3
    Join Date
    Oct 2009
    Posts
    865
    For one, your buffer sizes are waaay too large to use with your current max connection count. Potentially, the DBMS can use up to sum(buffer_sizes) * max_connections bytes of RAM - the buffers are allocated if needed for each thread - which on your server is almost three times the available RAM. Which can leads to swapping and horrid performance.

    Typically, if it "suddenly" starts slowing down when nothing has changed, it's because the database reached a certain threshold size where the active part can no longer fully fit in RAM, making it go to disk - which tends to overload them for reasonably busy databases. You don't mention much in terms of server capacity, but try installing sysstat and then run:

    iostat -x 1

    to analyze the current IO activity.

  4. #4
    Thank you for your answer. Problem is that when most of my users (and when the server is too busy) I am sleeping (they are on the other side of the world)

    fiddling around with my.cnf is also way over my head. I barely understand what I am doing and just follow the suggestions done by the two programs I mentioned above

    If I start one of the queries which keeps on running forever (and slows down the server)

    iostat -x 1

    Gives the following

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

    avg-cpu: %user %nice %system %iowait %steal %idle
    29.35 0.00 20.40 0.00 0.00 50.25

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.00 25.00 0.00 123.00 0.00 1184.00 9.63 2.89 23.51 0.25 3.10
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 2.00 0.00 16.00 8.00 0.04 18.00 9.00 1.80
    sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda5 0.00 25.00 0.00 121.00 0.00 1168.00 9.65 2.86 23.60 0.26 3.10
    sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

    avg-cpu: %user %nice %system %iowait %steal %idle
    29.50 0.00 20.50 0.00 0.00 50.00

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

    avg-cpu: %user %nice %system %iowait %steal %idle
    30.35 0.00 19.90 0.00 0.00 49.75

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

    avg-cpu: %user %nice %system %iowait %steal %idle
    28.00 0.00 22.00 0.00 0.00 50.00

    Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
    sda 0.00 13.00 0.00 2.00 0.00 120.00 60.00 0.00 0.00 0.00 0.00
    sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sda5 0.00 13.00 0.00 2.00 0.00 120.00 60.00 0.00 0.00 0.00 0.00
    sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
    sdb1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00

    The specifications of the server are:

    100Mbps port
    Core2 Duo 3.0GHz (1333MHz FSB)
    8 GB RAM
    3 160GB SATA2
    Linux - CentOS - 64 bits
    Plesk Unlimited

    Actually I am willing to pay someone to speed it up again.

  5. #5
    I followed the instructions here
    http://everythingmysql.ning.com/prof...-mysqls-tmpdir
    and your suggestions to change the buffer size

    That works for the normal queries but not for the queries which keep on going and make other queries "sleep"

    If I take a look in the mysqltmp directory I see the following

    [root@plesk mysqltmp]# ls -l
    total 4
    -rw-rw---- 1 mysql mysql 0 Jun 5 06:23 #sql_2a4f_0.MYD
    -rw-rw---- 1 mysql mysql 1024 Jun 5 06:23 #sql_2a4f_0.MYI

    I allocated 4G for the mysqltmp "disk"

    my.cnf now looks like

    [mysqld]
    log_queries_not_using_indexes
    big_tables
    max_heap_table_size=32M
    tmp_table_size=32M
    table_definition_cache = 2613
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-networking
    safe-show-database
    query_cache_limit=32M
    query_cache_size=32M ## 32MB for every 1GB of RAM
    query_cache_type=1
    max_user_connections=200
    max_connections=200
    interactive_timeout=10
    wait_timeout=600
    max_allowed_packet = 64M
    connect_timeout=20
    thread_cache_size=128
    thread_cache_size=128
    key_buffer=512M ## 128MB for every 1GB of RAM
    join_buffer=1M
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=2613
    query_cache_limit = 4M
    record_buffer=1M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2
    myisam_sort_buffer_size=64M
    server-id=1
    #collation-server=latin1_general_ci
    tmpdir=/tmp/mysqltmp/

    [mysql.server]
    user=mysql

    [safe_mysqld]

    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    table_open_cache=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

  6. #6
    Join Date
    Aug 2010
    Location
    Prague, Czech Republic
    Posts
    404
    You can also use mysqlreport, dstat and top to get more information about the issue.
    Also it would be useful to switch to 64 bit operation system, enable slow logs and analyze it. Besides, 'show processlist' might help.
    Supportex.Net server management, full range of services. EU-based outsourced company. Since 1998.
    Outstanding quality for high performance projects; clustering and high-availability solutions, DDoS protection.
    Cisco/Juniper network management & deployment assistance. Network design and monitoring.

  7. #7
    As far as I know I DO have a 64 bit operation system even though the utility mentions to upgrade. mysqlreport won't run, I tried. I enabled slow logs, but they are empty.

    If I run one of the queries which doesn't complete, it keeps on running, show processlist just gives me

    | 1344 | gebruikers | localhost | nieuws | Query | 31 | Copying to tmp table | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changed |
    | 1346 | gebruikers | localhost | nieuws | Sleep | 9 | | NULL |
    | 1347 | admin | localhost | NULL | Query | 0 | NULL | show processlist

    Nothing more.

  8. #8
    If I keep on using show processlist all the other queries obviously go to sleep.

    +------+------------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | Id | User | Host | db | Command | Time | State | Info |
    +------+------------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
    | 1344 | gebruikers | localhost | nieuws | Query | 118 | Copying to tmp table | SELECT p.personID, p.lastname, p.firstname, p.birthdate, p.living, p.gedcom, p.changedate, p.changed |
    | 1346 | gebruikers | localhost | nieuws | Sleep | 96 | | NULL |
    | 1351 | gebruikers | localhost | nieuws | Sleep | 67 | | NULL |
    | 1355 | gebruikers | localhost | nieuws | Sleep | 36 | | NULL |
    | 1359 | gebruikers | localhost | nieuws | Sleep | 7 | | NULL |
    | 1360 | admin | localhost | NULL | Query | 0 | NULL | show processlist

  9. #9
    Join Date
    Mar 2009
    Location
    Israel
    Posts
    1,212
    first of all - optimize and repair the databases

    [!!] Total fragmented tables: 287

    to check if you have a 64bit system, type :

    beastserv ~ # uname -i
    x86_64


    it should return x86_64

  10. #10
    That's a good one, I LEASED a 64 bit system but I seem to have
    uname -i
    i386

    Total fragmented tables: 287 that's what I don't understand. I use:
    els --mysqloptimizedb
    regularly

    and especially the biggest database is optimized three times per day. but it keeps on telling me that there are 287 fragmented tables. If I check with MySQL the big database is optimized. SO the slowness can't come from there. There are a bunch of databases in use by other users (customers) but they don't play a role in the slowness of mysql. (I checked that) They are not in use a lot maybe once a day.

  11. #11
    Join Date
    Mar 2009
    Location
    Israel
    Posts
    1,212
    Quote Originally Posted by HennySavenije View Post
    That's a good one, I LEASED a 64 bit system but I seem to have
    uname -i
    i386

    Total fragmented tables: 287 that's what I don't understand. I use:
    els --mysqloptimizedb
    regularly

    and especially the biggest database is optimized three times per day. but it keeps on telling me that there are 287 fragmented tables. If I check with MySQL the big database is optimized. SO the slowness can't come from there. There are a bunch of databases in use by other users (customers) but they don't play a role in the slowness of mysql. (I checked that) They are not in use a lot maybe once a day.

    You can still use your 8GB of ram on a 32bit server, you would have to switch your kernel version to a PAE version.

    is your system actually caching to the ram properly?

  12. #12
    Quote Originally Posted by beastserv View Post
    You can still use your 8GB of ram on a 32bit server, you would have to switch your kernel version to a PAE version.

    is your system actually caching to the ram properly?
    the OS can use 8GB RAM on PAE kernel but not MySQL. MySQL can only use 2GB RAM on 32-bit system AFAIK

    you need to switch to 64-bit OS to maximize your RAM usage
    HalfDedi.com • Half Dedicated Half Price
    We provide affordable VPS hosting solution Singapore datacenter

  13. #13
    Alright, thanks both of you. I think I will have to change to the 64 bit OS first. I complained to the server centre and let's see how long it takes before they react and change.

    I think the system is using the cache fine but I am not sure. I will keep you posted once the OS is changed.

  14. #14
    Join Date
    Oct 2010
    Location
    Copenhagen
    Posts
    252
    You might take off some load by tuning the "query_cache_size=32M" parameter.

    "However, 8515 queries have been removed from the query cache due to lack of memory Perhaps you should raise query_cache_size"

    Fixing the ram issue first would be a good idea though.

    Enabling the slow log, as others have recommended, is also a good idea.

  15. #15
    After upgrading the operating system and resolving tons of problems that caused (The server center just pushed a new disk inside with a new operating system and plugged the old disk in as backup while I specifically asked them to restore the old configuration), and a lot of testing, I finally found the solution: I added indexes to the database. Not that I knew which ones to add, but someone obviously took the effort to suggest indexes for all tables for that particular piece of software, I added them all and damn, I am back in business. Everything runs quickly now, quicker than it ever did before.

    Thanks for all of you who took the efforts of thinking along with me.

  16. #16
    hey!
    this is a nice thread..

    mine to paste your my.cnf here ?
    what is ur apache max_connections? is it 2000 ?
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  17. #17
    Here is the my.cnf

    [mysqld]
    delayed_insert_timeout=600
    flush_time=14400
    log-slow-queries=/var/log/mysql-slow.log
    long_query_time = 10
    big_tables
    max_heap_table_size=200M
    tmp_table_size=128M
    table_definition_cache = 6240
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-networking
    safe-show-database
    query_cache_limit=64M
    query_cache_size=64M ## 32MB for every 1GB of RAM
    query_cache_type=1
    log-queries-not-using-indexes=/var/log/no-indexes.log
    max_user_connections=100
    max_connections=300
    interactive_timeout=20
    wait_timeout=600
    max_allowed_packet = 64M
    connect_timeout=20
    thread_cache_size=128
    key_buffer=512M ## 128MB for every 1GB of RAM
    join_buffer=2M
    key_buffer_size=128M
    local-infile=0
    max_connect_errors=20
    max_allowed_packet=16M
    table_cache=6240
    record_buffer=1M
    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM
    thread_concurrency=4 ## Number of CPUs x 2
    myisam_sort_buffer_size=6M
    server-id=1
    #collation-server=latin1_general_ci
    tmpdir=/tmp/mysqltmp/

    [mysql.server]
    user=mysql

    [safe_mysqld]

    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192
    table_open_cache=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

    I have no idea where to find the max_connections of Apache, could you enlighten me?
    Last edited by HennySavenije; 06-19-2011 at 11:15 PM. Reason: small correction

  18. #18
    the max_connections should be located at /usr/local/apache/conf/httpd.conf.

    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM

    Is there any reference for this comment that you put ? like 1 MB for 1 GB ?
    or 128MB for 1GB ram ?

    tahnks
    Looking for shared or reseller or VPS Hosting ?
    Try our service at https://www.sosys.net!
    Singapore - Indonesia - Malaysia

  19. #19
    Quote Originally Posted by Benny Kusman View Post
    the max_connections should be located at /usr/local/apache/conf/httpd.conf.

    sort_buffer_size=4M ## 1MB for every 1GB of RAM
    read_buffer_size=4M ## 1MB for every 1GB of RAM
    read_rnd_buffer_size=1M ## 1MB for every 1GB of RAM

    Is there any reference for this comment that you put ? like 1 MB for 1 GB ?
    or 128MB for 1GB ram ?

    tahnks
    There is no max_connections in httpd.conf, so I assume there is a standard setting somewhere.

    The comments come from myhuge.cnf. I have been tweaking a lot but as I said, adding the indexes to the database did the final trick. One query basically locked the whole database (and didn't release it, not even after several hours) now it takes 30 seconds to run the same query.

  20. #20
    Join Date
    Jun 2011
    Posts
    66
    same is happening with me too

  21. #21
    This is interesting from a case-study perspective:
    1) things got slow (and appeared to do so all of a sudden) and hog resources
    2) the slow-queries log wasn't enabled
    3) it was indexes after all - so looking @ the slow-queries might have shown that they were on non-indexed tables (or didn't use an indexed column). .

    A good place to start in the future ;-)
    WholesaleBackup.com
    Online Backup Reseller
    Your Brand, Our Technology: Total Success.
    +1.800-624-9561

Similar Threads

  1. Replies: 19
    Last Post: 01-24-2010, 08:13 AM
  2. SQL Taking Up Too Much Resources
    By forcium in forum Hosting Security and Technology
    Replies: 6
    Last Post: 05-24-2008, 03:24 PM
  3. MRTG taking up resources ?
    By ashish1987 in forum Hosting Security and Technology
    Replies: 1
    Last Post: 10-12-2007, 06:06 PM
  4. Nobody User Taking up CPU Resources
    By Matt Holme in forum Hosting Security and Technology
    Replies: 11
    Last Post: 09-21-2006, 10:05 AM
  5. SQL taking up huge resources
    By TWD-Tony in forum Dedicated Server
    Replies: 10
    Last Post: 10-26-2005, 05:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •