New answers tagged performance
1
So I resolved this, turns out that power management features were enabled on our SQL server that were scaling the CPU frequency up and down, but not fast enough to keep up with the small demand and introduced the SOS_Scheduler_Yield wait. After changing it to run always in high performance the issue went away and now the waits are more normal (LatchIO type ...
1
Several points:
1) Store the data in two columns.
2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).
3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.
4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.
3) Once you do ...
1
My database is for an HR application with performance reviews and I am interested in whether I should index my foreign key.
Generally speaking, if the column will appear in a search condition such as a WHERE clause or a JOIN predicate, then it should probably be indexed. It depends on the exact queries that access the table.
In a greenfield project, my ...
4
It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.
However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm ...
1
Yes, it will make a difference. How big a difference will depend both on how many rows the table has and how many columns as well as how frequently the table is read from and updated. Depending on the database, the block size and how many rows are updated at the same time could also be factors.
Keep in mind the overhead you are introducing by splitting ...
1
Log file sync occurs when a commit is made and the redo buffer needs to be flushed to disk. The session has to wait for that to happen.
An increase in the number of log file syncs generally means that one of your developers has gone commit-happy, and is committing far too frequently -- every row, for example.
Here you probably have a process that performs ...
11
Short version: seek is much better
Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...
1
Others have defined well enough the differences between seek and scan. In this instance, your query itself and the execution planner should give you the information you need to see which values are used as predicates (filters) for the query in each part. Typically it's a good practice to always add non clustered indexes on foreign keys, and depending on the ...
2
If you wish to dig the subject, a very helpful book (at least for me) is SQL Server Execution Plans by Grant Fritchey, freely available at RedGate here.
If you have a query such as
SELECT *
FROM myTable
SQL Server will likely use an Index scan, as it needs to go through all the rows to display the required results.
On the contrary,
SELECT *
FROM ...
2
Generally, seeks are good, scans are bad.
Seeks are where the query is able to make effective use of the index, and use it to find the rows it needs.
Scans are where the query is looking through the whole index trying to find what it needs.
How does SQL choose? Deep in the internals of the query optimiser, the decision is made based on your query and the ...
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 ...
0
Triggers are problematic because they tend to get forgotten about. I only ever use triggers for auditing purposes such as updating a "last updated datetime" column. Stored procedures and functions can be used at any time since they are called directly by processes wishing to modify data -- as opposed to triggers that are fired as the result of data being ...
0
If you haven't done this before then the best thing you can do is to hire someone who has been there, done that and has the battle scars to prove it. Just to become familiar with the complexity that can go into a performance test you should take a look at the industry documentation for the defacto performance benchmark for databases, TPC-C
PDF ...
0
Can partitioning the table help reduce the completion time?
Yes - if you have sufficient CPU and IO resources to achieve query parallelism and partition the table on a key that will result in a set number of partitions having a roughly equal number of rows in them at all times. This approach uses a principle of spreading the workload across the partitions ...
0
You asked for suggestions. Here's one.
If I were trying to analyze this case, the first thing I would do is separate it out into two separate DELETE statements, one to cover the IS NULL case, and the other to cover the value_was < Value_now case. See whether they both run slow. I expect the IS NULL case to do a table scan, unless indexes work ...
0
You could try to use EXISTS clause instead of IN, since its usually less performance expensive:
DELETE FROM TABLE T_delete
WHERE EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was IS NULL
AND T_delete.ID1 = t_join.ID1)
OR EXISTS (SELECT 1
FROM TABLE t_join
WHERE value_was ...
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
0
You might improve performance with partitioning. You will need to experiment on a development server and see if it is something that can benefit you.
You may improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more ...
2
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 ...
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
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 ...
1
I Don't know your database but hope maybe this will help
I setup some test data so I have an orders_products table with 27,000 rows populated like this so I have each order with three random items from a possible set of 10
Create Table orders_products (Orders_Id Number, Products_Id Number);
create index orders_products_I1 on orders_products(orders_id);
...
0
The first operation looks expensive, with the warning signs Using temporary; Using filesort. Consider adding an index on orders_products that allows you to search on products_id:
create index IX_OrdersProcucts_ProductId
on orders_products(products_id, orders_id);
By adding orders_id in addition to products_id, the index becomes covering for:
SELECT ...
0
For time being ensure you have more connections set to max_connections globally.
`SET GLOBAL max_connections = 1024;`
Consider below points before you conclude anything.
For every 5 minutes you have a new thread coming so ensure that you have your application effeciency to complete in seconds unless it is really a time consuming process.
Check what ...
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, ...
0
It is better for music, books and ... Consider a table. And use of natural interfaces, such as user-music table(UserId,MusicId,UserMusicId). It will remain empty fields often move to another intermediate tables.
Fields containing basic information of user insert in user main table and For more information, Favorites, and ... use of Interface Tables for ...
2
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. ...
0
The .mdf extensions doesn't really matter and the same case with .ndf/.ldf extensions as well.
Primary file/secondary file/log file can have any extension (something like .xyz)
And NO it is not SQL Server that created multiple data files.
For further clarity please refer here:
Can a Database have more than one mdf File?
If you want to delete the extra ...
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
...
0
Most importantly, the main reason why you need to put it in a variable is if you want the exact same value (GETDATE() at that exact time) to be used elsewhere on the code. Otherwise, the performance difference of it is really a moot point.
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 > ...
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 ...
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 ...
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. ...
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 ...
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`)
-- ...
0
I had similar problem with MySQL 5.1, you can see in here Innodb Slow queries since convert from MyISAM
before start, have a look on This post, it will help you to understand how to configure your innodb Log checkpoint and dirty Buffer pool pages
I suspect that you are running MySQL 5.1 on innoDB buld-in version, if possible, try to install the innodb ...
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 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 ...
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 ...
1
You just need to optimize for InnoDB environment. innodb_buffer_pool_size is still the key for performance.
And beacuse your slave server is weak than master, you might need to deal with replication lag. (i.e. Write to master, but cannot read data from slave immediately)
0
To increase the speed of any SQL command you should have a properly set up database, thus I do hope that your database is stored on a different disk and that the master and tempdb are on their own disk.
That being said there are several factors that affect index creation: if the table is sorted already, and since it looks like you are building this on a ...
1
If the data can be made available in a structured file you could read it with a foreign data wrapper and perform a merge on the target table.
0
Here are a few things to evaluate:
Turn on data compression: it looks like you're IO bound and have CPU to spare. Data compression might be a good trade-off here.
Turn SORT_IN_TEMPDB on. This can drastically improve IO patterns (more sequential IO, and less fragmentation in the final index).
Build the index into a fresh (presized) filegroup. A fresh ...
0
Each time when you create/recreate cluster index, server starts to order pages, and this is quite resource-demanding procedure. Your table is a large one. I'd advise you to divide your table in several smaller tables (i.e. to perform data normalization), if it is possible. Or you can create an empty copy of this table, add cluster index on empty table, ...
0
I think that what you need to use is a Nonequi join
select *
from
table_a,
table_b
where
table_a.id1 = table_b.id1
and table_a.id2 = table_b.id2
and table_a.evnt_sec BETWEEN table_b.evnt_sec -2 and table_b.evnt_sec +2
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.
0
Even with all the changes you have implemented in my.ini, I would like to suggest just one more:
I noticed you did not set innodb_buffer_pool_instances.
According to the MySQL Documentation, this is what innodb_buffer_pool_instances is for:
The number of regions that the InnoDB buffer pool is divided into. For
systems with buffer pools in the ...
Top 50 recent answers are included
