Hot answers tagged performance
5
The short answer is that you can't eliminate writes to disk, because InnoDB is doing its very best to make sure that it can recover if the server crashes.
I'd strongly recommend against migrating to MyISAM - a MyISAM table that's heavily written to is extremely likely to be corrupted if your server crashes, and you'll likely lose data (or need to restore ...
5
Leaving aside the fact that the DISTINCT is causing some weird behavior, there are two main reasons why insert times get longer as bulk loads get larger:
B-tree indexes get less efficient to update as they get larger and have more tree levels. So indexes take longer to insert a the millionth value than they did the 10th.
At certain sizes, you exceed ...
5
High CPU in SQL Server is very often caused by poor indexing
Unfortunately, SQL Server 2000 lacks the tools of later versions to track these down easily
Saying that, if you run SQL Profiler you will be able to find high CPU queries and start looking at query plans to work out what indexes are missing
5
Can partitioning the table help reduce the completion time?
No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.
If you want to improve performance you need to identify ...
5
You've got two different questions in here that I'll answer separately.
Q: Can the performance of one table suffer from other large tables in the same database?
Yes, during insert/updates/deletes. SQL Server only has one log file per database. When you insert/update/delete, your transaction doesn't complete until your data is written into the log file.
...
4
Assumptions
Since information is missing in the Q, I'll assume:
Your data comes from a file on the database server.
The data is formatted just like COPY output, with a unique id per row to match the the target table.
If not, format it properly first or use COPY options to deal with the format.
You are updating every single row in the target table or most ...
3
Conditions with OR are harder for the optimizer than conditions with AND only.
Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.
Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:
WHERE ( languageId = 3 AND
...
3
The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows
DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > ...
3
This (orders_products) is a many-to-many table. I think it's common to have 2 composite indexes on such tables as it helps in many common queries.
I would definitely add two (unique) indexes, on (orders_id, products_id) and on (products_id, orders_id).
Not sure if defining them both as UNIQUE would be a further improvement in MariaDB's optimizer.
And if ...
2
Asked if I could take a look and see what could be done, got a yes as answer.
That's very encouraging: you're in an excellent position to advance your career and learn something!
Some of the things you mentioned are problems, some not. I'll answer them briefly and follow with some broader advice.
procedure returns almost 2000 rows with lot of ...
2
Possible this be helpful for you -
;WITH thread AS
(
SELECT
th.ThreadID
, th.CreateTime
, th.ThreadTitle
, th.ThreadUrl
FROM dbo.Thread th
WHERE th.LanguageID = 58
AND th.TechnologyID = ''
AND th.BigintTime BETWEEN 20130101 AND 20130501
AND th.IsAnswer = 1
)
SELECT
t1.ThreadID
...
2
Based on the query you provided, I would say:
For date_add field, I would definitely recommend that you separate the date part and time part, as this will allow you to group by a field instead of a function.
Assuming that you will always be passing a id_website, I would recommend you create a composite index covering, and in this order: id_website, ...
2
I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column.
From the manual
Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper ...
1
If a lot of your queries have conditions similar to what you describe, e.g. range condition on the date column:
WHERE dateColumn >= (CURRENT_DATE() - INTERVAL 1 MONTH)
WHERE dateColumn >= '2012-01-01'
AND dateColumn < '2013-01-01'
it will be useful to define the primary key of the table as (dateColumn, tableAI), where tableAI is an auto ...
1
I have five(5) aspects to discuss here
ASPECT #1 : innodb_buffer_pool_size
You need to run this query
SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) BPSIZE
FROM information_schema.tables WHERE engine='InnoDB';
This will give you the ideal sized buffer pool because InnoDB caches data and index pages.
If the DB Server has 32G RAM, the ...
1
Over-committing CPU and memory on a VM can certainly give you false positives. The true performance bottlenecks are "hidden" from the OS, so you may not see it as easily as you would on a normal windows box. I have seen this countless times before, so we always recommend reserves on assigned resources, and where possible, dedicated LUNs for the SQL Server. ...
1
What concerns me fact that fieldC is not the lead column in the PRIMARY KEY.
What would be preferable is to reverse the order to the primary key columns
CREATE TABLE `my_table` (
`id` int(10) unsigned NOT NULL,
`fieldA` char(40) NOT NULL,
`fieldB` char(40) NOT NULL,
`fieldC` char(32) DEFAULT NULL,
-- some other fields
PRIMARY KEY (`fieldC`,`id`)
-- ...
1
You'll need to look at the wait type when the query is running. Odds are you need faster disks as building an index on a table that large is going to cause MASSIVE amounts of reads and writes.
In a nutshell you'll need to read the 120 Gig table, sorting it based on the clustering key (which is going to cause a ton of spill to tempdb writing probably 100 ...
1
If you concern is about performance and you don't care about loose a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables:
innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb)
innodb_log_file_size - choise a good value in here to optimize the i/o in your slave
...
1
You'll be averaging about a million records a year, which shouldn't be a problem given the table is fairly narrow. I don't see anything wrong with your design (other than a NULLable primary key?). You should be able to easily track when and how long each process takes by comparing timestamps. This is accomplished using a self join.
1
When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.
I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:
...
1
To speed up the execution of your query, you need to create a compound index:
ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);
More information: Multiple-Column Indexes
1
Are the performance gains from data consistency enough of a reason to develop in NoSQL, rather than relational?
I think your question presupposes a certain way of looking at things. Your fundamental tradeoff with NoSQL is a tradeoff between declarative ad hoc reporting and fast and loose inputs. This tradeoff is unacceptable for many (maybe most) ...
1
Ask the developers/architects who designed the solution. Only they can really tell. I can think of several reasons:
It could be that the databased was added at different times. First the main database, then they decided they needed a database for the events and last a place to store old data. Depending on environment it might be easier to create a new ...
1
I have recently discovered a fantastic free script from the people at BrentOzar Unltd
http://www.brentozar.com/blitzindex/
This does some good analysis of which indexes exist, how often they are used and how often the query engine is looking for an index that doesn't exist.
It's guidance is generally good. Sometimes it gets a bit over-suggestive of ideas. ...
Only top voted, non community-wiki answers of a minimum length are eligible
