Database Performance Question(s)

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
User avatar
epyon_avenger
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 27 Mar 2013, 12:49
Location: A little place I like to call Earth.
Contact:

Database Performance Question(s)

Postby epyon_avenger » 25 Jun 2013, 06:12

I did a few searches around the forums and wiki to see if this had already been solved, but didn't find anything conclusive I can use, but if I missed the obvious I fully expect to face the bears.

That said, I have noticed that for most of the users of my install, there is little to no noticeable blips in performance on the server. However, myself and the other major user see quite a large hit whenever we're going through our feeds. I had figured it was just me being an idiot somewhere, so I have looked through the various performance tuning walkthroughs here and additional ones for NGINX + PHP-FPM (and APC too) along with PostgreSQL and MySQL (tried both dbs, just in case) but it still seems to manifest. More specifically, the issue is that myself and the other user have many many items to get through (upwards of 1200+ a day) and we usually sit down and do it all in one go (me at night, them in the afternoon) and whenever I look at the data on the system and in New Relic, there is a huge hit to the DB from rapidly browsing through entries (and thereby marking them as read).

I'm assuming, perhaps incorrectly, that every mark as read (or at least, batches of them) are what is hitting the database and so I was wondering if there is something I am missing somewhere that would make them either queue up more uniformly or batch up better so it doesn't slam as hard. I should note that the performance for users is still pretty ok because the server is beefy enough to take it, it just seems like the sort of thing I'd rather address before it becomes a noticeable issue as opposed to after.

Hints or tips would be great, if anyone has them. I can furnish more specific logs/configs/etc. if necessary as well.

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

Re: Database Performance Question(s)

Postby AngryChris » 25 Jun 2013, 08:40


User avatar
epyon_avenger
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 27 Mar 2013, 12:49
Location: A little place I like to call Earth.
Contact:

Re: Database Performance Question(s)

Postby epyon_avenger » 25 Jun 2013, 10:29

Appreciated! I will spin up a test instance with Postgres and see if that solves it.

Cheers!

joseph-mx
Bear Rating Disaster
Bear Rating Disaster
Posts: 68
Joined: 19 Oct 2012, 05:19
Location: http://www.mxhub.com/reader/
Contact:

Re: Database Performance Question(s)

Postby joseph-mx » 25 Jun 2013, 10:43

NGINX is useful if you are serving many images..

but i don't find it necessary for a reader..
you need php + caching.. to improve it.
SSD might even HELPS speed thing up. ;)

User avatar
epyon_avenger
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 27 Mar 2013, 12:49
Location: A little place I like to call Earth.
Contact:

Re: Database Performance Question(s)

Postby epyon_avenger » 25 Jun 2013, 10:49

I've got APC ramped up pretty well, and I tuned MySQL/Postgres according to the walkthroughs and ideas people had here (and elsewhere) though I didn't see the work_mem suggestion before for this.

I've got a decent RAID array on the system with its own cache, if I could afford a pile of SSDs for it I definitely would use them, heh. In the meantime, I haven't seen a lot of disk queue length, and I've already swapped to NOOP (it tested practically the same as Deadline for the normal workload) since the RAID controller handles caching and queuing on its own.

User avatar
epyon_avenger
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 27 Mar 2013, 12:49
Location: A little place I like to call Earth.
Contact:

Re: Database Performance Question(s)

Postby epyon_avenger » 25 Jun 2013, 20:38

So, the PostgreSQL changes that AngryChris recommended worked out fairly well, I still see some slowness in queries but it's not too bad. I spent the rest of the evening trying to replicate in MySQL/MariaDB.

From the default Ubuntu config on MariaDB I did the following to get somewhat similar results.

In /etc/mysql/conf.d/tt-rss.cnf I added...
innodb_thread_concurrency = 0 # Allows MySQL/MariaDB to auto adjust threads for number of CPUs
innodb_read_io_threads = 64
innodb_write_io_threads = 64
sort-buffer-size = 6291456 # Much larger than this and I saw slowdown in single/small queries, much smaller and I saw less benefit for the mass marks as read

In /etc/mysql/my.cnf I changed...
query_cache_type = DEMAND # Was commented out, uncommented since I do a lot or writes between my users and the daemon.

APC Settings are as follows...
apc.enable_cli=1 # Enables the updater daemon to take advantage of APC
apc.shm_size=32M # This seems ok for now, may bump to 64M if it fills up over the coming week.
apc.ttl=7200 # Seems like happy medium for now, will be watching the evictions and what-not.
apc.user_ttl=7200 # Seems like happy medium for now, will be watching the evictions and what-not.
apc.num_files_hint=2048
apc.max_file_size=10M # Seems ok for now, will keep watching to see if it needs more/less.

Additionally, as previously mentioned, I changed the IO Governor to NOOP.

I'll keep tweaking and see what else will push the latency down further.

justauser
Bear Rating Master
Bear Rating Master
Posts: 133
Joined: 23 Mar 2013, 20:37

Re: Database Performance Question(s)

Postby justauser » 25 Jun 2013, 22:31


User avatar
epyon_avenger
Bear Rating Trainee
Bear Rating Trainee
Posts: 21
Joined: 27 Mar 2013, 12:49
Location: A little place I like to call Earth.
Contact:

Re: Database Performance Question(s)

Postby epyon_avenger » 25 Jun 2013, 22:37

I had it set to "ON" before, but the MySQL performance breakdowns were highlighting that "ON" benefits high-read scenarios and "DEMAND" benefits high-write scenarios, since my issue was with many writes, I switched to "DEMAND".

That said, I didn't notice a significant difference either way from that variable change, so in the grand scheme of things it might not matter?

I'd have to figure out more tests to be sure.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 8 guests