[how to] How do I determine when scaling is necessary or helpful? |
- How do I determine when scaling is necessary or helpful?
- How to allow each user to access his own db, and superuser to all dbs in PostgreSQL?
- PostgreSQL COPY: is it always faster to delete and recreate indexes?
- Changing autogrow settings with database mirroring
- Transfer logins to another server
- SQL Server logs in Gmail [on hold]
- What is a good name for a column representing the display order of the rows?
- Caclulating employee's work time
- Just how secure is the data I put on SQL Azure?
- Reporting Services TempDB keeps going into restoring mode
- MySQL table architecture
- Can I force a user to use WITH NOLOCK?
- How to determine Windows Server start time using T-SQL
- TokuDB database size unknown in phpmyadmin
- select query not displaying column names with records
- best practice either create multiple databases or multiple users
- DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition
- PostgreSQL CREATE TABLE creates with incorrect owner
- Proper MySQL database maintenance
- Teradata : Disadvantage of Collect stats
- Can't change root password: "The system cannot find the file specified."
- InnoDB tables inaccessible after reboot
- Fulltext stoplist replication
- sql server database sharding - what to do with common data / non sharded data
- Defragmenting SQL Server data and log files live with MoveFile API
- What's the difference between a temp table and table variable in SQL Server?
- Logging/profiling an unpredictably slow stored procedure
- Any Open Source / free ETL out there?
| How do I determine when scaling is necessary or helpful? Posted: 31 Jul 2013 05:43 PM PDT I have a MySQL Server that I use for the back-end storage of an online game. The game writes to the database to persist character information such as world location, health status, ability stats, etc .. This data is written every few seconds to the database for each character and vehicle. I have been noticing that as more and more users join the game that queries I write via the database seem slower. Also, as I make the online world more complex, via content added via the database, the game tends to slow. I am looking for the bottleneck and I am wondering how I go about determining if scaling my database (vertically or horizontally) will make a difference. Knowing the database is MySQL how do I go about seeing if my single database server is the bottleneck and if scaling it would be helpful? Thank you. |
| How to allow each user to access his own db, and superuser to all dbs in PostgreSQL? Posted: 31 Jul 2013 03:57 PM PDT I want to set up some pretty traditional permissions: each user can access his own db, and superuser can access all the dbs. So far I got the first part right (I think). I edited pg_hba.conf, and changed this: for this: But now I can't access any db with my superuser. I tried adding "localhost all myuser" but doesn't seem to work. Any ideas? |
| PostgreSQL COPY: is it always faster to delete and recreate indexes? Posted: 31 Jul 2013 06:46 PM PDT In this answer, Erwin Brandstetter recommends
1) Is this true even when the table is already large? If not, is there an easy way to estimate the point of diminishing return? For example, I'm doing COPY of 30,000 records at a time into a table which already has say 100 million rows. Each batch is relatively small, but on the other hand the total batches on hand to import at the moment will more than double the size of the table. 2) If I were to delete the index before COPY, would recreating it be faster if the COPY is done with records already in index order? There is only one index on the table, which is a 5-column primary key. I'm doing this on a non-production machine, and I've already disabled fsync, syncronous_commit, autovacuum, etc. I'm executing the COPY operation from four separate processes concurrently on an 8-core machine. I've got 12GB of memory, so I could set maintenance_work_mem to 1GB or more if it would help. |
| Changing autogrow settings with database mirroring Posted: 31 Jul 2013 02:15 PM PDT Recently I changed the Autogrow settings for some of the databases on our SQL Server 2008 R2 server. These are involved in a database mirroring configuration, with the principal on SERVER1 and the mirror on SERVER2. This week I failed over three databases- now SERVER1 is the mirror and SERVER2 is the principal for these databases. It appears that the autogrow settings did not move over to SERVER2 properly, as the databases now show that they grow by a percentage (two are set to 32768%, the other to 131072%). This is different than the settings that used to be there (I believe it was the default- 10%), and also different that the 256MB I set on SERVER1. To make things more confusing, this is only happening on the primary file- the secondary files and log file has retained the settings I set on SERVER1. My suspicion is that this is a bug- I did patch SQL after changing the autogrow settings. My question is- has anyone seen this scenario before? Is there a method to make sure all of the settings are correct on both servers without failing all the databases over? UPDATE: Using a 4th database that I'll call DB1, I set the autogrow on SERVER1 to 512MB (after failing the database over, witnessing the same issue, and failing it back). When I failed it over to SERVER2 after that, it shows growth of 65536%. The takeaway is that the UPDATE2: With DB1 on SERVER1, I changed the autogrow to 10% and failed it to SERVER2. The 10% value remained. I then repeated this, setting autogrow back to 256MB this time. Again, the |
| Transfer logins to another server Posted: 31 Jul 2013 01:30 PM PDT I moved a database from SQL Server 2005 to SQL Server 2012. Is there any way of restoring all the logins on the new server? |
| SQL Server logs in Gmail [on hold] Posted: 31 Jul 2013 02:09 PM PDT First off, I'm using SQL Server 2008 R2. So we all know Gmail uses the conversation view for emails. I like this...except when my SQL Server backup logs get emailed to me from the same email address. So Gmail collapses them into a single conversation even though the logs are for different databases. Is there a way to trick Gmail to separating them out based on the database? Do I have to create a new distribution list for each database I have or do I have to create a new Operator (SQL Server Agent -> Operator) for every database? Does anyone else have to deal with this issue? |
| What is a good name for a column representing the display order of the rows? Posted: 31 Jul 2013 02:11 PM PDT For example, a junction table associating a product and its pictures. What is a good, short, general, name for "some_column" if it represents the display order of the photos? "order", "sort", and "sequence" are out, as they are keywords. |
| Caclulating employee's work time Posted: 31 Jul 2013 11:44 AM PDT I need to store and analyze employee's work time. Currently I've developed this table structure CREATE TABLE `hirefire` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `employeeid` int(10) unsigned NOT NULL, `hired` date NOT NULL, `fired` date DEFAULT NULL, `firereason` enum('fired','left','parental','vacation','death') COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `hired` (`hired`), KEY `fired` (`fired`), KEY `employee` (`employeeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin The fields
This my table scheme may be (I think) good to check whether a given employee is listed as working at least a day in a given month (BTW, an SQL code for this would be appreciated), But now a complexity comes out: We will also need to be able to count employee's total working years. Note that parental leave and non-paid vacations should be not subtracted from the working years. This time my database structure goes non convenient for this kind of calculation. Which DB structure would you suggest to use? (Is my (above) variant is the best structure for these tasks, or can it be improved?) What's about allocating a whole DB row for every hire/fire event (that is having a separate row for hire data and fire date not one the same row for paired fire/hire)? For which tasks this would be better and for which worse? MySQL 5. |
| Just how secure is the data I put on SQL Azure? Posted: 31 Jul 2013 01:20 PM PDT My wife has been asking me to create a web application for her business and from what I've read using SQL Azure would be perfect. Except for a couple of small (really really big) gotchas.
So my question is, if I put her companies information on SQL Azure how secure will it be? I'm not asking about general SQL Server/Azure security. I'm talking about the fact that this is a shared space. How possible is it to hack into the data, bypassing the security that I put into place? |
| Reporting Services TempDB keeps going into restoring mode Posted: 31 Jul 2013 10:53 AM PDT I am looking into an issue with SQL Server 2008 Reporting Services. It has been working fine until yesterday morning. Now I notice that The reports work fine after this and then after 10-15 minutes, then
Any suggestions why it is doing that. I have checked the following
There are multiple instance of SSRS running and SQL instance hosting the database for SSRS on the same server. I have checked that AutoClose is not enabled for this database, and I have discovered that the database goes into |
| Posted: 31 Jul 2013 09:58 AM PDT Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; frequently it uses an even tighter time scale and queries are restricted to information gathered in the last 30 days This system has been in existence from 2004 when there were 400 uploads, today there are 2+ million Table structure: Questions Is there a way to partition the table that would make the most sense? Does partitioning even make sense? How do I deal with new data if I do partition? |
| Can I force a user to use WITH NOLOCK? Posted: 31 Jul 2013 02:10 PM PDT Can I force a user's queries to always run with the hint NOLOCK? e.g. they type: But what is executed on the server is: THIS QUESTION IS NOT about the various pros and cons of NOLOCK. |
| How to determine Windows Server start time using T-SQL Posted: 31 Jul 2013 09:22 AM PDT I am looking for a way to find out when the Windows Server was last started using only t-sql commands. I am trying to stay within the default security configurations (i.e. I don't want to enable xp_cmdshell) |
| TokuDB database size unknown in phpmyadmin Posted: 31 Jul 2013 01:51 PM PDT In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show |
| select query not displaying column names with records Posted: 31 Jul 2013 03:47 PM PDT If I execute a select query it is displaying only records not column names like as follows
What is the problem? |
| best practice either create multiple databases or multiple users Posted: 31 Jul 2013 06:45 PM PDT I have several application which currently running on my local. I have installed oracle 11g and create a global database name as oracle11. Now should i create again different database for each application using Database configuration assistance or should i create different users for each application under that global database? What would be the best practice? If i create different users its also behave same as creating different databases right? What if in a production environment where server house for couple of application? Do i need to do the same as my local by creating one database and different users for each application? |
| Posted: 31 Jul 2013 08:34 AM PDT TL;DR: I've got an unfixable corruption in an indexed view. Here the details: Running
on one of my databases produces the following error:
I do understand that this message indicates that the materialized data of the indexed view 'ViewName' is not identical with what the underlying query produces. However, manually verifying the data does not turn up any discrepancies:
No rows are being returned here, meaning that the two tables are identical. (There are only integer and guid columns, collations do not come into play). The error cannot be fixed by recreating the index on the view or by running (Even if rebuilding fixed it my question would still stand - why is an error reported although my data checking queries run successfully?) |
| PostgreSQL CREATE TABLE creates with incorrect owner Posted: 31 Jul 2013 10:59 AM PDT I'm using PostgreSQL 9.2.4. When I create a table as a non-superuser in a database owned by that non-superuser, it is owned by the I created the non-superuser like this: Then I created a database owned by Then I started a new psql as user But I couldn't insert into it: Checking the permissions shows that the table is owned by the However, I can grant myself permissions and do stuff: What's going on? I'm pretty sure this used to work. And the PostgreSQL docs for CREATE TABLE say
Having to grant permissions to myself on my own tables doesn't sound like it's what I should have to do. Any help much appreciated! [UPDATE] It looks like this might be caused by some kind of change to the PostgreSQL package at http://apt.postgresql.org/pub/repos/apt/ We have three machine images, all of which installed PostgreSQL from that source:
So perhaps something has been changed in the packaging. I'll drop a line to the mailing list for that. |
| Proper MySQL database maintenance Posted: 31 Jul 2013 01:31 PM PDT I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it. I'm a systems admin that got put into the roll of DBA (because I helped the CEO with his outlook, so I can obviously manage our databases!). It's not that big or busy of a database server... a mysqldump is about 6GB in size and it's taken us 5 years to get it that big. Munin reports that we're averaging 40-60 queries a second at our peak hours. My boss paid for me to take the Oracle University systems admin course, but having gone through it, it simply explains the different parts of the mysql server, what things do and how they do it. But it's not that in-depth and you definitely don't come out of that course a DBA. So as the current DBA, what should I do to make sure everything is running smooth? Are there daily maintenance tasks I can perform? Are there certain metrics I should be checking? Or to put it another way, as DBAs, what do YOU do on a daily basis to keep everything in good shape? If it'll help tailor the answer a little bit, here are some specifics. We have 171 tables, all but 3 are innodb, the others are myisam. We have Master/Slave replication set up between our primary datacenter and our disaster recovery site, using RBR. Version is 5.5.28. What can I do? |
| Teradata : Disadvantage of Collect stats Posted: 31 Jul 2013 01:13 PM PDT I want to know any disadvantage can occur by doing collect stats on a column that is being widely used in join condition across so many procedures ? (Eg. the column is accessed 300+ times access in 60 days) While searching in google, i came to know the only issue that can cause performance degradation is out of date Stats. In this case, this is not going to happen as whenever data of table changes, we are going to run collect stats. Any other disadvantage is there because of adding collect stats other than space consumption ? |
| Can't change root password: "The system cannot find the file specified." Posted: 31 Jul 2013 08:46 AM PDT I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message:
I'm using MySQL 5.1.70 (x86, 32-bit) on Windows 7 SP1 (64 bits). I've added MySQL's "bin" directory to my "Path" environment variable. In the comments of the MySQL documentation, I read that I should have installed the service using the absolute path, so I stopped MySQL, and uninstalled it: Then I installed it again, using the absolute path this time: I started MySQL, and tried to change the password again: I also tried with quotes: I also tried to change the current directory to MySQL's "bin" directory: What's wrong? |
| InnoDB tables inaccessible after reboot Posted: 31 Jul 2013 11:46 AM PDT After reboot each time, I can't see my InnoDB table. However, when use command mode I can see my tables, but cannot access to it. But when I want to access a table it says table doesn't exist; Edit by RolandoMySQLDBA Please run the following in MySQL: Please run the following in the OS: Hi RolandoMySQLDBA, Thank you for your quick reply. Here is all those outputs. My data directory is not in "/var/lib/mysql" path. I have been changed it to "/var/www/xampp/mysql/data" path. So I am going to execute the following code. Hi Mannoj These tables are created in these same database. I have some errors in my "/var/log/mysql/error.log". I don't understand what are those. Here are they .... Thanks to both of you. |
| Posted: 31 Jul 2013 07:47 PM PDT In MS SQL Server 2008 R2 there is replication of table with fulltext index on it. But stoplist that is associated with replicated fulltext index doesn't replicate. Is there any possibility to replicate stoplist also? |
| sql server database sharding - what to do with common data / non sharded data Posted: 31 Jul 2013 06:47 PM PDT We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very heavy and continues to grow heavier the larger the company grows. sql proc optimization has been performed and hardware has already been scaled up to a very high level. We are looking to shard the database now to ensure that we can handle company growth and future loads. We have decided what particular data should be sharded. It is a subset of our database which is highly utilized. However, my question is regarding the non sharded data which is common/universal. An example of data like this may be an Inventory table for instance or possibly an Employee table, user table etc . I see two options to handle this common/universal data: 1) design 1 - Place the common/universal data in an external database. All writes will occur here. This data will then be replicated down to each shard allowing each shard to read this data and inner join to this data in t-sql procs. 2) design 2 - Give each shard its own copy of all common/universal data. Let each shard write locally to these tables and utilize sql merge replication to update/sync this data on all other shards. concerns about design #1 1) Transactional issues: If you have a situation in which you must write or update data in a shard and then write/update a common/universal table in 1 stored proc for instance, you will no longer be able to do this easily. The data now exists on seperate sql instances and databases. You may need to involve MS DTS to see if you can wrap these writes into a transaction since they are in a separate database. Performance is a concern here and possible rewrites may be involved for procs that write to sharded and common data. 2)a loss of referential integrity. Not possible to do cross database referential integrity. 3) Recoding large areas of the system so that it knows to write common data to the new universal database but read common data from the shards. 4). increased database trips. Like #1 above, when you run into a situation in which you must update sharded data and common data you are going to make multiple round trips to accomplish this since the data is now in separate databases. Some network latency here but I am not worried about this issue as much as the above 3. concerns about design #2 In design #2 each shard gets its own instance of all common/universal data. This means that all code that joins to or updates common data continues to work/run just like it does today. There is very little recoding/rewriting needed from the development team. However, this design completely depends on merge replication to keep data in sync across all shards. the dbas are highly skilled and are very concerned that merge replication may not be able to handle this and should merge replication fail, that recovery from this failure is not great and could impact us very negatively. I am curious to know if anyone has gone with design option #2. I am also curious to know if i am overlooking a 3rd or 4th design option that I do not see. thank you in advance. |
| Defragmenting SQL Server data and log files live with MoveFile API Posted: 31 Jul 2013 02:26 PM PDT My employer is looking at deploying NTFS defragmentation software that uses the Windows MoveFile API to defragment open files. This would get deployed to thousands of SQL Server servers running SQL versions from 2005-2012 and Windows versions from 2003 and 2008 R2. If it matters, the product I'm talking about is PerfectDisk, but I believe there are similar programs that work the same way. Testing so far hasn't turned up many problems aside from occasional I/O performance issues, which is no surprise and can be worked around by rescheduling & tuning the defragmentation. However, I'm more concerned about the risk of data corruption. Does anyone here have experience with running this type of software in production on database servers? Did you experience any data corruption? I'm rather uneasy about this despite being unable to find any solid evidence that it poses a problem. Thanks for any replies. Edited to add: This horrible idea was thankfully just forgotten about, possibly in part due to some of the warnings I gave. |
| What's the difference between a temp table and table variable in SQL Server? Posted: 31 Jul 2013 08:43 AM PDT This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server? |
| Logging/profiling an unpredictably slow stored procedure Posted: 31 Jul 2013 10:40 AM PDT I have a stored procedure that sometimes runs significantly slower than others. I have two different timers measuring the execution time at different scopes. One is in the client code, measuring only the OCI execute call. The second is inside the PL/SQL procedure itself, timing the inside of the procedure's main block. Normally, if the procedure's timer takes, say, 5 seconds to run, the client's timer is only a few milliseconds greater. This is completely reasonable. The problem is that sometimes the client's timer will be much greater than the procedure's, even double. I've eliminated any issue on the client or in the transport layer. I enabled client and server logging in The question remains, if the overall call takes 10 seconds, and inside the procedure takes only 5 seconds, where did the other 5 seconds go? I don't expect the exact answer to the above question, but I would like to hear ideas on how to go about finding the answer. What logs can I turn on to give me insight into what Oracle is doing both after the request arrives but before the actual call to the procedure, and after the procedure until the response is sent back to the client? Edit: Perhaps there's something wrong with my timing methodology? I'm using |
| Any Open Source / free ETL out there? Posted: 31 Jul 2013 12:35 PM PDT I was using Pentaho Data Integration even before Pentaho bought it and call it that. I have the last free version. I went on their website recently to see if they had released another version only to find out my favorite open source etl is not much open anymore and not quite free. Does any of you know of alternatives in affordable, easy to use ETL tools? |
| You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment