Skip to main content ↑ ↓
Topic: Delayed Statistic Counters (Read 6761 times) previous topic - next topic
0 Members and 2 Guests are viewing this topic.

Re: Delayed Statistic Counters

Reply #16 –

Quote from: Joshua Dickerson – There's a big difference between 1500 users looking at the ElkArte stats and looking at the database connections. I guarantee you've never had 1500 concurrent connections to the database.
Thats right of course. i have a max of 15 concurent db connections ( hard set in my.cnf). And nginx / php-fpm is set to 12 childs.
Still - if 500 Users running around including spiders you will do everything to keep writes as fast as possible to avoid queuing up the requests.

Writes are fsync'd to disk - cache is only for reads.

Quote from: Joshua Dickerson – Removing the query makes a difference because the SQL analyzer is what's taking the most time.
Nope. Writes to Disk take more time than any Analyzer ( and Querys are cached and reused ) will ever use. I don't run on a ssd, i use standard hard disks.

Re: Delayed Statistic Counters

Reply #17 –

Quote from: Nifty – I delayed the writes once and ended up having corrupt databases which i had to rebuild to get back online.  That takes hours of manual work - if you around. Very bad if you are currently on vacation .... ;) )

Data needs to be flushed to Disk.
I'd like to understand the case where that setting to 2 can cause database corruption, its a potential data loss issue, yes, but corruption?

You will get better performance by setting the value to 2, but then you can lose up to one second worth of transactions should the system crash.  With a value of 2, only an operating system crash or a power outage can loose the last second of transactions.  Using a value of 0 you would add in that loss should mysql crashing, mysql crashing with a value of 2 is not different that the default value of 1. 

Quote i have a max of 15 concurent db connections
You are saying you have "max_connections" set to 15?  I really feel like you are hobbling your system doing that given the number of users you have.  Obviously I don't know your site situation, but 15 just seems really low.

Re: Delayed Statistic Counters

Reply #18 –

@Spuds
i used the "delay write" for MyIsam tables.
I have currently not need to delay anything since i switched the search to sphinx.
But - i am still running myBB. elkarte is what i am currently testing.
-
QuoteYou are saying you have "max_connections" set to 15?  I really feel like you are hobbling your system doing that given the number of users you have.  Obviously I don't know your site situation, but 15 just seems really low.
Current Page times are ~ 200-400ms. all reads come from cache. i only care about the writes going to disk. Thats my bottle neck.

Re: Delayed Statistic Counters

Reply #19 –

If you are now using Innodb now, then innodb_flush_log_at_trx_commit = 2 would be your friend. 

I have no idea on "delay write" for MyIsam.  For MyIsam using concurrent_insert = 2 simply means that insert will not search for an empty row in the table to insert into, but instead it simply stuffs it at the end.  This saves time at the expense of some table size.   Its not right for every application, but for forum querys it should be just dandy.  Also since there is a scheduled optimize table task those empty rows will get cleaned up as part of the standard maintenance so any size inefficiency is short lived.

I'm surprised that 15 connections could cause a disk I/O bound situation, I'm just having a hard time wrapping my head around that.  What are you using 5.25" floppy's :D :D  Is there something else that has bottle necked disk I/O other than the db?

Re: Delayed Statistic Counters

Reply #20 –

Quote from: Spuds – I'm surprised that 15 connections could cause a disk I/O bound situation, I'm just having a hard time wrapping my head around that. 
They cant. The 15 is just to make shure it will not happen. I prefer a "proxy did not respond in time" (max php childs -> 12 ;) ) to a not responding server.
But the server can currently  handle 1500 users at a time without any problems.
The trick was switching the search to sphinx & using nginx. Mariadb handles most situations better than mysql as well. And to many  sql connections can kill you easily.

But it seems you are more skilled at mysql internals than me. I would love to come back to you after running my performance test for elkarte if you don't mind. i will not change the settings for my current live system.

Re: Delayed Statistic Counters

Reply #21 –

Which tables? I think I can help you out with your performance a lot more without code changes ;)

You already see that it doesn't matter for spiders. With 15 concurrent database connections, some of those are probably just open threads that are cached, right? Even still, what's the likelihood that 15 people browsing your forum are looking at the same topic or accessing its row (read/write) at the exact same moment? That's the only thing that's going to cause your UPDATE vs INSERT to really matter. Do me a favor and benchmark the time it takes to run an INSERT and an UPDATE on your topics table. That 1/100 or 1/1000 of a second going to matter in the grander scheme? Meh, I'd say not.

Caching doesn't just matter for reads. It matters for writes where you have common rows, or in your case - because you're using MyISAM - tables, that have to wait on write locks to clear. That's not the point though. Do you care how fast a query is or do you care about how fast your page loads? First comes the page as a whole, then comes the care about what's causing it to take longer than desired.

You're right, writes to disk take the most amount of time, but in the difference between insert and update (with a fixed length row), I'd think that it is everything else involved in the query and not the write that makes the update take longer.

Re: Delayed Statistic Counters

Reply #22 –

What about stats - > a stats file of some sort.
Then a cron or task to update the db from the stats file every x seconds/minutes.

If the stats file was in the same location as the cache then folks that toss the cache into a tmpfs would be offloading even more io.

Re: Delayed Statistic Counters

Reply #23 –

Quote from: Spuds – Also since there is a scheduled optimize table task those empty rows will get cleaned up as part of the standard maintenance so any size inefficiency is short lived.
On a forum with 1500 users, I would hope you'd disable the optimize table. I once worked for a company where we had scripts that did 1000s of INSERT/UPDATE a minute (more like 100k) adding about 10MB to the size of the table every hour. I used concurrent_insert=2 and never once ran an OPTIMIZE as I would kill the server. A couple of times in the 3 years I worked there I did a server swap - swapping the master and slave while I did a full server dump so I could upgrade the servers. Hard to benchmark the difference on that though because I was radically changing the hardware each time.

The way I see OPTIMIZE is that if you can use it without hours/days of locked tables or running out of free RAM/disk, your tables are too small/low IO to matter. As a "scheduled task" it's a feel good thing IMO

Re: Delayed Statistic Counters

Reply #24 –

Quote from: Joshua Dickerson – On a forum with 1500 users, I would hope you'd disable the optimize table.
For shure - i have no idea how long that would take:
QuoteOur members have made a total of 831.946 posts in 112.897 threads

Re: Delayed Statistic Counters

Reply #25 –

I give up, really I do.

Re: Delayed Statistic Counters

Reply #26 –

@Spuds
Sorry if i made you upset, i did not mean it so. I am not a native speaker, my English is lacking the polished part.

Re: Delayed Statistic Counters

Reply #27 –

Quote from: SpeedFreak – What about stats - > a stats file of some sort.
That means moving hot spots from the database engine to the filesystem and handle it from there. I thing this will course more work without a real benefit after all. Inserting into a separate table is a very fast operation.

Re: Delayed Statistic Counters

Reply #28 –

Quote from: Joshua Dickerson – You already see that it doesn't matter for spiders. With 15 concurrent database connections, some of those are probably just open threads that are cached, right?
Currently
Quote449 users active in the past 30 minutes (172 members, 23 of whom are invisible, and 274 guests).
-
Table_locks_immediate   30609821
Table_locks_waited      13717
Tc_log_max_pages_used   0
Tc_log_page_size        0
Tc_log_page_waits       0
Threadpool_idle_threads 0
Threadpool_threads      0
Threads_cached  5
Threads_connected       1
Threads_created 6
Threads_running 1
Uptime  685523
Uptime_since_flush_status       685523
-
Generated in 166 ms (32.09% PHP / 67.91% MySQL)
SQL Queries: 17 / Server Load: 0.39 / Memory Usage: 10,75 MB
Stats from my mybb forum.

btw: i use innodb on all tables which are updated frequently.