Postgres is a true ACID compliant DB. MySQL is (in my opinion) a hack that kinda works. It wasn't even necessary when it was developed, since Postgres was already available and free and a true SQL DB.
Gerald
Very high disc write from MySQL destroying SSD.
-
geraldbrandt
- Posts: 132
- Joined: Tue Dec 09, 2014 10:20 pm
Re: Very high disc write from MySQL destroying SSD.
Gerald
Re: Very high disc write from MySQL destroying SSD.
So this may be of interest/related.
Last night I discovered that our ajax requests weren't passing along cookies, which means that every status update (1/second/monitor) was creating a new session.
Having fixed that, we may see a noticeable reduction in db usage when having lots of viewers.
Last night I discovered that our ajax requests weren't passing along cookies, which means that every status update (1/second/monitor) was creating a new session.
Having fixed that, we may see a noticeable reduction in db usage when having lots of viewers.
Re: Very high disc write from MySQL destroying SSD.
Great news. That might be the case here, because I make heavy use of the API.
With lots of DB Tuning and RAM extension I brought the Writing down by a lot. Seems there are many querys not using an index, resulting in temp tables for sorting. So you need to increase the max RAM for temp tables and also the max number of temp tables. This cut the writes by half, avoiding all the disc-temp-tables for sorting.
With lots of DB Tuning and RAM extension I brought the Writing down by a lot. Seems there are many querys not using an index, resulting in temp tables for sorting. So you need to increase the max RAM for temp tables and also the max number of temp tables. This cut the writes by half, avoiding all the disc-temp-tables for sorting.
Re: Very high disc write from MySQL destroying SSD.
I'd be curious to know which are not using indexes. I know that we get that logged when we turn on logging of queries without indexes, but it is generally loading the config table, which is us loading the entire thing, so no indexes required, and there is no WHERE clause so no temp tables should be required for that.
Re: Very high disc write from MySQL destroying SSD.
I found only how to log all Querys or Slow Querys. But not now to log "unindexed" or how to mark them in the logfiles. I think it is neccessarry to run explain on all potential querys to so see which ones are affected.
I can tell this only from the innodb performance counters reporting this. And also the tool "mysqltuner" gave this hint like listed here: https://wiki.zoneminder.com/MySQL
What would be the biggest gain, is to think if you really need transactions?
Its not a critical banking system with accounts, where multiple querys need to be a transaction.
Using MyISAM in MySQL is 10 Times faster and has way less writes.
And for decades it was enough data consistency for all applications on the internet.
Would be great, if "SSD Live concerend" users like me could just change the sceme from innodb to MyISAM and be happy.
But the Transaction brackets prevent this switch to MyISAM.
Currently its a single process per camera doing the inserts and updates on events for this camera.
So there can never be a conflict which makes transactions neccessarry, because there are never two processes working on the same data.
I can tell this only from the innodb performance counters reporting this. And also the tool "mysqltuner" gave this hint like listed here: https://wiki.zoneminder.com/MySQL
What would be the biggest gain, is to think if you really need transactions?
Its not a critical banking system with accounts, where multiple querys need to be a transaction.
Using MyISAM in MySQL is 10 Times faster and has way less writes.
And for decades it was enough data consistency for all applications on the internet.
Would be great, if "SSD Live concerend" users like me could just change the sceme from innodb to MyISAM and be happy.
But the Transaction brackets prevent this switch to MyISAM.
Currently its a single process per camera doing the inserts and updates on events for this camera.
So there can never be a conflict which makes transactions neccessarry, because there are never two processes working on the same data.
Re: Very high disc write from MySQL destroying SSD.
log-queries-not-using-indexes is the mysql config you are looking for.
If you want to use myisam, you can go ahead and convert your tables to it. We prefer it for row-level locking and other modern features.
Your assumptions about the need for transactions are not correct. Where we have used transactions, we have done so because they were required.
Now, our use of triggers has really screwed everything up, and we will be looking to remove them (because using them locks all referenced tables).
But that won't fix your write concerns.
If you want to use myisam, you can go ahead and convert your tables to it. We prefer it for row-level locking and other modern features.
Your assumptions about the need for transactions are not correct. Where we have used transactions, we have done so because they were required.
Now, our use of triggers has really screwed everything up, and we will be looking to remove them (because using them locks all referenced tables).
But that won't fix your write concerns.