|
Zombywuf posted:Is the 4gb including buffers? i.e. is the free stat at 12gb? Backup and restore, but ... why? The system's working fine. I'm just curious why it's suddenly reporting a 16 load (which implies that something, and that something is almost certainly mysql, is now running on all 16 cores) rather than the 1 load it used to. The DB has not appreciably changed in size over the last two weeks, and its drive is still more than 50% free. Memory: free reports total 15933, used 13689, free 2243, buffers 610. Swap 16378, used 152, free 16226. One issue we did have is that, for some reason, load spiked to 19, and a bunch of zombie connection showed up on MySQL and both slaves. This usually happens when things get too slow, restarting mysqld dumped them but didn't dump the 16 core usage. I should turn it off entirely tonight and see what happens to load.
|
# ¿ Oct 8, 2012 19:02 |
|
|
# ¿ May 14, 2024 17:48 |
|
I forgot to point out that it appears all of our scheduled processes finished much faster than usual over the weekend. My theory is still that MySQL decided to notice there were 16 cores and use them, my question is, why did it take two weeks? edit: lol there may be a far more mundane solution to this, due to a LAN config issue we have a bunch of processes taking up 0% CPU yet appear to be counted in the load number Golbez fucked around with this message at 20:54 on Oct 8, 2012 |
# ¿ Oct 8, 2012 19:24 |
|
Zombywuf posted:Are processes waiting on network IO counted in the load number? It would seem so, because I'm able to reliably increase the load number by loading df, which then freezes when it tries to display info on a network share.
|
# ¿ Oct 8, 2012 22:25 |
|
fletcher posted:I've been using the WEEK(date, 3) function in MySQL to aggregate daily data into weekly data, which from what I understand will follow the ISO-8601 definition of a week number. Something like ... "SELECT IF(DAYOFWEEK(date) = 7, WEEK(date, 3) - 1, WEEK(date, 3)) AS week_number" ? And use that idea to populate where clauses and the like.
|
# ¿ Oct 13, 2012 01:31 |
|
fletcher posted:I like the simplicity of this...but it seems too good to be true. Anything involving dates and times inevitably makes my brain hurt. I mean, with a standard for week numbering like ISO-8601, should anything else really be used in the business world? Though I just realized you'd have to account for the first week of the year; don't want to deal with a 0 week.
|
# ¿ Oct 18, 2012 01:02 |
|
And for MySQL all you need, I believe, is DATE(date_column).
|
# ¿ Oct 23, 2012 14:25 |
|
Try adding FIELDS TERMINATED BY ' ' to it.
|
# ¿ Oct 25, 2012 19:31 |
|
Goat Bastard posted:Not in Oracle. || is the concatenation operator, and NULL || 'any string' returns the string.
|
# ¿ Nov 15, 2012 15:54 |
|
Users table: user_id user_name [other user info] Skills table: skill_id skill_name [other skill info] Join table: user_id skill_id This lets you define each user and each skill once, and in the linking/join table you can have as many entries as you want. So if user 1 has skills 3, 7, and 10, there would be three rows in that table for him. So, to find out what skills someone has, you would query: SELECT skill_name FROM skills LEFT JOIN user_skills USING (skill_id) WHERE user_id = $i
|
# ¿ Nov 28, 2012 23:36 |
|
The syntax error is that it has INNER JOIN inventory_trans but never says which column to join on.
|
# ¿ Dec 12, 2012 23:41 |
|
MySQL: We have a table with start and end dates, and we want to be able to query this to collapse it into a single range. For example: Row1: Start date 2011-01-01, end date 2011-12-31. Row2: Start date 2012-02-01, end date 2012-03-31. Row3: Start date 2012-04-01, end date 2012-12-31. Row4: Start date 2013-01-01, end date null I want to be able to query for today and say, "Get me the range that this record is active." Which would be Feb 1 2012 through present (rows 2 through 4), but I don't just want to say "min(start_date) to max(end_date)" because that would include Row1 and thus the inactive range between Jan 1 and Jan 31 2012. Is this possible to do in MySQL?
|
# ¿ Feb 1, 2013 16:19 |
|
I'm working on storing all iterations of something, for record keeping. So I have a record with an ID, information on the record, and finally start_date and end_date. Any time a change is made, what will really happen is a new record will be made with the new information and a start_date of NOW() and a null end_date. Once made, apart from the end_date, a record should never be altered. But then I was thinking - these records aren't designed to be deleted or ended. There will never be an "end_date". The current record's end_date will always be null. The previous record's end_date, therefore, will always be the next record's start_date. So I wondered: Do I even need an end_date column in this case? To get the current record I could just do SELECT ... WHERE NOW() > start_date ORDER BY start_date DESC LIMIT 1, right? I don't need to do ... WHERE end_date IS NULL. And, likewise, if I was trying to find what it was like on January 1, I would run ... WHERE start_date < "2013-01-01" ORDER BY start_date DESC LIMIT 1 instead of ... WHERE start_date < "2013-01-01" AND COALESCE(end_date, NOW()) > "2013-01-01". Right? An additional question: should the creation of these records be handled in the PHP code, or in a MySQL trigger, and all the code thinks it's doing is running an update? Also, to answer an earlier response: Aredna posted:What version of MySQL are you using? Do you know which window functions it supports (i.e. row_number, rank, lag, lead)? I'm normally an MS SQL guy, but the general idea here is that you want find a way to compare each row to the prior or next row and determine if they are in the same group or not. After this you'll filter for just the group that matches the current group and find the min startdate from there and just use getdate() as your enddate since it's the current row. Version 5.5. I don't know much about the window functions. One of my cocoders found a snippet that might be able to work it but I haven't tested it yet.
|
# ¿ Feb 5, 2013 16:44 |
|
hewerman posted:Are you worried about storage or performance? I guess my question is why remove end_date if it doesn't greatly impact performance or storage. Will this data every be shown in a report? For example say you want to show each value of an iteration and the dates it was active. Displaying it in a report the way you are suggesting will require some extra query gymnastics to get the range, where as if you just leave the end date in it would be simple. I like clean data. It has nothing to do with space or performance and everything to do with having a clean schema that won't be confusing or won't become potentially inconsistent. Good point, though - it requires more than a simple query to find the whole date range a record is active.
|
# ¿ Feb 7, 2013 18:53 |
|
Beelzebozo posted:I normally recommend against separately storing an end_date in cases where a set of date ranges should form a full cover, such as an audit of a status change. Not because of any storage redundancy or performance concern, but that just means that humans have to maintain the full cover property and opens up the possibility of somebody creating a situation where there are overlapping ranges or parts of the time series not covered by any range, which creates an unknown semantics scenario that software or reports might not expect. That was my thought as well; it's highly unlikely but possible that the end date of one record and the start date of the next record become out of sync, or what not. This is being used to store iterations of account information, so there will never be any overlap and never any gaps. Make a change, a new record gets inserted, maintaining a solid audit trail.
|
# ¿ Feb 7, 2013 18:57 |
|
Beelzebozo posted:You have no need to store an end_date. The end_date for each row can be presumed to be the day prior to the begin_date of the next row, or "current" if there is not a next row by date for that key. Is there an easy way, then, to get the range for a particular row in one query?
|
# ¿ Feb 7, 2013 20:38 |
|
Pardot posted:Check out window functions, you can use them to easily roll up the next record. What are my options within MySQL?
|
# ¿ Feb 7, 2013 20:54 |
|
|
# ¿ May 14, 2024 17:48 |
|
So what you're saying is, since I'm on MySQL, I should include an end date.
|
# ¿ Feb 7, 2013 22:32 |