7,522 reputation
1429
bio website
location
age
visits member for 3 years, 3 months
seen 10 hours ago

20h
answered Advanced design question related to compound primary keys
1d
revised Get first login-Hit on huge table
added 45 characters in body
1d
answered Get first login-Hit on huge table
1d
comment Sybase9, not using full system capacity
Many things are not SSD aware and are optimised for spinning metal based drives only. With traditional drives often trying to perform parts of a single bulk operation like this will make it significantly slower than a single thread because of all the extra head movements involved. This isn't an issue with SSDs (well, much much less of one) but query/other planners don't optimise for this yet. They no doubt will soon, but for now if you need the extra parallelism you'll need to force it by breaking your process down manually and running several steps together.
Apr
10
comment MySQL vs. Microsoft Access
As Steve indicates, if you are targeting a Windows environment only then SQL Express is an excellent choice: it is essentially the full core DB engine of SQL Server with a few limitations applied and some tweaks to optimise it for small loads where the real thing is optimised for large many-user loads, so you skip some of mySQL's key limitations and have an easy upgrade path to full SQL Server if you need to scale later. For cross-platform needs both SQL Express and Access "fail" of course.
Apr
9
comment Sybase9, not using full system capacity
That isn't "strange": both CPUs have four cores and you have one single-threaded CPU-bound operation. 1 is 25% of 4. If you were on a two core machine you would likely see ~50% instead.
Apr
8
answered Should I include as much information as possbile to help the optimiser?
Mar
23
revised What is the best way to upgrade a MySQL version in a Linux Server
added 16 characters in body
Mar
22
answered What is the best way to upgrade a MySQL version in a Linux Server
Mar
21
comment Aliases with correlated subqueries
It is not required here, though it does not harm. If the inner query were more complicated then having something aliased to c inside and the overall derived table referred to as c too causes potential confusion (like this) so should be avoided.
Mar
20
comment Very high I/O on database during a backup maintenance plan
You might not be imposing a large impact on the SAN overall, but that doesn't necessarily mean I/O contention is not your problem. I'm assuming that you have a gbit link to the SAN (at 100mbit your backups/restores/checkDBs will be very slow and will badly affect anything else on your VM(s)) but even at gbit there will be latency issues and massive processes like a large backup or checkDB will slow down other processes that try to access the data files. Increasing the RAM allocation for your VM may help as this means it will need to hit the SAN less in normal operation.
Mar
19
comment Understanding Backup Types
Definitely consult with the business. You should have both an acceptable service restore window (which affects more infrastructure than just the database itself) and an acceptable data loss window. These will vary greatly depending on application and client: some of ours have a 4 hour restore window with maximum 24 hour data loss, in which case a daily tested backup is enough but more won't hurt, some are 15 minutes and an hour respectively. Some applications have finer requirements than that. Also consider both offsite and offline backups for disaster recovery purposes.
Mar
17
comment Nginx with Node.js server specifications and thoughts
Unfortunately the most likely answers you'll get from this is "you'll need too benchmark it and see" as there are a great many variables that will depend on the specifics of your code and storage structures.
Mar
13
answered Need a faster method to concatenate rows into column(s)--currently using CTEs
Mar
12
answered Triggers After Insert and After Update - Good Practice
Mar
10
comment Why is setting open_files_limit making MySQL slow?
Much more detail should be provided on questions like this. What did you set the value to? What specification of machine/VM are you using? Is it slow to start, slow to run a particular short of query, slow to run any statement, what? Any comparative stats like "this statement took X.xx seconds before but Y.yy after" and so on.
Mar
7
comment how many 'text' column a table could have in mysql? I have this error “Row size too large (> 8126).”
Are you getting the error when creating the table, when altering the table structure, or when inserting/manipulating rows? If the table created OK provide the results of running SHOW CREATE STATUS YourTableName to show what you currently have including what storage engine the table is using. Things like this will vary by storage type.
Mar
6
comment Best approach on storing/displaying sanitized data
Removing things that should not be there is different to encoding things that should/could be there but need protection against being used for injection and other problems. If tags should not be there remove them on the way in, or better still give the user a message and refuse to save until they provide valid input. If tags are valid then keep the input as-is in the DB and verify+escape as needed on the way out.
Mar
6
answered Best approach on storing/displaying sanitized data
Mar
6
answered Sybase9, not using full system capacity