GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MyISAM to INNODB..

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

  • MyISAM to INNODB..

    Our community starts to gets slowly in the evening and i tried to change from myisam to innodb. For some hours its works fine. But in the evening my site gets very slow and a error message starts to show sometimes.

    Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
    [MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Lock wait timeout exceeded; try restarting transaction

    Is it my querys or can i do some performance to speed up my site and not gets any erromsg.

    Do i need to change all myisam to innodb? is it ok only convert the heavy tables?

    Need more info to help?

    //Henrik


    Server specifications:
    2x Intel Xeon CPU 3.2 ghz
    4 GB RAM
    Windows 2003
    IIS 6.0
    MySQL 4.1.11
    ASP 3.0

    Datafolder: 200mb


    my.ini:
    [mysqld]
    port=3306
    basedir="C:/Program Files/MySQL/MySQL Server 4.1/"
    datadir="C:/Program Files/MySQL/MySQL Server 4.1/Data/"
    default-character-set=latin1
    default-storage-engine=INNODB
    max_connections=800
    query_cache_size=200M
    table_cache=1520
    tmp_table_size=30M
    thread_cache_size=38

    #*** MyISAM Specific options
    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100M
    myisam_sort_buffer_size=30M
    key_buffer_size=1024M
    read_buffer_size=64M
    read_rnd_buffer_size=256M
    sort_buffer_size=128M

    #*** INNODB Specific options ***
    innodb_additional_mem_pool_size=6M
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=3M
    innodb_buffer_pool_size=1000M
    innodb_log_file_size=50M
    innodb_thread_concurrency=18

  • #2
    you need to determine qhich queries are creating the locks, and which are being blocked by them

    when the site starts to run slow, you can try

    SHOW PROCESSLIST
    and
    SHOW INNODB STATUS

    to get more feedback on which queries are performing badly.

    Then run an EXPLAIN on each one to see if they are running optimally.

    You can mix and match MyIsam with innodb, but personally i prefer to just convert everything to innodb and elimiate the resources needed to feed myisam

    Your innodb config may need tweaking, but Ill leave that to the other guys to look at

    Comment


    • #3
      Thanks for you tip carpii!
      I will try this then i get help for my .ini file.
      I have changed all back and wait for more feedback before i change back to innodb.

      Can anyone help my innodb config?

      Comment


      • #4
        Please, i realy need some help.
        Can someone help me to set good startvalues in my my.ini file?
        Then i get 1000 login users, sites get very slow..
        //Henrik

        Comment


        • #5
          Hello Henrik

          My advice is that you switch completely to InnoDB. Joins between MyISAM and InnoDB tables may hurt performance badly.
          Keep MyISAM for logging or archiving (if you do any).

          Your default values are OK, but I suggest that (after converting to InnoDB) you keep MyISAM key_buffer_size to a minimum (say 128MB)

          innodb_buffer_pool_size is typically 60-80% of *available* memory. I'm not a Windows guy so I don't know how good the values may be but under Linux, with a server dedicated to MySQL, I'll put the values to something like 2048M minimum. Your mileage may vary if you have other apps like IIS or Apache on the server.

          The log_file_size should be something like 25% of buffer pool size, so for your config it should be much larger (256M with the current values)

          Also I don't know the impact of InnoDB thread concurrency on windows but I would set it to 2 x number of CPU by default.

          Comment


          • #6
            I agree about moving entirely to innodb. You can virtually eliminate your myIsam resources and there are significant gains in concurrency to be made with innodb, at the expense of larger data files.

            Remember innodb doesnt support full text indices though, which might be a consideration. You could always use Sphinx though.

            The other thing you really need to do is study your process list as I mentioned earlier. If you have suboptimal queries then it might be that no amount of config tweaking will address the problem that your queries are just badly written.

            Find the slow queries, run EXPLAIN on them and then see if any indices can be added to help, or if the query can be rewritten.

            Comment


            • #7
              Thank you both for your help! nice!
              Now i think i can fill in some value correct. )
              Is it ok to keep myisam config values?

              I dont realy understund that you meen about this:
              " Remember innodb doesnt support full text indices though, which might be a consideration. You could always use Sphinx though. "
              That can happen?

              Comment


              • #8
                Yes, you can keep MyISAM config values. Know that it will allocate unnecessary memory if you don't use MyISAM then.

                About Full text indexes : They are not supported in InnoDB tables
                http://dev.mysql.com/doc/refman/5.0/...xt-search.html

                But if you don't use this function there's no problem to convert your tables. You could use MyISAM consolidated search tables and do a join, though. It will still work )

                Comment

                Working...
                X