[how to] Clustered tables vs non-clustered (IOT vs heap) |
- Clustered tables vs non-clustered (IOT vs heap)
- How/Where to get date/time/calendar data to store in a table?
- Questions regarding second normal form
- What is the best way to recover from a mysql replication fail?
- Extract MySQL information from a compressed tar.gz file
- MySQL always crash on start, ibdata1 bigger than 4GB
- Huge 0.000001 and 0.000002 files and so on, in mysql data folder
- Can't connect to my Postgres databases
- Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop
- How to find parent rows that have indentical sets of child rows?
- MySQL bin log missing data?
- Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
- USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)
- How would I store the result of a select statement so that I can reuse the results to join to different tables?
- How long takes populating catalog in SQL Server 2008 R2 when update a row?
- Error installing SQL Server 2008 - all componets installed successfully except database engine
- Connection pools being reset with Error: 18056, Severity: 20, State: 46. & Perfmon Counters not showing
- Bitmask Flags with Lookup Tables Clarification
- SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
- BIT columns all "1" after a phpMyAdmin export/import
- SQLite writing a query where you select only rows nearest to the hour
- Breaking Semisynchronous Replication in MySQL 5.5
- Mongo replication lag slowly increasing
- Unable to start Oracle Database Server : Get an error as I try to start it
- SQL Server replication subscriptions marked as inactive
- "Waiting for initial communication packet" error 0 in MySQL
- "freeing items" taking forever on inserts, updates and deletes
- How do you kick users out of a SQL Server 2008 database?
| Clustered tables vs non-clustered (IOT vs heap) Posted: 30 Jun 2013 02:40 PM PDT It happened I had to work with both SQLServer and Oracle for quite a while (thankfully not at the same time). What still puzzles me is the approach to storing tables as balanced trees. In Oracle-like RDMS heap is default, in SQLServer (and many others ) the reverse (clustered, IOT) is true. Adepts of each approach claim their way is the only 'correct' and support chosen point of view with bunch of tests/presentations. However, in my opinion, the only point they proved is that implementation of "non-default" approach is poor, and it shouldn't be used for the most cases... I'm pretty sure both approaches are good enough (just because they still exist on the market and show comparable performance) and have some math underneath , but I'm failed to find any good references. I realize the topic may be too broad to answer, and good links are very welcome, but I really want to know why two seemingly controversial approaches have proven they are both valid. Thank you |
| How/Where to get date/time/calendar data to store in a table? Posted: 30 Jun 2013 08:18 PM PDT I cannot recall where I got the data before so I'm asking here: Where can I get or how can I generate standard date/time/calendar data? The table would consist of columns such as: and a few more columns maybe like the year, month, date (dd) - etc - which I cannot remember at the moment. Then there are, if I recall correctly, columns that cover timezone changes -- I'm not sure if the data was created by a script made by another DBA in my previous job, or if it is available on the internet - I can't seem to find it, but I am likely to believe it is somewhere out there. It could also have been a table already created and just ready for download (script?) -- The data I'm referring to doesn't change. Say, for example, 2013-06-30 will forever be a Sunday - nothing will change that. 12:00 PM will always be 12:00 PM. There are 24 hours each day - that's also constant.. I apologize if this is vague.. I'm really looking for a table that can be looked-up since the values per day per year will never change. |
| Questions regarding second normal form Posted: 30 Jun 2013 10:16 AM PDT I understand that in order to fulfill 2.NF, attributes must not be dependent on part of the key. Now, the question is, let's say we have a relation R with a set of attributes {A,B,C,D,E,F,G,H,I,J,K} and its functional dependencies {A→GH, B→IJ, C→A, F→B, FC→DK, K→E} and the candidate keys C AND F. Do these FDs like C→A violate 2.NF because it is not fully functionally dependent on BOTH F AND C ? So the question is
|
| What is the best way to recover from a mysql replication fail? Posted: 30 Jun 2013 05:25 PM PDT Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue. I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker. At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow? The servers are connected through a 1gb switch. |
| Extract MySQL information from a compressed tar.gz file Posted: 30 Jun 2013 07:58 AM PDT I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There is an 'import' feature in phpmyadmin, but this doesn't accept files in the tar.gz format, only sql. Is there any way I can convert from .tar.gz to sql? I know I can upload from an sql file without a problem. I don't have access to any 'back-end' stuff on the web-server, so I'm limited with what I can do locally on my PC. Any help is much appreciated. |
| MySQL always crash on start, ibdata1 bigger than 4GB Posted: 30 Jun 2013 02:13 AM PDT I'm having a problem with MySQL when testing my production data. Whenever I started MySQL (mysqld), it always crash. My suspect is on my InnoDB data file (ibdata1) since the size is already bigger than 4GB. I'm using Windows XP 32-bit, memory 3GB. How can I start my server normally? I already tried to search on the Internet, and found out that we can actually make more than one ibdata by changing the config file (my.ini), but when I change the config file, I got "Error 1067: The process terminated unexpectedly" How can I fix this problem? Thank you. EDIT Error log: Log file: |
| Huge 0.000001 and 0.000002 files and so on, in mysql data folder Posted: 30 Jun 2013 12:23 AM PDT In mysql data folder unknown files were created like this: This is my.ini content: I recently added some innodb tables and these files appeared! These files are in root mysql data folder(e:/hafez/bin/mysql/mysql5.5.24/data) and aren't in any database folder. What are these files? Is it safe to delete this? |
| Can't connect to my Postgres databases Posted: 30 Jun 2013 09:35 AM PDT *** SECOND UPDATE ***I gave up and reset completely postgres, as suggested in this thread. Even after this reset I was not able to use the "main" cluster, I had to remove and reinstall it. Now it works, but I don't understand what happened. I think that @Pjack was on the right track, the problem was probably be that postgres thought the clusters were already running, or the ports were already used. If the problem comes up again I'll investigate more deeply. *** UPDATE ***I tried also to remove and reinstall postgres and postgis but it doesn't work. This is what I've done A strange message was showed up during uninstall: Of course I have created a cluster when I installed postgres. I tried to install a new cluster 'test' and it works, but I obviously I can't access any database of the other clusters. I can't connect anymore to my Postgres databases. Trying to access it gives me the usual error: Starting the postgres service gives me no error, and there's no error in var/log/postgresql/ , but checking the status gives me: while usually I have port 5433 up, and From the last time it worked and now, I tried to dump OpenStreetMap data inside a postgis db. The only "dangerous" things that I could have done was changing these postgres settings: and adding in /etc/sysctl.conf as suggested on postgres documentation for tuning-up the db performance. Anyway I reverted all the changes, using the default values or commenting added lines. The second thing I've done was that I executed osm2pgsql to dump the data, using: I don't know if osm2pgsql did something dangerous, but I don't think so... What could have happened? I'm using Postgres 9.1 + Postgis 2 on Lubuntu 13.04 32 bit on local, using a peer authentication and a Unix socket. |
| Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop Posted: 30 Jun 2013 05:36 PM PDT I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform. Is there a version of Oracle XE 11g R2 available for Windows 7? And if so, could you please point me to it? Thanks... |
| How to find parent rows that have indentical sets of child rows? Posted: 30 Jun 2013 04:25 AM PDT Suppose I have structure like this: What are some good ways for finding duplicate recipes? A duplicate recipe is defined as having the exact same set of ingredients and quantities for each ingredient. I've thought of using Edit: There are 48K recipes and 200K ingredient rows. |
| Posted: 30 Jun 2013 08:37 PM PDT I'm trying to make heads and tails of my binary logs and am coming up short. I have many entries similar to the following from mysqlbinlog but I'm missing log data that I know should be there. It's puzzling because I get expected SQL in the mysqlbinlog output for statements executed in phpmyadmin but those coming from other PHP-based remote web servers appear to not be recorded. My settings bin logging are: Am I missing a logging option? Mysql 5.0.95 / CentOS 5.9 |
| Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour! Posted: 30 Jun 2013 05:45 PM PDT I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar. Currently the fastest way i've found to get the information into my DB until now was: Process raw data into CSV files. Load Data In File to rawData Table. Insert rawData table into Table1. (on dupe key do my function) Truncate rawData Repeat. Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour. I got 16G of ram so I set my Key_Buffer_Pool to 6G. I have my query cache pool to 16M I have my query cache limit to 10M I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not. Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow. Maybe allowing more table cache? Or even Query Cache? mysql sql mysqli innodb myisam Merge Code:
To compare my 2 bool columns. Update
|
| USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view) Posted: 30 Jun 2013 11:45 AM PDT Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view. I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue. But other than that, it appears that value is always NULL. |
| Posted: 30 Jun 2013 03:45 AM PDT How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor. Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it. I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error. |
| How long takes populating catalog in SQL Server 2008 R2 when update a row? Posted: 30 Jun 2013 04:45 AM PDT I have installed SQL Server 2008 R2 with FullText search feature installed. I created a catalog and fulltext index on a table (let's say
When I use a stored procedure to update a row from but no results were found. I've checked the populate status is 0 (idle). I have to go in Sql Management studio and choose rebuild catalog. And then works (mean I found results what I expected) My question: how long takes populating catalog in sql server ? My table (Table1) has 2000 rows. |
| Error installing SQL Server 2008 - all componets installed successfully except database engine Posted: 30 Jun 2013 05:45 AM PDT Below is the error I copied, please someone help me, I have tried at least 10 times in the last 3 days. It is SQL Server 2008 R2 and I wanted to install it on Windows 7.
|
| Posted: 30 Jun 2013 06:45 PM PDT We are using SQL authentication & .net 4.0 Connection strings to connect to an Enterprise Edition 2012 SP1 SQL Server on a windows 2008r2 Enterprise Server. We use about 50 Servers split into 8 different groups different parts of a website. Our website is using this SQL Server to log Visit tracking data and over the last few days it has spat out the following messages about the resetting connection pools.
Errorlog reads
After some digging I found this document from CSS blog and this one by the scholar Aaron Bertrand (I know the error number is different but the failure ID is the same with a number of the messages are identical) Failure ID 46, suggests that the login did not have permissions. Our logins default to the master database and the db name is specificied in the connection string. I wanted to check the number of connection strings pools, etc and checked all the counters in Perfmon for ".Net Data Provider for SqlServer" it only gave me the option of defaultdomain9675 for the instance so I selected that assuming that is a system generated ID name for our Datacentre network. Unfortunately all of the counters are reading zero. On one of our other main servers the connection pools are hovering around 10 which is what I expected to see on a healthy server with that kind of load. My question is 3 fold 1- Can anyone suggest why the Windows 2008 R2 Server is not showing ".Net Data Provider for SqlServer"? 2- Has anyone experienced this as I obviously believe that the login not having permissions is a red herring ? 3- If different groups of web servers have the same connection string syntax but with slightly different whitespace, would this cause the server to use another connection pool ? |
| Bitmask Flags with Lookup Tables Clarification Posted: 30 Jun 2013 07:45 PM PDT I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic. Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records. Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results. I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented. Is this as simple as creating a lookup table that has all possible search conditions? Example for 3 bits using (a & b) (Edit: Wrong bitwise op) The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows. Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated. Edit: A more concrete example using small data.
Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &). Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions. |
| SQL Server 2012 catalog.executions to sysjobhistory - any way to join them? Posted: 30 Jun 2013 01:45 PM PDT I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job. |
| BIT columns all "1" after a phpMyAdmin export/import Posted: 30 Jun 2013 03:45 PM PDT I have to import data from a MySQL database using phpMyAdmin because that's the only interface my hosting provider supports. I have exported my database from my local machine using phpMyAdmin. After that I imported the script file to my host. All of data in the columns that is BIT type are changed to '1'. Hosting database version is 5.5.29. Is it a phpMyAdmin problem, or MySQL version problem? How can I fix this? |
| SQLite writing a query where you select only rows nearest to the hour Posted: 30 Jun 2013 04:45 PM PDT I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a unix timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing. What I need is the row nearest to the hour, with the timestep rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour. Where a matching hour could not be found it would be helpful if the query could include a time but no value. I realise I'm asking a lot, but this would be incredibly helpful Thanks for taking the time to read this. James BTW, The table is just PK (autoincrement),timestamp,value, sensor id(FK). I've tried this to get the data out: |
| Breaking Semisynchronous Replication in MySQL 5.5 Posted: 30 Jun 2013 08:45 PM PDT I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the slave database server. But due to some unknown reasons sometimes, Replication breaks. On running the command: It gives this status: Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave. Please help us in this regard. |
| Mongo replication lag slowly increasing Posted: 30 Jun 2013 08:45 AM PDT I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day.
One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command ) |
| Unable to start Oracle Database Server : Get an error as I try to start it Posted: 30 Jun 2013 09:45 AM PDT I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop : but as I click this icon I see this dialog box : What is it ? How do I start my Oracle Database Server ? |
| SQL Server replication subscriptions marked as inactive Posted: 30 Jun 2013 10:45 AM PDT Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever? It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time. Note, I'm not talking about the subscriptions being marked as expired...just as inactive. Thank you. |
| "Waiting for initial communication packet" error 0 in MySQL Posted: 30 Jun 2013 06:45 AM PDT I am using MySQL server 5.1.51 on a Linux machine. The application was working fine for the last 32 days, but since yesterday a number of clients are not able to connect to the database from our application. They are getting the following error: What can I do to fix this? |
| "freeing items" taking forever on inserts, updates and deletes Posted: 30 Jun 2013 02:45 AM PDT Recently I migrated all my MySQL database tables from MyISAM engine to InnoDB. I'm using the DotCloud MySQL service as my server (http://docs.dotcloud.com/services/mysql/). After the migration, everything went fine for the first two weeks, but for a couple days I've been experiencing some problems. At first, all my queries would lock under an update or insert at certain tables. With a SHOW PROCESSLIST; I could see that the locking queries had the state of "freeing items". I started to research and found this: Why do MySQL threads often show "freeing items" status when the query cache is disabled? I've changed the innodb_thread_concurrency to 32 as suggested, and that really did help in order to keep the website running well, which means that I'm not experiencing application locks any more. The problem is that inserts and updates are still slow, some of them taking more than 60 seconds to happen and that is terrible. I'll try to put my database under maintenance and optimize or repair it, but I don't know if that is going to work really well. Does anyone knows what I could do about it? Thanks! |
| How do you kick users out of a SQL Server 2008 database? Posted: 30 Jun 2013 03:55 AM PDT We need to do a restore, and cannot because other users are connected. We thought we had disconnected every process, but apparently not. How can we, from Management Studio, kick off everyone else so we can do this backup? |
| 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