Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    527

    limiting mysql for a site ?

    hi,
    i have a site that is eating up mysql and my ram/cpu
    is there a way to limite the use of ram/cpu/mysql for each site with out effect others on 1 server ?

  2. #2
    Join Date
    Jul 2003
    Posts
    527
    up up and away

  3. #3
    Join Date
    Nov 2004
    Location
    India
    Posts
    1,100
    I don't think there is an option available to limit mysql usage for single domain...but you can tweak your /etc/my.cnf file to limit connections, quearies..etc for server wide..
    AssistanZ - Beyond Boundaries...
    Cloudstack Consultancy / 24x7 Web Hosting Support / 24x7 Server Management / Infrastructure Management Services
    Web & Mobile Apps Development / Web Designing Services / Php, Grails, Java Development

  4. #4
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    It will depend on which version of MySQL you're using.

    Anything past MySQL v4.0.2 supports limiting MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, and MAX_USER_CONNECTIONS
    on a per user basis.

    see:
    http://dev.mysql.com/doc/mysql/en/grant.html

    GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
    NONE |
    [{SSL| X509}]
    [CIPHER 'cipher' [AND]]
    [ISSUER 'issuer' [AND]]
    [SUBJECT 'subject']]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
    MAX_UPDATES_PER_HOUR count |
    MAX_CONNECTIONS_PER_HOUR count |
    MAX_USER_CONNECTIONS count]]

  5. #5
    Join Date
    Jul 2003
    Posts
    527
    lets say i have user1 which i want to limit his mysql usage
    can i creat a my.cnf file and put it in his /home/user1/ folder or i need to edit /etc/my.cnf file ?
    also what will i have to put inside my.cnf file for limiting his usage ?

  6. #6
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Doing it using the my.cnf file as the person stated.. you will be placing the limitations SERVER WIDE.

    Read the mysql docs... specificaly how to properly grand privledges on a per user basis.

  7. #7
    Join Date
    Jul 2003
    Posts
    527
    i tried but could not figure it out
    any chance you may give me an example ?
    thanks

  8. #8
    Try: GRANT USAGE ON *.* TO '%'@'localhost' WITH MAX_QUERIES_PER_HOUR NUMBER;

    Where number, user queries number per user per hour.

  9. #9
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Backup your stuff before you start fooling with grant. You may quickly find none of your users can access mysql anymore

  10. #10
    Join Date
    Jul 2003
    Posts
    527
    this is my.cnf
    PHP Code:
    [mysqld]

    max_connections 1500
    max_user_connections 
    400
    key_buffer 
    150M
    myisam_sort_buffer_size 
    64M
    join_buffer_size 
    2M
    read_buffer_size 
    2M
    sort_buffer_size 
    3M
    table_cache 
    1024
    thread_cache_size 
    128
    wait_timeout 
    14400
    connect_timeout 
    400
    max_allowed_packet 
    16M
    max_connect_errors 
    10
    query_cache_limit 
    1M
    query_cache_size 
    32M
    query_cache_type 
    1
    skip
    -innodb
    thread_concurrency 
    4

    [mysqld_safe]
    open_files_limit 8192
    err
    -log=/var/log/mysqld.log

    [mysqldump]
    quick
    max_allowed_packet 
    16M

    [myisamchk]
    key_buffer 64M
    sort_buffer 
    64M
    read_buffer 
    16M
    write_buffer 
    16M

    [isamchk]
    key_buffer=64M
    sort_buffer
    =64M
    read_buffer
    =16M


    [mysqlhotcopy]
    interactive-timeout 
    now what exactly should i add ?
    and how to make it for user1 and not just for all

  11. #11
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Originally posted by BizB
    now what exactly should i add ?
    and how to make it for user1 and not just for all
    You can add anything you want there.. It won't help the problem you're trying to solve.

    It's already been pointed out twice that changes to the config file will be server wide changes.

    If you want to restrict one specific user... you have to use the GRANT tables.

    See above for an suggestion as to what exactly to type. But, you really should have a clue what it's going to do before hand... or pay someone who has a clue to do it for you. As the above might not do exactly what you want it to.

    http://dev.mysql.com/doc/mysql/en/user-resources.html

    Read over this tiill you understand what you're typing will do, or, as I said, don't be surprised when everyone looses access to MySQL.

Posting Permissions

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