•  

Announcement

Announcement Module
Collapse
No announcement yet.

Optimized Configuration

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

  • Optimized Configuration

    #1
    Hi guys,

    I'm setting up a PHP/MySQL web application on a new VPS. The VPS is running PHP 5 and MySQL 5, and has 512MB RAM (burstable to 1GB). The server and the application are freshly installed, so I don't have much benchmarks on the database performance, but I would like it to be robust and scalable under a high-usage multi-user scenario.

    The database schema consists of 4 tables only, all InnoDB except for one which is a MyISAM table. The MyISAM table has a full-text index on one of the VarChar fields. There are indexes on all relevant primary key and foreign key fields (all MediumInt).

    I would appreciate if someone can review my database configuration (my.cnf listed below) and please suggest suitable improvements:


    [mysqld]port = 3306socket = /var/lib/mysql/mysql.sockmax_connections = 400key_buffer = 16Mmyisam_sort_buffer_size = 32Mjoin_buffer_size = 1Mmax_allowed_packet = 16Mmax_connect_errors = 10table_cache = 1024thread_cache_size = 286query_cache_limit = 1Mquery_cache_size = 16Mquery_cache_type = 1tmp_table_size = 16Minteractive_timeout = 25wait_timeout = 1000connect_timeout = 10sort_buffer_size = 2Mread_buffer_size = 1Mread_rnd_buffer_size = 256Knet_buffer_length = 2Kthread_stack = 64Kft_min_word_len = 3skip-bdbskip-innodbskip-locking[mysqld_safe]open_files_limit = 8192[mysqldump]quickmax_allowed_packet = 16M[mysql]no-auto-rehash#safe-updates[isamchk]key_buffer = 8Msort_buffer_size = 8M[myisamchk]key_buffer = 32Msort_buffer = 32Mread_buffer = 16Mwrite_buffer = 16Msort_buffer_size = 8M[mysqlhotcopy]interactive-timeout


    Thanks in advance!
    Tags: None

  • #2
    You have a bunch of different things that are a bit strange about that config.

    Have you checked out the different settings yourself yet?

    Because you say that 3 of your tables are InnoDB and yet you have:
    Quote:

    skip-innodb

    in your config.

    Comment


    • #3
      Thanks for your reply.

      The config I posted is a template. Its in no way for final production use yet, and that's why I'm seeking expert advice )

      You are right, "skip-innodb" should be removed and instead the "innodb_*" parameters should be used.

      Any recommendations for a stable (yet scalable) configuration to start with?

      Comment


      • #4
        Please share any suggestions.

        Comment


        • #5
          Some questions that I need answered if I'm going to give you proper suggestions.

          1.
          How big is your database in MB?
          So that I can get a feel for what the settings would be.

          2.
          Do you really need InnoDB and why?
          Because as I understand you need MyISAM due to fulltext index and it is better to just focus on one table type in that case.

          3.
          How many queries per second are you expecting?
          You should always try to do some estimation so that you know what to begin with. Otherwise you are just shooting in the dark with the settings.

          Comment


          • #6
            Thanks Sterin,

            Quote:

            1. How big is your database in MB?


            The database will initially be around 10-20mb only, but it will scale up from day one, as users start adding data. Each user will have on average about 1000-1500 records (mainly with varchar and mediumint fields) as aggregate in all tables. There are couple of table joins (max between 2-3 tables) as well, and some composite indexes defined. Average fields per table is 4-5. So its not a very complex schema, but the data storage will scale up eventually.

            Quote:

            2. Do you really need InnoDB and why?


            I've now fianlized on InnoDB (for all tables), because I've given up on MySQL FTS (the reason I had MyISAM), as I'm using Sphinx FTS now.

            Quote:

            3. How many queries per second are you expecting?


            Based on preliminary usage analysis, I'm expecting around 50-100 queries per second initially, and it will scale up to 500-1000 queries per second (rough estimate) in the first 6 months.

            Hoping to hear your suggestions and clarify the scalability.

            Good day!

            Comment


            • #7
              Please suggest. Thanks!

              Comment


              • #8
                OK, here's a suggestion.

                I have made some assumptions based on what you told me and some experienced guesses of my own.

                You said the server had 512MB RAM.
                Which means that I have deduced about 128MB (OS) + 128 (Apache+PHP) = 256Mb.
                If you look at this later and it turns out they are using much less then you can increase the Innodb_buffer_pool_size parameter.
                But at the same time you said that your DB was current only 20Mb so it will take you a while before your are going to need so much cache space.

                I also made an estimation about future storage requirement for your DB.
                One user = 1500 rows according to you and if we assume an average of 5 columns and a guess of column width to 100 bytes (which is probably very high but since I don't know about your structure).
                It still turns out to: 1500 * 5 * 100 = 750kb so the growth rate doesn't seem to be that high.
                Which means that if your InnoDB table space is set to 256Mb as I have in the conf file.
                It will take you 3000 users to reach 256MB. Is this a reasonable figure?

                Above I'm trying to get you to start to think about things like this. Because without actually trying to calculate something you will never make an educated guess.


                Unless you had some special reason for some of the settings in the my.cnf that you posted.
                I suggest something like this instead.
                It is much simpler since I removed a lot of usually unnecessary or default values:
                Quote:


                [mysqld]
                max_connections = 256
                skip-locking
                max_allowed_packet = 1M
                table_cache = 256
                sort_buffer_size = 5M
                read_buffer_size = 1M
                read_rnd_buffer_size = 4M

                query_cache_size= 16M

                innodb_data_file_path = ibdata1:256M:autoextend
                # You can set .._buffer_pool_size up to 50 - 80 %
                # of RAM but beware of setting memory usage too high
                innodb_buffer_pool_size = 256M
                innodb_additional_mem_pool_size = 20M
                # Set .._log_file_size to 25 % of buffer pool size
                innodb_log_file_size = 64M
                innodb_log_buffer_size = 8M
                # Can increase insert/update performance a lot,
                # drawback is if OS crashes you might loose some
                # transactions.
                innodb_flush_log_at_trx_commit = 1
                innodb_lock_wait_timeout = 50

                [mysqldump]
                quick
                max_allowed_packet = 16M

                [mysql]
                no-auto-rehash

                [mysqlhotcopy]
                interactive-timeout


                This should be sufficient for you to start with and run for quite some time. And by then your DB will be large enough to actually be able to spot any real performance problems.

                Comment


                • #9
                  Thanks a lot sterin! Your suggestions make good sense, and I'll try them out.

                  Comment

                  Working...
                  X