MySQL Caching

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
progitto
Bear Rating Trainee
Bear Rating Trainee
Posts: 6
Joined: 04 Apr 2013, 12:42

MySQL Caching

Postby progitto » 03 May 2013, 14:38

I'm using ttrss on a linux virtual server (ubuntu server on Amazon EC2) with 512MB of RAM.
I want to reduce the disk IO of the mysql ttrss database.
Any hint to how I can setup caching or reduce the disk read/write operations?

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: MySQL Caching

Postby gbcox » 03 May 2013, 15:35

Google "how to clean sql cache"
You'll see several entries which tell you how to do it, some mentioned using cron...

User avatar
dxbi
Bear Rating Disaster
Bear Rating Disaster
Posts: 62
Joined: 16 Mar 2013, 13:44

Re: MySQL Caching

Postby dxbi » 03 May 2013, 15:42

Check this answer for information how to tune InnoDB with MySQL. http://dba.stackexchange.com/a/18384

progitto
Bear Rating Trainee
Bear Rating Trainee
Posts: 6
Joined: 04 Apr 2013, 12:42

Re: MySQL Caching

Postby progitto » 03 May 2013, 16:46

@gbcox can you tell me why is better to clean the query cache?

I'm starting to read some documentation about InnoDB while monitoring IO on my server.
Seems that the most of IO are due the feeds update, I'm going to try to increase the innodb_log_buffer_size value

AngryChris
Bear Rating Master
Bear Rating Master
Posts: 135
Joined: 08 Apr 2013, 02:42

Re: MySQL Caching

Postby AngryChris » 03 May 2013, 17:07

Disk I/O on an INSERT (or UPDATE) is something you want. That's what ensures new data remains available in the event of a database or server failure. It's I/O on a SELECT that you want to avoid. If you're experiencing high I/O during feed update cycles, that's normal and you really shouldn't be trying to prevent the database from committing the data to disk.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: MySQL Caching

Postby gbcox » 03 May 2013, 17:48

Sorry, I had misread your question.

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: MySQL Caching

Postby cqrt » 04 May 2013, 03:17

progitto wrote:@gbcox can you tell me why is better to clean the query cache?

I'm starting to read some documentation about InnoDB while monitoring IO on my server.
Seems that the most of IO are due the feeds update, I'm going to try to increase the innodb_log_buffer_size value


Let us know the results of your configurations, the high I/O usage is mildly irritating.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MySQL Caching

Postby fox » 04 May 2013, 08:47

Don't update feeds or access tt-rss in the browser, that would definitely solve the I/O usage.

progitto
Bear Rating Trainee
Bear Rating Trainee
Posts: 6
Joined: 04 Apr 2013, 12:42

Re: MySQL Caching

Postby progitto » 04 May 2013, 12:11

As AngryChris wrote the IO disk are most due the feed update, so many INSERT in the table.
For me ttrss is not a 'essential' service so it's not a big problem to lost some hours of database update.
I'm making a test using a ramdisk where I've placed the InnoDB cache files (ib_logfileX) obtaining this result
Image

hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Re: MySQL Caching

Postby hrk » 04 May 2013, 12:14

fox wrote:Don't update feeds or access tt-rss in the browser, that would definitely solve the I/O usage.


Image

Bonus points for the physical exercise?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MySQL Caching

Postby fox » 04 May 2013, 12:15

You definitely get this forum.

cqrt
Bear Rating Disaster
Bear Rating Disaster
Posts: 70
Joined: 05 Apr 2013, 00:37

Re: MySQL Caching

Postby cqrt » 04 May 2013, 17:22

progitto wrote:As AngryChris wrote the IO disk are most due the feed update, so many INSERT in the table.
For me ttrss is not a 'essential' service so it's not a big problem to lost some hours of database update.
I'm making a test using a ramdisk where I've placed the InnoDB cache files (ib_logfileX) obtaining this result
Image


Nice. Did you increase the size of your log files? Not that it's probably necessary now. While I realise you storing the innodb log files in ramdisk is for testing, you will eventually run into corruption issues with reboots and crash recovery. No?

Keeping the log files disk based, I'm experimenting on these innodb settings on a 512mb server to see if it makes a difference on I/O.

innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_buffer_pool_size = 128M

progitto
Bear Rating Trainee
Bear Rating Trainee
Posts: 6
Joined: 04 Apr 2013, 12:42

Re: MySQL Caching

Postby progitto » 05 May 2013, 17:39

With ramdisk no problem on reboot, mysql save the cache to disk, only with crash I will lost the data stored in cache.
I will try to use cron to force a periodically flush of the cache, but, I've I written before, for me is not a big deal to lost some hours of database update.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 11 guests