0

I have a database most tables are in MyISAM and have checked MySQLTuner and tuning-primer to optimize MySQL server variables such as key_buffer_size, max_connections, open_tables limit,etc. Reads are 80% and writes 20%.

I have been having problems lately that sometimes data is not inserted. I have checked several times if it was a problem on how it inserts the data, such as weird characters, but not a problem. It seems somehow the database is so busy that inserts and updates fail, there are about 4 tables that are heavy read. But still, doing inserts on tables that rarely have reads, it is not possible to insert. I understand MyISAM does table locks but I don't see how it can affect other tables to fail to insert as well if those are not being read.

Do you have any suggestions what to do?

The main reason I use MyISAM is that it's a lot faster than the selects I do with InnoDB. The queries always use index and I run explain extended to make sure all is correct and that response times are quite low to avoid server load and piling up connections.

Appreciate your comments.

Rob Watts
  • 6,866
  • 3
  • 39
  • 58
user1361389
  • 55
  • 5
  • 3
    MyISAM doesn't provide any guarantee that it will write data to disk, that's why your inserts are failing. It's also not true that MyISAM provides faster selects compared to InnoDB. Long story short - use InnoDB. – N.B. May 09 '14 at 22:06
  • thank you , i have copy one of the tables and added as Innodb with the same data and then adjusted with below variables: innodb_buffer_pool_size=6G innodb_additional_mem_pool_size = 2M innodb_log_file_size = 256M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 innodb_thread_concurrency=8 But Myisam can answer in 1.3 seconds while in Innodb takes 11 seconds. I have also tried to defragment the table. any ideas? – user1361389 May 09 '14 at 22:58
  • Post the query and explain so we can analyse it. – N.B. May 09 '14 at 23:35
  • it's actually a stored procedure and quite a bit of code, i have done some adjustements and now seems as fast as Myisam. thanks i will keep exploring it and testing. – user1361389 May 09 '14 at 23:55
  • 1
    +1 for using InnoDB. It's debatable whether MyISAM or InnoDB are faster (hint: [it's InnoDB](http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/)), but regardless of performance, *not losing your data* should probably trump that. – Bill Karwin May 10 '14 at 01:14

0 Answers0