Tuesday, October 1, 2013

[how to] How to arrange rows selected in MYSQL query?

[how to] How to arrange rows selected in MYSQL query?

How to arrange rows selected in MYSQL query?

Posted: 01 Oct 2013 08:46 PM PDT

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) .

I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A where PK_ONE in (2,88,3999,4,282,33399,1,394);

When I'm using dataset mentioned above in PHP cursor; I want to ensure that data fetched comes in the sequence of values of "PK_ONE" in WHERE clause.

Can you please give me some direction to achieve same? [Currently rows are coming in asending order]

Looking for JET Blue (Extensible Storage Engine - ESE) administration tool

Posted: 01 Oct 2013 06:44 PM PDT

I am looking for database administration/viewer/editor tool for JET Blue / ESE databases - basically, any database using esent.dll. Something similar to HeidiSQL for MySQL. I would expect Google would return quick results especially as it is built into Windows but I could not find any tool for that.

Can anyone recommend any (free or open source if possible) tool for that? Perhaps some of the other tools that support other database formats have an ESE "driver". Thanks in advance.

Query with PIVOT fails with second LEFT OUTER JOIN to same table

Posted: 01 Oct 2013 06:02 PM PDT

I have a big query that contains a PIVOT and several LEFT OUTER JOINS. I need to add a second LEFT OUTER JOIN to a table that is already joined in the query. When I do this the query errors and I'm not sure why.

It appears to me that the PIVOT does not like two left outer joins to the same table.

HERE is a SQL Fiddle Schema Build for reference I tried to create a minimal sample for this question, the SQL statement below will not run in SQL Fiddle however so I dont know how much help this is.

Here is the current query that works as expected.

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;  Set @startno = 1;  Set @endno = 104;        WITH DateRange(dt) AS          (              SELECT CONVERT(int, @startno) dt              UNION ALL              SELECT dt+1 FROM DateRange WHERE dt < @endno          )  SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE               WHEN dt <= 52                  THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3))))                  ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3))))          END)) FROM DateRange          FOR XML PATH(''), TYPE          ).value('.', 'NVARCHAR(MAX)')       ,1,1,'')      OPTION (maxrecursion 0);      set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, ABALPH as VendorName, IBBUYR as BuyerNo,  ' + @cols + '  FROM      (      SELECT LIITM, IBVEND, ABALPH, IBBUYR, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo       FROM F41021        LEFT OUTER JOIN F4111          ON F41021.LIITM = F4111.ILITM         LEFT OUTER JOIN F4102      ON F41021.LIITM = F4102.IBITM AND F41021.LIMCU = F4102.IBMCU         LEFT OUTER JOIN vwF0101      ON F4102.IBVEND = vwF0101.ABAN8         ) d  PIVOT  (      SUM(ILTRQT)      for WeekNo in (' + @cols + ')  ) piv  GROUP BY LIITM, IBVEND, ABALPH, IBBUYR,   '+ @cols + ''    execute(@query)

Sample output is along the lines of this (heavily trimmed for readability):

ShortItemNumber   |   VendorNo   |   VendorName        |   BuyerNo   |   2012-Week1   |   2012-Week2   |   2012-Week3  -----------------------------------------------------------------------------------------------------------------------  140               |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80049             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80071             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL       80072             |   32560      |   Bill's Auto Parts |   971494    |     NULL       |      NULL      |      NULL

The second query, I add a second join to the vwF0101 table to retrieve another alpha name for a userId (IBBUYR).

DECLARE @cols AS NVARCHAR(MAX), @isnullcols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX), @startno as int,@endno as int;  Set @startno = 1;  Set @endno = 104;        WITH DateRange(dt) AS          (              SELECT CONVERT(int, @startno) dt              UNION ALL              SELECT dt+1 FROM DateRange WHERE dt < @endno          )  SELECT @cols = STUFF((SELECT ',' + QUOTENAME((SELECT CASE               WHEN dt <= 52                  THEN (cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-Week'+(cast(dt as varchar(3))))                  ELSE (cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-Week'+(cast(dt-52 as varchar(3))))          END)) FROM DateRange          FOR XML PATH(''), TYPE          ).value('.', 'NVARCHAR(MAX)')       ,1,1,'')      OPTION (maxrecursion 0);      set @query = N'SELECT TOP 5 LIITM as ShortItemNumber, IBVEND as VendorNo, vendor.ABALPH as VendorName, IBBUYR as BuyerNo,  buyer.ABALPH, ' + @cols + '  FROM      (      SELECT LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH, ILTRQT, cast(DATEPART(yyyy, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(4))+''-Week''+cast(DATEPART(ww, DATEADD(DAY, ILDGL % 1000, DATEADD(YEAR, ILDGL / 1000, -1))) as varchar(2)) WeekNo       FROM JDE_Production.PRODDTA.F41021        LEFT OUTER JOIN JDE_Production.PRODDTA.F4111          ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4111.ILITM         LEFT OUTER JOIN JDE_Production.PRODDTA.F4102      ON JDE_Production.PRODDTA.F41021.LIITM = JDE_Production.PRODDTA.F4102.IBITM AND JDE_Production.PRODDTA.F41021.LIMCU = JDE_Production.PRODDTA.F4102.IBMCU         LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as vendor      ON JDE_Production.PRODDTA.F4102.IBVEND = vendor.ABAN8         LEFT OUTER JOIN JDE_Production.PRODDTA.vwF0101 as buyer      ON JDE_Production.PRODDTA.F4102.IBBUYR = buyer.ABAN8        ) d  PIVOT  (      SUM(ILTRQT)      for WeekNo in (' + @cols + ')  ) piv  GROUP BY LIITM, IBVEND, vendor.ABALPH, IBBUYR, buyer.ABALPH,  '+ @cols + ''    execute(@query)

These are the error messages I received. It seems to me the PIVOT doesn't like me referencing the ABALPH column twice, even though I have it aliased separately. 

Msg 8156, Level 16, State 1, Line 20  The column 'ABALPH' was specified multiple times for 'd'.  Msg 8156, Level 16, State 1, Line 24  The column 'ABALPH' was specified multiple times for 'piv'.  Msg 4104, Level 16, State 1, Line 25  The multi-part identifier "vendor.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 25  The multi-part identifier "buyer.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 1  The multi-part identifier "vendor.ABALPH" could not be bound.  Msg 4104, Level 16, State 1, Line 1  The multi-part identifier "buyer.ABALPH" could not be bound.

Why do I have hundreds of connections open

Posted: 01 Oct 2013 04:44 PM PDT

I just had a transient issue with a development server. So this isn't urgent.

I had a "successful" restore that wasn't

So as part of checking I ran sp_who and found 307 open connections 2 minutes later when I ran it again there were 77 open connections

But what I saw causing the connections was that there were 3 connections open from the SQL Server host using the NT Authority account to every single database on the server.

I've never seen that before, and having spent 10 minutes playing with Google I can't see anything that looks like an explanation, so I was wondering if anyone knew what was going on.

How do I determine the licensing cost of Oracle Standard Edition for HyperV instances? [on hold]

Posted: 01 Oct 2013 02:02 PM PDT

I'm confused by Oracle's licensing for their Standard Edition.

My understanding is that licensing for the Standard Edition is determined by the number or physical processors (actual chips, not cores) available in the host machine. For example if I have a host box with 8 processors and I have 4 virtual machines running on it, Oracle's documentation says that each of those processors counts toward the license total regardless of how many are allocated to the virtual machine running the database.

The part that confuses me is that Standard Edition is limited to 4 processors. Does this mean that I'd have to pay for 4 processor licenses, 8 processor licenses, or not be allowed to install Oracle Standard on such a box because it has too many processors?

MDX: How do I combine/merge/union two Measures dimensions with calculated members?

Posted: 01 Oct 2013 12:54 PM PDT

I am trying to combine two dimensions in an MDX query. 

SELECT   NON EMPTY  (      [XXX].[YYY].children,  ) ON ROWS,  NON EMPTY  {      [Measures].[Dimension1A],      [Measures].[Dimension1B],      [Measures].[Dimension2A],      [Measures].[Dimension2B]  } ON COLUMNS  FROM [RePro]  WHERE  (      [ABC].[ABC].[Val]  ) CELL PROPERTIES VALUE

This produces results where all the [Measures] dimensions are in separate columns. How can I make it so that [Dimension1A] and [Dimension1B] are combined/merged into the same column, and likewise for [Dimension2A] and [Dimension2B]?

I've tried the solution here, but I get '#Error' in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimensions". Any ideas?

Thanks :)

Why is there a difference between these transaction DMVs in SQL Server 2008 R2?

Posted: 01 Oct 2013 02:45 PM PDT

When I execute the two queries below, 

SELECT       session_id, transaction_id      FROM sys.dm_tran_session_transactions;

AND

SELECT       session_id, request_id, at.transaction_id      FROM sys.dm_tran_active_transactions at          JOIN sys.dm_exec_requests r              ON r.transaction_id = at.transaction_id;

I've read the BOL for both 1 and 2 but don't see any clear explanation as to why the difference would occur.

I get different results. The former query returns no results, but the latter returns active transactions with session and transaction ids. The request_id is 0 which, I think, means that it's the only request made by the session. Could someone help me understand why there is a difference between the two concepts I've queried above?

EDIT

I just reran the queries and now I get a result for the first DMV which has a session_id that is not actually contained in the second result set.

How to use column name 'use' in SQL Server [on hold]

Posted: 01 Oct 2013 01:52 PM PDT

I have a database that I migrated from MySQL to SQL Server. One of the table's column names is use. How can I write my insert statement to not throw an error because of it?

I'm getting the error:

Msg 156, Level 15, State 1, Line 4830
Incorrect syntax near the keyword 'use'.

My insert looks something like:

insert into foo (id, use, some_other_attrs)   values(1, fun, other_stuff);

Joining on a field with different values

Posted: 01 Oct 2013 06:22 PM PDT

I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule program gives times as BREAK1, BREAK2, BREAK3, and LUNCH, while the tracking program simply lists them as Lunch and Break. I can join the data and get the lunches just fine, but the breaks are throwing me off. If I convert BREAK1, BREAK2, and BREAK3 to just "Break", I end up with too many segments because it is matching every instance with every other instance. Is there a way that anyone can think of to join these two pieces of information?

Here is some sample data:

This is the Scheduled Times:

EMP_ID  NOM_DATE    SEG_CODE    START_MOMENT    STOP_MOMENT  626009  26-Sep-13   BREAK2          9/26/13 5:00 PM 9/26/13 5:15 PM  625650  26-Sep-13   BREAK2          9/26/13 4:30 PM 9/26/13 4:45 PM  638815  26-Sep-13   BREAK2          9/26/13 4:00 PM 9/26/13 4:15 PM  621649  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM  567005  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM  626009  26-Sep-13   LUNCH           9/26/13 2:30 PM 9/26/13 3:30 PM  625650  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM  638815  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM  621649  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM  567005  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM  626009  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM  625650  26-Sep-13   BREAK1         9/26/13 11:30 AM 9/26/13 11:45 AM  638815  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM  621649  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM  567005  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM

This is the Actual Times

EMP_ID  Seg_Code    Start_Time  Stop_Time  625650  Break           9/26/2013 17:54 9/26/2013 17:55  567005  Break           9/26/2013 14:56 9/26/2013 14:59  567005  Break           9/26/2013 15:32 9/26/2013 15:44  638815  Break           9/26/2013 16:34 9/26/2013 16:47  567005  Break           9/26/2013 10:08 9/26/2013 10:21  626009  Break           9/26/2013 17:01 9/26/2013 17:15  625650  Break           9/26/2013 11:31 9/26/2013 11:45  626009  Break           9/26/2013 11:52 9/26/2013 12:07  621649  Break           9/26/2013 9:34  9/26/2013 9:48  621649  Break           9/26/2013 15:31 9/26/2013 15:45  638815  Break           9/26/2013 11:46 9/26/2013 12:02  625650  Break           9/26/2013 16:35 9/26/2013 16:51  567005  Lunch           9/26/2013 12:31 9/26/2013 13:29  625650  Lunch           9/26/2013 13:31 9/26/2013 14:30  626009  Lunch           9/26/2013 14:31 9/26/2013 15:30  638815  Lunch           9/26/2013 13:31 9/26/2013 14:30  621649  Lunch           9/26/2013 12:31 9/26/2013 13:30

I am trying to get the difference (in minutes) between when they are scheduled, and when they are actually taking breaks. A correct example is:

Badge   Seg_Code    Scheduled Start     Scheduled Stop      Actual Start           Actual Stop      Difference      Seg_Duration  192329  Lunch       9/26/13 8:15 AM     9/26/13 9:15 AM     9/26/2013 8:18:27 AM    9/26/2013 9:17:59 AM        3       0:00:59:32

Thank you

Restrict range of dynamic ports available to SQL Server

Posted: 01 Oct 2013 11:33 AM PDT

Is there a way to limit the range of dynamic ports available to SQL Server from the database side, or database server side? Our SOP is to use static ports across the network firewall and a vendor is having trouble locking down their ports. Theoretically, if we allowed a range of 1000 ports inside the dynamic range (49152–65535) across the firewall how would I limit SQL Server to only assign a dynamic port inside that range?

If a transaction is "committed" , is it then saved for sure?

Posted: 01 Oct 2013 12:13 PM PDT

If a transaction is committed successfully, can I then be 100% sure that it has been written to the database AND to the log files? Can I be sure that the data is SAVED ?

Today our log files had reached the limit of the drive and we got a lot of errors. Furthermore, some other services crashed. We increased the disk and restarted the server.

During startup, the server did a "database recovery" -- can I be sure that everything is okay again ?

Trigger and temp table or materialized view

Posted: 01 Oct 2013 11:01 AM PDT

I'm looking for the better/best solution to compare data in a table before update and after. This can be done with trigger and temp/global temp table or with read only m-view as far as I know. Would like to ask DBA community which solution is better, more efficient etc... The table is not big, less then 1000 rows. It may grow in the future, but not much. Which is better for big tables, with million(s) rows please? Thank you all.

Is MSDTC required for SQL Server 2012 Fail Over Cluster?

Posted: 01 Oct 2013 11:21 AM PDT

I am making a 2 node SQL Server 2012 Fail Over Cluster, do I need to install MSDTC also?
If yes then can both be installed on single shared disk?

Optimize IN clause in where query with order by - MySQL

Posted: 01 Oct 2013 11:41 AM PDT

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` WHERE user_id =9898 AND status IN (1,3,4) order by id limit 30;

Here is the result of explain, as you can see the query is filesort

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra  1   SIMPLE  test    range   user_id     user_id     8   NULL    3   Using where; Using index; Using filesort

Here is my table structure

CREATE TABLE IF NOT EXISTS `test` (    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,    `user_id` int(10) unsigned NOT NULL,    `status` int(3) unsigned NOT NULL,    PRIMARY KEY (`id`),    KEY `user_id` (`user_id`,`status`)  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;    --  -- Dumping data for table `test`  --    INSERT INTO `test` (`id`, `user_id`, `status`) VALUES  (5, 9797, 2),  (6, 9797, 3),  (4, 9898, 0),  (1, 9898, 2),  (2, 9898, 3),  (3, 9898, 4);

How can I optimize the query? In my real table I can see the following information in error log: # Query_time: 26.498180 Lock_time: 0.000175 Rows_sent: 100 Rows_examined: 4926

Issue moving database between servers (cannot find .frm file even though it's there)

Posted: 01 Oct 2013 09:15 AM PDT

I used MAMP on my local machine to make a little site with a mysql database. I moved all the site files over the to server. Great.

The name of my database is roster and it has one table called users. I copied the 'roster' folder which contains the following files:

db.opt  users.frm  users.MYD  users.MYI

I put the 'roster' folder in the mysql directory. I run the following:

<?php  $db = mysql_connect("localhost","username","password") or die ('Fail message1');  mysql_select_db("roster") or die("Fail message2");  ?>

No error. Awesome. But when I run the following:

$query = "SELECT last_name, first_name, u_name, skype_id, primary_location FROM users";    $result = mysql_query($query)or die('query error'.mysql_error());

Then I receive the following error:

query errorCan't find file: './roster/users.frm' (errno: 13)

Furthermore, when I login to the server and login to mysql. I see the database and table:

mysql> show databases;  +--------------------+  | Database           |  +--------------------+  | information_schema |  | mysql              |  | phones_db          |  | roster             |  +--------------------+  4 rows in set (0.00 sec)    mysql> use roster;  Reading table information for completion of table and column names  You can turn off this feature to get a quicker startup with -A    Database changed    mysql> show tables;  +------------------+  | Tables_in_roster |  +------------------+  | users            |  +------------------+  1 row in set (0.00 sec)    mysql> select * from users;  ERROR 1017 (HY000): Can't find file: './roster/users.frm' (errno: 13)  mysql>

I checked the 'roster' folder and it contains the four files mentioned at the beginning of this post. The file is where it supposed to be, right? Did I move the database wrong? What gives? Any ideas?

Microsoft SQL server 2012 error 18456 with domain credentials

Posted: 01 Oct 2013 08:53 AM PDT

I have a SQL server 2012 setup to use windows authentication.

Remotely I can connect to "Server A" no problem with domain credentials but when I try to locally connect to "Server A" with SSMS it errors out to say that there is a login failure.

I have all the correct permissions set I beleive any other suggestions?

How to set identity_insert to on in SQL Server

Posted: 01 Oct 2013 01:52 PM PDT

EDIT: See answer here: http://stackoverflow.com/questions/19119303/how-to-set-identity-insert-to-on-in-sqlserver

I get the command set identity_insert table_name on;, but for some reason it is not actually changing the attribute. I've done it with the account that I created the table with, and I've also created an 'admin' account and gave it permissions to do anything (to the best of my knowledge).

Whenever I run the command under either account I get the result of '0 rows affected', then I am unable to run the query to insert the rows I wish to insert with the following error:

Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.

Additionally I've tried to include the schema name in the query like:

set identity_insert mySchema.table_name on;

but I get the following error:

Cannot find the object "mySchema.table_name" because it does not exist or you do not have permissions.

Also of note, my project looks like:

localhost\SQLEXPRESS  -Databases  --System Databases  --mySchema

Does anyone know a solution to this problem? Thanks.

Double queue reader or is it still busy?

Posted: 01 Oct 2013 08:45 AM PDT

I came across this line in the verbose log on replication QueueReader Agent job startup.

The Queue Reader Agent has encountered the error 'Another queuereader agent for the subscription or subscriptions is running, or the server is working on a previous request by the same agent.' when connecting to 'distribution' on 'Svr01\Inst01'. Ensure that the publication and subscription are defined properly and that both servers are running.

The first clue I had that something was wrong was the vague replication message:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

Replication IS working but Replication Monitor is showing red X's. This second error has shown up from time to time and sometimes goes away. For a while now I though this was a bug as it has been reported a few times on Connect.

Assuming the first quoted error is correct, how can I find the double QueueReader or previous request?

tasklist /FI "imagename eq qreader.exe"

Returns nothing.

SELECT job_id, name, enabled FROM msdb..sysjobs WHERE category_id = 19 ORDER BY date_created;

Returns 1 row on each distributor.

Setup:

  1. Svr01 = Publisher, Distributor, SQL 2005
  2. Svr02 = Publisher, Distributor, SQL 2005, Subscribor (Svr01)
  3. Svr03 = SQL 2005, Subscribor (Svr01, Svr02)

Both Svr01 and Svr02 are reporting these errors.

Resources:

DBA.Stackexchange search results

MSDB forum

Oracle: Tables and Views missing from the all_objects dictionary

Posted: 01 Oct 2013 01:17 PM PDT

I have a couple of database instances that are having the curious issue where tables and views do not show up in the all_objects dictionary which is causing some issue with certain schema viewing applications. Not all are missing, but there is only like a dozen of the 130 or so tables/views that are in the schema. If you query all_tables or all_views, they all show up just fine. If you select/update/delete with those tables, it works fine.

Any one know of what types of things I should be looking at that would cause this?

BTW, I am not the DB administrator to this DB, I'm just trying to figure this out for our client. I don't have full access to the DB.

put number inside database sqle server in arabic format I do not want it in english format I need it in arabic [on hold]

Posted: 01 Oct 2013 02:49 PM PDT

I do not want the number in English format I need it in Arabic

I am sorry my problem I inserted number in my table in English(e.g 5),but I want to replace it in Arabic format. because I need it in Arabic to return it in text box in my windows form . hope you get what I want thanks

Performance setting for MyISAM tables - my.cnf file

Posted: 01 Oct 2013 09:02 AM PDT

I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) .

QUESTIONS

  • Since the databases size is smaller than the available memory, how can I make full usage of my server's resources?
  • What settings should I attempt configure in my.cnf?
  • Can I force data + indexes to stay in memory?

Possibilities to speed up InnoDB INSERTs and UPDATEs

Posted: 01 Oct 2013 09:41 AM PDT

My website retains user sessions in an innodb table. This usually works well, but a few hundred times a day, the INSERT and particularly the UPDATE statements run slower.

I'm monitoring when, and it turns out it's usually when a bot is hitting my site (googlebot for instance always gets the same session assigned, so each hit will require an UDPATE on the session table).

I presume google hits me so hard that mysql can't keep up with updating the same record. Usually an update takes a few mills, but I've seen it reach a full second. My table btw has never reached more than 7000 records.

Knowing that I have only 1 disk at my disposal, but still some free memory to spare, I was wondering what would be the best possibilities to enhance the UPDATE performance on this particular table (I'm not experiencing any issues on other tables as it's much less write intensive).

why do I have three copies of data/log files per database on same server instance

Posted: 01 Oct 2013 06:23 PM PDT

OS: Windows 7 Professional

Database: SQL Server 2008 R2 Standard Edition

Just detected by SpaceMonger that, each of my databases on test instance has three copies of data files (.mdf) and log files (.ldf).

they are located at three folders:

  • ..\DataFiles\Test
  • ..\LogFiles\Test
  • ..\Microsoft SQL Server\Mssql10_50.test\Mssql\Data

and they have exactly same file size and timestamp.

I confirmed that I have no mirroring setup for any of my databases, neither do I have any special setup to enforce the database to have files splitted.

The only suspicious setting I can find is:

  • the first two locations are the server level database default location (right click server in the MSSMS -> Properties -> Database Settings -> Database default locations): the first is for Data while the second is for Log,

  • and the last is each database's file location appearing in the MSSMS (right click a database -> Properties -> Files -> Database Files)

This annoying problem has made my server available disk space very low and forced me to do housekeeping very often.

any idea why does this happen?

any methodology i can follow to find out what has been wrong?

-- EDIT ---

Thank you all for the comments and reply.

We have identified the problem - not related to the sql database setting, but the folders themselves: the three folders have a setup to mirror each other. we have got our network team to investigate why it's so.

How can I get my linked server working using Windows authentication?

Posted: 01 Oct 2013 04:24 PM PDT

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error:

"Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'".

In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012

Posted: 01 Oct 2013 05:24 PM PDT

I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition.

My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008.

The error message I get when trying to login is

Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452)

I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy).

Any help would be appreciated.

Inserting Records - How to get next incremented PK for each insert

Posted: 01 Oct 2013 01:54 PM PDT

I'm using spoon to look for Devices that have several attributes that define its location, and then create a Cabinet in that location if one doesn't exist. Is there an operation that lets me get a cabinet.id value that is 1 greater than the highest currently in the table before each row is inserted.

Here's some pseudo code for what I want to do:

  • Find Devices with location data AND not with cabinet_id
  • Create a Cabinet record in the location of the first Device record returned, using the next Cabinet.id
  • Get the next Device, ensure a Cabinet was not created during this operation matching this Devices position, create a Cabinet record with the next Cabinet.id

Thanks for any guidance.

Mysql DB server hits 400% CPU

Posted: 01 Oct 2013 01:42 PM PDT

I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top. 

 - load average 40 to 50   - CPU % - 400%    - idle % - 45%   - wait % - 11%   - vmstat procs r-> 14 and b-> 5

And then drains down within 5 minutes. And when I check the show processlist I see queries for DML and SQL are halted for some minutes. And it processes very slowly. Whereas each query are indexed appropriately and there will be no delay most of the time it returns less than 1 second for any query that are being executed to server the application.

  • Mysql Version : 5.0.77
  • OS : CentOS 5.4
  • Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE)
  • Database Size: 450 GB
  • 16 Processor & 4 cores
  • Not in per-table model.
  • TPS ranges 50 to 200.
  • Master to a Slave of the same configuration and seconds behind is 0.

Below url shows show innodb status \G and show open tables; at the time spike. And this reduced within 5 minutes. Sometimes rare scenarios like once in two months I see the processes takes more than 5 to 8 hours to drain normal. All time I notice the load processor utilization and how it gradually splits its task and keep monitoring the process and innodb status and IO status. I need not do anything to bring it down. It servers the applications promptly and after some time it drains down to normal. Can you find anything suspicious in the url if any locks or OS waits any suggestion to initially triage with or what could have caused such spikes ?

http://tinyurl.com/bm5v4pl -> "show innodb status \G and show open tables at DB spikes."

Also there are some concerns that I would like to share with you.

  1. Recently I have seen a table that gets inserts only about 60 per second. It predominantly locks for a while waiting for auto-inc to get released. And thus subsequent inserts stays in the processlist tray. After a while the table gets IN_USE of about 30 threads and later I don't know what it makes to free them and clears the tray. (At this duration the load goes more than 15 for 5 minutes)

  2. Suppose if you say application functionality should be shapped to best suite the DB server to react. There are 3 to 5 functionalities each are independent entities in schema wise. Whenever I see the locks it gets affected to all other schemas too.

  3. Now what makes best fuzzy is the last one. I see slave keeps in synch with master with a delay of 0 second all time whereas slave has a single thread SQL operation that is passed from relay IO that which acts in FIFO model from the binary logs where Master had generated. When this single headed slave can keep the load less and have the operations upto-date, should the concurrent hits are really made to be concurrent for the functionalities which I assume making the possible IO locks in OS level. Can this be organized in application itself and keep the concurrent tenure density thinner?

Multiple database servers for performance vs failover

Posted: 01 Oct 2013 07:24 PM PDT

If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?

Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active?

I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.'

If it matters, we have traffic around 3,000-7,000 simultaneous users.

Best way to delete very large recordset in Oracle

Posted: 01 Oct 2013 12:22 PM PDT

I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no triggers or anything) it's just a data store.

Every month we are required to purge records from the two of the main tables. The criteria for the purge varies and is a combination of row age and a couple of status fields. We typically end up purging between 10 and 50 million rows per month (we add about 3-5 million rows a week via imports).

Currently we have to do this delete in batches of about 50,000 rows (ie. delete 50000, comit, delete 50000, commit, repeat). Attempting to delete the entire batch all at one time makes the database unresponsive for about an hour (depending on the # of rows). Deleting the rows in batches like this is very rough on the system and we typically have to do it "as time permits" over the course of a week; allowing the script to run continuously can result in the a performance degradation that is unacceptable to the user.

I believe that this kind of batch deleting also degrades index performance and has other impacts that eventually cause the performance of the database to degrade. There are 34 indexes on just one table, and the index data size is actually larger than the data itself.

Here is the script that one of our IT people uses to do this purge:

BEGIN  LOOP    delete FROM tbl_raw     where dist_event_date < to_date('[date]','mm/dd/yyyy') and rownum < 50000;      exit when SQL%rowcount < 49999;      commit;    END LOOP;    commit;    END;

This database must be up 99.99999% and we've only got a 2 day maintenance window once a year.

I'm looking for a better method for removing these records, but I've yet to find any. Any suggestions?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog