| How far into the Relational Model does one need to go? Posted: 19 Jun 2013 08:48 PM PDT For instance we have the basics in database theory for the traditional RDBMS. How much into detail does one need to go when Designing/Maintaining/Developing a traditional RDBMS. Pretend for instance you are a beginner to this, do we only need the basic Set Theory and Relational Algebra or should one go much further when learning about the world of databases? I feel alot of the things learned don't really get put into practice, how often do we actually see this with DBA's in the world today? |
| compare all tables and find number of common columns between all tables Posted: 19 Jun 2013 08:40 PM PDT is there any way to compare every table with each other in a database list the number of COMMON columns between each tables!! ?? prefer using join over subquery and information_schema.column, if possible thx. example: lets say there are 4 tables in a database table1, table2, table3 and table4. list1 list2 commonColumns ----- -------- -------------------- table1 table2 1 table1 table3 2 table1 table4 0 table2 table1 1 table2 table3 5 table2 table4 2 table3 table1 3 table3 table2 0 table3 table4 1 table4 table1 2 table4 table2 3 table4 table3 0
|
| SQL Server compatibility mode Posted: 19 Jun 2013 07:57 PM PDT Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different then I expected. As far as I understand compatibility modes, it is about the availability and support of certain language structures between the various versions of SQL Server. It does not affect the inner workings of the database engine version. It would try to prevent use of features and constructs that were not yet available in earlier versions. I just created a new database with compat level 80 on a 2008r2 server. Created a table with a single int column and populated it with a few rows. Then executed a select statement with a row_number function. My thought was, since the row_number function was only introduced in 2005, this would throw an error in compat 80 mode. But to my surprise this worked fine. Then, surely, the compat rules are only evaluated once you 'save something'. So I created a stored proc for my row_number statement. The stored proc creation went fine and I can perfectly execute it and obtain results. Could someone help me to better understand the working of compatibility mode? My understanding is obviously flawed. |
| RMAN backup Issue ( ORA-27052: unable to flush file data ) Posted: 19 Jun 2013 08:20 PM PDT This error comes while RMAN backup running backup pieces location is on NFS share mount point /b02 this error comes on regular basis mount point have enough space sometimes backup completed without any issue and most of time with this issue.I have searched on MOS and didn't find effective solutions for same.Please share your thoughts......... released channel: c1 released channel: c2 released channel: c3 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on c1 channel at 06/19/2013 01:40:45 ORA-27052: unable to flush file data Linux-x86_64 Error: 5: Input/output error Additional information: 1 RMAN> end-of-file RMAN> [oramsta@testordb1 logs]$ cat backuplog_err.txt incremental level 0 ORA-19510: failed to set size of 385577 blocks for file "/b02/backup/MSTA/df_MSTA81818469889_2hochn01_1_1_DALY20130618" (block size=8192) ORA-27045: unable to close the file ORA-19510: failed to set size of 353041 blocks for file "/b02/backup/MSTA/df_MSTA82818469934_2iochn1e_1_1_DALY20130618" (block size=8192) ORA-27045: unable to close the file ORA-27052: unable to flush file data [oramsta@testordb1 logs]$ |
| SQL Server AD groups membership seems cached - how to flush to test? Posted: 19 Jun 2013 06:54 PM PDT I am configuring a new, fully AD group based security scheme on an existing SQL Server 2008. When I change the group to which I belong to test my configs, it seems that there is quite a delay between the change and it appearing in SQL Server. To try and improve this I do: repadmin /syncall /Ade (to force full domain syncing) DBCC FREESYSTEMCACHE('TokenAndPermUserStore') I've also tried disconnecting and reconnecting the user in SSMS. Any idea how I can get the AD Droup permissions to apply faster ? Testing once every ten minutes is painfull ! Thank you ! Phil. |
| Will certain Database Compatibility cause performance degradation? Posted: 19 Jun 2013 07:11 PM PDT I'm running a variety of MSSQL Servers from 2005 to 2012 across several servers. I have many of these databases running in MSSQL 2000 compatibility. Will using an older compatibility in a newer version of mssql cause any performance issues? What is the different between these databases, other than older versions of SQL being able to access them? |
| SQL server agent SSIS error Posted: 19 Jun 2013 05:56 PM PDT I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't even know how to begin fixing this error. Where should I check first? Date a value of time Log Job History (MyJob) Step ID 1 Server PCTSQL004 Job Name MyJob Step Name Job_1 Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message Executed as user: CS\DmcSysManager. The process could not be created for step 1 of job 0x63BB5A86DB23F947866D2A806BE4CC6B (reason: A required privilege is not held by the client). The step failed.
|
| How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1) Posted: 19 Jun 2013 05:55 PM PDT I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O. I'm running MS SQL Server 2008 R2 SP1 Results in my SQL Log file look something like this (numbers fudged a little): about to log checkpoint end last target outstanding 2, avgWriteLatency 40ms Average Throughput: 0.67 MB/sec, I/O Saturation: 79, Context Switches 201 FlushCache: cleaned up 125 Bufs with 69 writes, in 1447ms (avoided 0 new dirty bufs) Ckpt dbid 9 phase 1 ended (8) about to log checkpoint begin. I don't really know how to read this, or break it down in a way that I get get anything truly meaningful out of it. What does 'last target outstanding mean?" Does the average write latency mean the overhead time it takes per write? or the time between writes? 40ms seems high, the physical drive is a 1TB, and it's RAID5 configured. What is I/O saturation? What does it have to do with the Context Switches. I'm assuming Context switches have something to do with multi-tasking. Changing between jobs/writes. FlushCache. I realize this has to do with clearing out the cache. What are the Bufs? Are these pages of data that needed to be written? What are the dirty Bufs? Why would they be avoided? A detailed breakdown would be appreciated. |
| MySQL using too much CPU Posted: 19 Jun 2013 07:21 PM PDT I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server: - CentOS 5.9
- 24gb Ram
- 8 Core CPU
- 50gb FusionIO disk for /var/lib/mysql
MySQL 5.5.23 top - 18:40:27 up 14 days, 4:43, 1 user, load average: 19.72, 22.62, 24.04 Tasks: 183 total, 3 running, 180 sleeping, 0 stopped, 0 zombie Cpu(s): 69.9%us, 0.4%sy, 0.0%ni, 29.2%id, 0.0%wa, 0.1%hi, 0.4%si, 0.0%st Mem: 24685224k total, 20172096k used, 4513128k free, 343420k buffers Swap: 2007284k total, 0k used, 2007284k free, 729004k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5446 mysql 15 0 18.4g 17g 6176 R 765.8 76.2 114437:38 mysqld I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file: [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock #innodb #innodb_log_file_size = 256M #innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout=50 innodb_file_per_table innodb_buffer_pool_size=16G innodb_buffer_pool_instances=4 #eliminating double buffering innodb_flush_method = O_DIRECT flush_time=86400 skip-name-resolve query_cache_limit=4M query_cache_size=256M sort_buffer_size=8M read_rnd_buffer_size=1M max_connections=5000 interactive_timeout=60 wait_timeout=300 connect_timeout=30 thread_cache_size=32 key_buffer=124M tmp_table_size=4096M max_heap_table_size=256M join_buffer=16M max_connect_errors=2000 table_cache=2048 thread_concurrency=12 long_query_time=5 log-slow-queries=/var/log/mysql-slow.log #table_definition_cache=384 max_allowed_packet=1024M #server-id=20 #log-bin=mysql-bin #expire_logs_days=10 event_scheduler=ON [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements? |
| query to get list of tables that has same columns from information_schema.columns? Posted: 19 Jun 2013 03:37 PM PDT idea is to list all the tables that has redundant columns across database using information_schema.columns! let's say table1 = dbo.ABC columns = id, name, department, designation table2= dbo.DEF columns= department, permissions, cube table3= dbo.GHI columns= street, zip, city, state the query should list the tables 'dbo.ABC' and 'dbo.DEF' |
| SIMPLE or FULL recovery model for databases? Posted: 19 Jun 2013 04:17 PM PDT When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this error: Microsoft OLE DB Provider for SQL Server (0x80040E14) The transaction log for database 'DATABASE NAME' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases The specific database is actually one of the smallest and most inactive databases on my server, so I have no idea how the log can be full on this database, and not the others. To shrink the log and making the database accessable again, I changed the recovery model from FULL to SIMPLE and shrinked the logical file log, with the following command alter database DATABASE NAME recovery simple go dbcc shrinkfile('LOG FILE LOGICAL NAME', 100) go
It helped, but now I need to understand WHY it helped, HOW this situation started and HOW to prevent this in the future? EDIT: Every night at 1 o'clock, we are doing a scripted backup of every database on the server. This is being done by a 31 line script where the most important part is set @Filename = 'D:\backup\' + convert(varchar, getDate(), 112) + ' - ' + @DBName + '.bak' set @Description = 'Full backup of database ' + @Filename BACKUP DATABASE @DBName TO DISK = @Filename WITH INIT , NOUNLOAD , NAME = @Description, NOSKIP , STATS = 10, NOFORMAT
Is the new recovery model and databaseshrink going to be a conflict with this script? We are not doing any other kind of backup of the databases, and therefore not the transaction logs, should we? |
| Using Change Data Capture with a frequently changing schema Posted: 19 Jun 2013 01:23 PM PDT We are following an agile development process, which in this case means that we frequently make changes to the database schema, e.g. adding new columns, moving data to other columns, etc. Is there a mechanism to update the CDC table to the new schema, and are there any best practices to how you deal with captured data when migrating the database schema? |
| Rearranging the output of mysql query Posted: 19 Jun 2013 12:54 PM PDT When i run the following Query: SELECT ticket.id, th.ticket_id , th.id, th.name, th.create_time FROM ticket_history th JOIN ticket ON th.ticket_id = ticket.id WHERE ( th.name LIKE '%\%Raw\%% %\%new\%%' AND EXISTS (SELECT * FROM ticket_history th1 WHERE th1.ticket_id = ticket.id AND th1.name LIKE '%\%Close') ) OR ( th.name LIKE '%\%Close' AND EXISTS (SELECT * FROM ticket_history th2 WHERE th2.ticket_id = ticket.id AND th2.name LIKE '%\%Raw\%% %\%new\%%') ) ORDER BY th.ticket_id , th.id ASC;
I Get the following output: +----+-----------+-----+-------------------------------------------+---------------------+ | id | ticket_id | id | name | create_time | +----+-----------+-----+-------------------------------------------+---------------------+ | 3 | 3 | 5 | %%2013060810000021%%Raw%%3 normal%%new%%3 | 2013-06-08 21:59:03 | | 3 | 3 | 82 | %%Close | 2013-06-10 11:50:49 | | 5 | 5 | 64 | %%2013060910000019%%Raw%%3 normal%%new%%5 | 2013-06-09 17:12:09 | | 5 | 5 | 85 | %%Close | 2013-06-10 11:51:10 | | 8 | 8 | 98 | %%2013061110000014%%Raw%%3 normal%%new%%8 | 2013-06-11 19:05:06 | | 8 | 8 | 109 | %%Close | 2013-06-17 23:57:35 | +----+-----------+-----+-------------------------------------------+---------------------+
In the above output the entry which contains "Raw" and "new" keyword show me when that particular ticket was open and the entry which contains "Close" keyword shows me when that particular ticket was closed. What changes i need to make in order to get the output as below: +----+-----------+----+----------------------+---------------------+ | id | ticket_id | id | Open time | Close_time | +----+-----------+----+----------------------+---------------------+ | 3 | 3 | 5 | 2013-06-08 21:59:03 | 2013-06-10 11:50:49 | | 5 | 5 | 64 | 2013-06-09 17:12:09 | 2013-06-10 11:51:10 | | 8 | 8 | 98 | 2013-06-11 19:05:06 | 2013-06-17 23:57:35 | +----+-----------+----+----------------------+---------------------+
|
| PostgreSQL 9.1.4 will not install service scripts Posted: 19 Jun 2013 12:57 PM PDT Same setup as before, but with a different install command. PostgreSQL refuses to install its service scripts into /etc/init.d/; in fact, I can't find them anywhere (find / -regex ".*postgres.*") command => '/bin/mount -o remount exec /tmp ; /bin/mkdir -m 0755 -p /opt/PostgreSQL/9.1 ; /tmp/setup/postgres-9.1.4.run \ --mode unattended \ --superaccount postgres \ --superpassword sUP34_se<uR3^P4sSw0rd \ --datadir /opt/PostgreSQL/9.1/data ; /usr/sbin/userdel postgres ; # for some reason, it does give itself (postgres) # permissions to its own files, which causes problems with initdb /bin/chmod 755 /opt/PostgreSQL/9.1 -R ; # so I nuke them all back to o=rwx,ug=rx /tmp/setup/postgres-9.1.4.run \ --mode unattended \ --superaccount postgres \ --superpassword sUP34_se<uR3^P4sSw0rd \ --datadir /opt/PostgreSQL/9.1/data ; /bin/mount -o remount /tmp ; /bin/touch /tmp/mysetup/postgres_installed ; ',
Is there any way I can get these files into /etc/init.d? Why won't the installer place them here in the first place?
Unfortunately, it is unlikely that I can use a different version of PostgreSQL - 9.1.4 is the most recent version that has been 'approved'. |
| Creating schema for a shop that sells items from multiple tables Posted: 19 Jun 2013 12:59 PM PDT I am trying to create a table in mySQl that contains a list of all the items that a shops. For example: shops (shop_ID (PK), shop_name) shops_and_items (shop_ID (PK), item_ID (PK), price) The issue I'm facing is that a shop can sell two types of products. For example, furniture and clothing. In my database, I already have separate tables for both furniture items and clothing items. The suggested shops_and_items schema therefore does not work. I'm wondering, what is the best way to go about it. Should I create two tables shop_and_furniture_items and shop_and_clothing_items ? |
| What is wrong with table_cache hit rate? Posted: 19 Jun 2013 04:02 PM PDT In my.cnf I have: table_cache = 524288 open_files_limit = 65535
Both are at max allowed value for mysql config. Both are less than max open file limit: # cat /proc/sys/fs/file-max 2097152
MySQL Variable Status: mysql> SHOW STATUS LIKE 'open%'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Open_files | 195 | | Open_streams | 0 | | Open_table_definitions | 594 | | Open_tables | 853 | | Opened_files | 257662 | | Opened_table_definitions | 0 | | Opened_tables | 0 | +--------------------------+--------+ 7 rows in set (0.00 sec)
Server has 32GB RAM. Mostly free! Still, when I run mysqltuner script: It says: [!!] Table cache hit rate: 13% (853 open / 6K opened) Any reason table_cache hit rate is poor? |
| Creating and tuning a materialized view in oracle for use by Cognos BI Posted: 19 Jun 2013 04:17 PM PDT I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to select its contents for fulfilling the incremental load in a timely fashion. The data is currently taking +5 hours to move between Oracle and SQL Server (Cognos 'Staging' layer). The source code for this scenario can be accessed via the following URL: https://github.com/pwl91/source_code I would like to create a materialized view that will use select * from ifsapp.fb_cust_ord_lines as its source, this will need to refresh fast (i.e. quickly) every day at 2:00. I have attempted this and below is the result: create materialized view ifsapp.fb_bi_cust_ord_lines_mv tablespace "IFSAPP_DATA" build immediate using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace "IFSAPP_DATA" refresh fast using default local rollback segment as select * from ifsapp.fb_customer_order_lines ; create index ifsapp.fb_fb_bi_cust_ord_lines_mv_i on ifsapp.fb_bi_cust_ord_lines_mv ( order_no, line_no, rel_no, line_item_no ) pctfree 10 initrans 2 maxtrans 255 tablespace IFSAPP_INDEX_16K ;
I know that this will not be 100% correct (maybe not even 50%) but I would really appreciate some assistance. Please ask if you require any further information |
| Insert performance with Geography column Posted: 19 Jun 2013 02:37 PM PDT I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly. I started out with no geography column and it took 6 minutes, then I added a geography column and it took 20 minutes (again same data). Then I added a spatial index and it took 1 hour and 45 minutes. I'm new at anything spatial, but this seems like really bad performance. Is there anything I can do to help speed this up or is this just the performance I'm going to see when dealing with SQL Server spatial data? |
| how to verify mysql backup file Posted: 19 Jun 2013 01:46 PM PDT Is There any tool to verify the backup taken using mysqldump without manual restoration ? I have to take full backup daily so it is difficult for me to verify the backups daily by restoring them. As it will take time and also temporary disk space. Please suggest me how to verify my backup online |
| Can I update an Access table with an Oracle table? Posted: 19 Jun 2013 01:28 PM PDT I have an Access database with a table with a unique key and price field among other fields. I have been told I have access to an Oracle database with those prices in it and the linking Key. Can I run an update query in Access to update those price fields with values in the Oracle table or do I need to import the Oracle table into Access first and then run the update query from that access table? I'm pretty well up on Access but have never had to link it with another SQL database system. |
| How to schedule PostgreSQL replication? Posted: 19 Jun 2013 06:32 PM PDT I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen. I'll be using postgres internal replication solution but as far as I understand, every event will be replicated just as it is achieved; for example, if I insert some data into the master, automatically it will be replicated to the slave...Am I right? Well, is that so, I was searching for way of scheduling this replications as a passive backup but the thing is, I need for example, every monday on the night the database gets replicated with all the data it had inserted in it that day to a slave database in Amazon EC2 virtual servers. I was thinking on using cron as a first approach but reading postgres documentation I got aware that practically only configuring the necessary files, postgres do it for you... So, how could I schedule replication in just desired moments? PD.-The schedule may vary so it needs to be completely generic, also...I'm using Ubuntu 12.04 and PostgreSQL 9.1 as in the master as in the slave. Thanks |
| Hierarchical Structure Posted: 19 Jun 2013 03:32 PM PDT Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes... I'm not an expert, but I've done research but it's all theoretical at this point of course, and I don't have hands on experience with the implementation that's for sure. I think my options are something like 'adjacency lists' or 'nested sets'? Any guidance into the right direction would be very much appreciated! |
| force PostgreSQL into recovery mode Posted: 19 Jun 2013 07:32 PM PDT I have a somewhat strange question. Is there a way to manually force PostgreSQL into recovery mode? I've not been able to find aything in the documentation. I've been working with a script monitoring a postgresql database and need to do some testing. /Peter |
| Dictate edition during unattended install? Posted: 19 Jun 2013 02:59 PM PDT I am creating an automated build of SQL Server installation which is run from a batch file. Is there any way to change the registry, or other method, of deciding which SQL Server edition (Either Enterprise or Standard) will be installed, or are the two versions still separate builds? If they are separate builds, I guess I could get around it, by having a "master" batch file call an appropriate "child" batch file, but this would mean having a folder which contains both sets of binaries, which I rather avoid. |
| Best practice for tempdb log file Posted: 19 Jun 2013 04:11 PM PDT I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file. Here's the strategy that I'm presently using with my tempdb: - I have used the recommendations of Paul Randal on how to split my tempdb data files
- I have set the size of my tempdb data files to their maximum and disabled autogrowth. For example, i have 100gb of free disk space and set the size of my 8 tempdb data files to 10gb each. This prevent fragmentation on the disk as recommended by Brent Ozar and also I have 20gb free for my log file.
But like I said, nobody is talking about the tempdb log file. What should I do with it? On my setup, this file is at the same place as the tempdb data files. What is the size and the autogrowth value that I should use with the tempdb log file? |
| Partition Fact Table Posted: 19 Jun 2013 04:32 PM PDT In one of my fact table which has close to 25 million records in it and now when a cognos team try to run the report on top of that it takes lot of time, So i am thinking of partitioning the table we store the records in fact table based on daily basis and we do have have the id calaendarday dma in the fact table as data type int.So is there a way i can partition this fact table based on id calaendarday dma? Please help me on the above query as it is impacting the cognos reports |
| SQL Server 2008 R2 corrupted after changing MAXDOP Posted: 19 Jun 2013 01:45 PM PDT My SQL Server 2008 R2 doesn't work after changing the MAXDOP parameter on instances. I have 6 instances located on the same physical server and I changed MAXDOP from 1 to 2 on 5 instances. After that all these 5 instances don't work. After restart of the server any services don't start and I just see the error The request failed or the service did not respond in a timely fashion. Consult the event log or other application error logs for details In Event Viewer I didn't find anything useful, also error log wasn't updated when I tried to run instance. Also I noticed a strange thing. When I open the Configuration Manager and open properties of instance, I don't see the fonts of parameter's name in Advanced tab. In another tabs everything is fine. The screen is attached. Does anybody faced the same problem? I don't have any idea how repair this... 
upd: By the way, i tried start sql server manually from command line with -f or -m parameter but nothing.. I just see empty error window |
| Applying user-defined fields to arbitrary entities Posted: 19 Jun 2013 05:32 PM PDT Currently we have an old (rather crude) system that has user-defined fields, which are mapped against rows in arbitrary tables. This was an after-the-fact modification based on a customer request, and it wasn't really designed to scale well. Our system has around 60 different types of entities, which makes things even more complicated. Essentially the implementation looks like this: USER_DEFINED_FIELDS:
UDF_ID int ENTITY_TYPE int UDF_NAME nvarchar(64) UDF_DATA_TYPE int UDF_FORMAT nvarchar(16) UDF_MASK nvarchar(24) UDF_FLAGS int
UDF_VALUES_NUMBER:
UDF_ID int ENTITY_ID int VALUE int MODIFIED datetime
UDF_VALUES_TEXT:
UDF_ID int ENTITY_ID int VALUE nvarchar(255) MODIFIED datetime
etc... This gets nice and fun when we generate our own ways to index compound primary keys, but that's another DailyWTF-worthy story. Obviously this is pretty hideous, and leads to some spectacularly horrific queries being generated, but it's worked alright for now because we limit each entity to a maximum of 5 user-defined fields. As a quick disclaimer, I wasn't with the company when this design decision was made! ;) Anyway, we're about to start a shiny new project and will inevitably need a better way of doing this, with no restrictions on the number of UDFs we can apply to entities, increased performance, and less horror in the generated query department. If we could run a NoSQL solution like Mongo or Redis I'd be happy and wouldn't need to bother you all, but sadly that's not an option. Instead, I need a way to do this from within SQL Server 2008 R2. So far, I've come up with the following options: - Individual UDF table for each entity type, with identical structures.
- Benefits: Queries are faster and simpler, solves the compound index problem, can search by UDF.
- Downsides: Table versioning is more cumbersome, lots of extra tables, difficult to index across similar entity types (e.g. purchase orders and sales orders)
- Binary blob UDF column in every entity's table.
- Benefits: Really fast: no fetch overhead, no extra JOINs.
- Downsides: Can't search by UDF, extra proprietary code required.
- XML column in every entity's table.
- Benefits: Fast like binary blobs, seems to be indexable.
- Downsides: No idea how they work - looks complicated!
So, do any of these ideas have merit? Is there a better alternative? |
| Connect to SQL Server behind NAT from remote machine Posted: 19 Jun 2013 02:32 PM PDT The scenario is that this is a SQL Server behind a corporate firewall with your standard setup to allow only minimal outbound traffic and zero inbound. However, port 80/443 are open, per the usual allowance. Has anyone tried, or is it possible to - Connect to a remote machine port 80, say using Telnet.
- On the local machine, note the bound address and port
[1], e.g. netstat - Disconnect from telnet. Start port forwarder and listen on
[1], mapped to SQL Server TCP/IP listener - On remote machine, connect to SQL Server on
[1] What do you think? I just need to get some testing done and I don't want to move my huge database outside for this one-off. I am familiar with and have used UDP hole punching for NAT traversal, mainly for push to mobile phones, but am interested to know if this works with TCP as well. References: I am open to other suggestions. 
|
| Select * from statement execute very slowly, innodb io read speed is low Posted: 19 Jun 2013 08:32 PM PDT I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: 5.1 ) and run the same applications. In server A, the row number of the table ap_statistic is about 22512379, in server B, the row number of the table is 41438751. Of course the query running on server A is faster than server B, but what is strange is the query on server B is extreme slow, it takes more than 3 hours where in server A it just takes 10 minutes. I use some tool to monitor system status and find that when the query is running in server A, system IO read speed is about 20~30M/s, but in server B it's 2~3M/s. I've tried to clean linux cache and restart mysql server, all is the same result. And I tried to restored DB from server B to server A, so the query in Server A is very very slow and io read speed is very slow. I want to know why this happen? the ap_statistic table data in server A is generated by normally running and table data in server B is generated by a stored procedure. the table scheme is: CREATE TABLE `ap_statistic` ( `ap_statisticId` BIGINT(20) UNSIGNED NOT NULL, `deviceId` INT(11) UNSIGNED NOT NULL DEFAULT '0', `macaddress` VARCHAR(100) DEFAULT NULL, `check_time` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', `ap_count` INT(10) UNSIGNED NOT NULL DEFAULT '0', `wlan` VARCHAR(64) DEFAULT NULL, `radio` VARCHAR(50) DEFAULT NULL, `bssid` VARCHAR(32) DEFAULT NULL, `zd_ap_name` VARCHAR(64) DEFAULT NULL, `channel` INT(2) DEFAULT NULL, `uplinkRSSI` INT(3) DEFAULT '0', `downlinkRSSI` INT(3) DEFAULT '0', `txBytes` BIGINT(20) DEFAULT '0', `rxBytes` BIGINT(20) DEFAULT '0', `txPkts` BIGINT(20) DEFAULT '0', `rxPkts` BIGINT(20) DEFAULT '0', `hops` INT(1) DEFAULT '0', `numDownlink` INT(3) DEFAULT '0', `distance` INT(4) DEFAULT '0', `phyerr` INT(11) DEFAULT '0', `max_num_clients` INT(3) DEFAULT '0', `max_mesh_downlinks` INT(1) DEFAULT '0', `airtime` INT(3) DEFAULT '0', `uptimePercentage` INT(3) DEFAULT '0', `total_num_clients` INT(3) DEFAULT '0', `tx_actual_throughput` BIGINT(20) DEFAULT '0', `rx_actual_throughput` BIGINT(20) DEFAULT '0', `tunnelMode` VARCHAR(32) DEFAULT NULL, `externalIp` VARCHAR(64) DEFAULT NULL, `externalPort` VARCHAR(32) DEFAULT NULL, `level` INT(1) DEFAULT '1' `essid` VARCHAR(64) DEFAULT NULL, `total_client_join` INT(11) DEFAULT '0', PRIMARY KEY (`ap_statisticId`), KEY `check_time` (`check_time`), KEY `macaddress` (`macaddress`), KEY `deviceId` (`deviceId`) ) ENGINE=INNODB DEFAULT CHARSET=utf8
the follows are the table file info and some outputs of the monitor tools Server B -rw-rw---- 1 mysql mysql 18568183808 Oct 11 14:52 ap_statistic.ibd [root@localhost itms]# filefrag ./ap_statistic.ibd ./ap_statistic.ibd: 164 extents found, perfection would be 159 extents TABLE Non_unique Key_name Seq_in_index Column_name COLLATION Cardinality Sub_part Packed NULL Index_type COMMENT ------------ ---------- ---------- ------------ -------------- --------- ----------- -------- ------ ------ ---------- ------- ap_statistic 0 PRIMARY 1 ap_statisticId A 41438751 (NULL) (NULL) BTREE ap_statistic 1 check_time 1 check_time A 10320 (NULL) (NULL) BTREE ap_statistic 1 macaddress 1 macaddress A 16 (NULL) (NULL) YES BTREE ap_statistic 1 deviceId 1 deviceId A 16 (NULL) (NULL) BTREE mysql>show status; Variable_name Value Aborted_clients 0 Aborted_connects 0 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 1256 Bytes_sent 8844 Com_admin_commands 0 Com_assign_to_keycache 0 Com_alter_db 0 Com_alter_db_upgrade 0 Com_alter_event 0 Com_alter_function 0 Com_alter_procedure 0 Com_alter_server 0 Com_alter_table 0 Com_alter_tablespace 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_binlog 0 Com_call_procedure 0 Com_change_db 1 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_event 0 Com_create_function 0 Com_create_index 0 Com_create_procedure 0 Com_create_server 0 Com_create_table 0 Com_create_trigger 0 Com_create_udf 0 Com_create_user 0 Com_create_view 0 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_event 0 Com_drop_function 0 Com_drop_index 0 Com_drop_procedure 0 Com_drop_server 0 Com_drop_table 0 Com_drop_trigger 0 Com_drop_user 0 Com_drop_view 0 Com_empty_query 0 Com_execute_sql 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 0 Com_insert_select 0 Com_install_plugin 0 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_release_savepoint 0 Com_rename_table 0 Com_rename_user 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_rollback_to_savepoint 0 Com_savepoint 0 Com_select 1 Com_set_option 3 Com_show_authors 0 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 0 Com_show_column_types 0 Com_show_contributors 0 Com_show_create_db 0 Com_show_create_event 0 Com_show_create_func 0 Com_show_create_proc 0 Com_show_create_table 1 Com_show_create_trigger 0 Com_show_databases 0 Com_show_engine_logs 0 Com_show_engine_mutex 0 Com_show_engine_status 0 Com_show_events 0 Com_show_errors 0 Com_show_fields 1 Com_show_function_status 0 Com_show_grants 0 Com_show_keys 1 Com_show_master_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_plugins 0 Com_show_privileges 0 Com_show_procedure_status 0 Com_show_processlist 0 Com_show_profile 0 Com_show_profiles 0 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 21 Com_show_storage_engines 0 Com_show_table_status 0 Com_show_tables 0 Com_show_triggers 0 Com_show_variables 0 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reprepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_uninstall_plugin 0 Com_unlock_tables 0 Com_update 0 Com_update_multi 0 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression ON Connections 323 Created_tmp_disk_tables 1 Created_tmp_files 5 Created_tmp_tables 2 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 1 Handler_delete 0 Handler_discover 0 Handler_prepare 0 Handler_read_first 0 Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 39 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 37 Innodb_buffer_pool_pages_data 43392 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 43822 Innodb_buffer_pool_pages_free 637198 Innodb_buffer_pool_pages_misc 562 Innodb_buffer_pool_pages_total 681152 Innodb_buffer_pool_read_ahead_rnd 9 Innodb_buffer_pool_read_ahead_seq 27 Innodb_buffer_pool_read_requests 36489397 Innodb_buffer_pool_reads 27421 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 4165371 Innodb_data_fsyncs 5228 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 1 Innodb_data_pending_writes 0 Innodb_data_read 626216960 Innodb_data_reads 36565 Innodb_data_writes 293947 Innodb_data_written 1792826880 Innodb_dblwr_pages_written 43822 Innodb_dblwr_writes 830 Innodb_log_waits 0 Innodb_log_write_requests 492588 Innodb_log_writes 268248 Innodb_os_log_fsyncs 2130 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 356559872 Innodb_page_size 16384 Innodb_pages_created 5304 Innodb_pages_read 38087 Innodb_pages_written 43822 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 28637 Innodb_rows_inserted 306449 Innodb_rows_read 16579740 Innodb_rows_updated 887251 Key_blocks_not_flushed 0 Key_blocks_unused 212928 Key_blocks_used 1415 Key_read_requests 393323 Key_reads 16 Key_write_requests 102461 Key_writes 102439 Last_query_cost 9142769.199000 Max_used_connections 19 Not_flushed_delayed_rows 0 Open_files 24 Open_streams 0 Open_table_definitions 142 Open_tables 146 Opened_files 592 Opened_table_definitions 0 Opened_tables 0 Prepared_stmt_count 0 Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0 Queries 1578897 Questions 30 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 2 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 0 Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 Ssl_accept_renegotiates 0 Ssl_accepts 0 Ssl_callback_cache_hits 0 Ssl_cipher Ssl_cipher_list Ssl_client_connects 0 Ssl_connect_renegotiates 0 Ssl_ctx_verify_depth 0 Ssl_ctx_verify_mode 0 Ssl_default_timeout 0 Ssl_finished_accepts 0 Ssl_finished_connects 0 Ssl_session_cache_hits 0 Ssl_session_cache_misses 0 Ssl_session_cache_mode NONE Ssl_session_cache_overflows 0 Ssl_session_cache_size 0 Ssl_session_cache_timeouts 0 Ssl_sessions_reused 0 Ssl_used_session_cache_entries 0 Ssl_verify_depth 0 Ssl_verify_mode 0 Ssl_version Table_locks_immediate 1549525 Table_locks_waited 0 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 17 Threads_created 322 Threads_running 2 Uptime 8093 Uptime_since_flush_status 8093 mysql>show variables; Variable_name Value auto_increment_increment 1 auto_increment_offset 1 autocommit ON automatic_sp_privileges ON back_log 50 big_tables OFF binlog_cache_size 32768 binlog_direct_non_transactional_updates OFF binlog_format STATEMENT bulk_insert_buffer_size 8388608 character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_filesystem binary character_set_results utf8 character_set_server utf8 character_set_system utf8 collation_connection utf8_general_ci collation_database utf8_general_ci collation_server utf8_general_ci completion_type 0 concurrent_insert 1 connect_timeout 10 date_format %Y-%m-%d datetime_format %Y-%m-%d %H:%i:%s default_week_format 0 delay_key_write ON delayed_insert_limit 100 delayed_insert_timeout 300 delayed_queue_size 1000 div_precision_increment 4 engine_condition_pushdown ON error_count 0 event_scheduler OFF expire_logs_days 0 flush OFF flush_time 0 foreign_key_checks ON ft_boolean_syntax + -><()~*:""&| ft_max_word_len 84 ft_min_word_len 4 ft_query_expansion_limit 20 ft_stopword_file (built-in) general_log OFF group_concat_max_len 1024 have_community_features YES have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_geometry YES have_innodb YES have_ndbcluster NO have_openssl DISABLED have_partitioning NO have_query_cache YES have_rtree_keys YES have_ssl DISABLED have_symlink YES hostname localhost.localdomain identity 0 ignore_builtin_innodb OFF init_connect init_file init_slave innodb_adaptive_hash_index ON innodb_additional_mem_pool_size 67108864 innodb_autoextend_increment 8 innodb_autoinc_lock_mode 1 innodb_buffer_pool_size 11159994368 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_io_threads 4 innodb_file_per_table ON innodb_flush_log_at_trx_commit 2 innodb_flush_method O_DIRECT innodb_force_recovery 0 innodb_lock_wait_timeout 120 innodb_locks_unsafe_for_binlog ON innodb_log_buffer_size 134217728 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir ./ innodb_max_dirty_pages_pct 90 innodb_max_purge_lag 0 innodb_mirrored_log_groups 1 innodb_open_files 300 innodb_rollback_on_timeout OFF innodb_stats_on_metadata ON innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 innodb_use_legacy_cardinality_algorithm ON insert_id 0 interactive_timeout 28800 join_buffer_size 268435456 keep_files_on_create OFF key_buffer_size 268435456 key_cache_age_threshold 300 key_cache_block_size 1024 key_cache_division_limit 100 large_files_support ON large_page_size 0 large_pages OFF last_insert_id 0 lc_time_names en_US license Commercial local_infile ON locked_in_memory OFF log OFF log_bin OFF log_bin_trust_function_creators OFF log_bin_trust_routine_creators OFF log_output FILE log_queries_not_using_indexes OFF log_slave_updates OFF log_slow_queries OFF log_warnings 1 long_query_time 10.000000 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 1 max_allowed_packet 134217728 max_binlog_cache_size 18446744073709547520 max_binlog_size 1073741824 max_connect_errors 10 max_connections 300 max_delayed_threads 20 max_error_count 64 max_heap_table_size 268435456 max_insert_delayed_threads 20 max_join_size 18446744073709551615 max_length_for_sort_data 1024 max_prepared_stmt_count 16382 max_relay_log_size 0 max_seeks_for_key 18446744073709551615 max_sort_length 1024 max_sp_recursion_depth 0 max_tmp_tables 32 max_user_connections 0 max_write_lock_count 18446744073709551615 min_examined_row_limit 0 multi_range_count 256 myisam_data_pointer_size 6 myisam_max_sort_file_size 9223372036853727232 myisam_mmap_size 18446744073709551615 myisam_recover_options OFF myisam_repair_threads 1 myisam_sort_buffer_size 8388608 myisam_stats_method nulls_unequal myisam_use_mmap OFF net_buffer_length 16384 net_read_timeout 30 net_retry_count 10 net_write_timeout 60 new OFF old OFF old_alter_table OFF old_passwords OFF open_files_limit 10240 optimizer_prune_level 1 optimizer_search_depth 62 optimizer_switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on port 3306 preload_buffer_size 32768 profiling OFF profiling_history_size 15 protocol_version 10 pseudo_thread_id 18 query_alloc_block_size 8192 query_cache_limit 1048576 query_cache_min_res_unit 4096 query_cache_size 0 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 rand_seed1 rand_seed2 range_alloc_block_size 4096 read_buffer_size 67108864 read_only OFF read_rnd_buffer_size 67108864 relay_log relay_log_index relay_log_info_file relay-log.info relay_log_purge ON relay_log_space_limit 0 report_host report_password report_port 3306 report_user rpl_recovery_rank 0 secure_auth OFF secure_file_priv server_id 0 skip_external_locking ON skip_name_resolve ON skip_networking OFF skip_show_database OFF slave_compressed_protocol OFF slave_exec_mode STRICT slave_net_timeout 3600 slave_skip_errors OFF slave_transaction_retries 10 slow_launch_time 2 slow_query_log OFF sort_buffer_size 16777216 sql_auto_is_null ON sql_big_selects ON sql_big_tables OFF sql_buffer_result OFF sql_log_bin ON sql_log_off OFF sql_log_update ON sql_low_priority_updates OFF sql_max_join_size 18446744073709551615 sql_mode sql_notes ON sql_quote_show_create ON sql_safe_updates OFF sql_select_limit 18446744073709551615 sql_slave_skip_counter sql_warnings OFF ssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine MyISAM sync_binlog 0 sync_frm ON system_time_zone UTC table_definition_cache 256 table_lock_wait_timeout 50 table_open_cache 512 table_type MyISAM thread_cache_size 0 thread_handling one-thread-per-connection thread_stack 262144 time_format %H:%i:%s time_zone +08:00 timed_mutexes OFF timestamp 1349946061 tmp_table_size 1073741824 transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ unique_checks ON updatable_views_with_limit YES version 5.1.53-enterprise-commercial-pro version_comment MySQL Enterprise Server - Pro Edition (Commercial) version_compile_machine x86_64 version_compile_os unknown-linux-gnu wait_timeout 28800 warning_count 0 mysql> show innodb status\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 121011 10:22:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 39 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3806, signal count 3778 Mutex spin waits 0, rounds 282892, OS waits 2075 RW-shared spins 1969, OS waits 864; RW-excl spins 2336, OS waits 749 ------------ TRANSACTIONS ------------ Trx id counter 0 5303968 Purge done for trx's n:o < 0 5303951 undo n:o < 0 0 History list length 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30336, OS thread id 1189509440 MySQL thread id 520, query id 1861594 localhost root show innodb status ---TRANSACTION 0 5303967, not started, process no 30336, OS thread id 1188710720 MySQL thread id 526, query id 1861593 127.0.0.1 root ---TRANSACTION 0 5303962, not started, process no 30336, OS thread id 1186314560 MySQL thread id 519, query id 1861555 127.0.0.1 root ---TRANSACTION 0 5303952, not started, process no 30336, OS thread id 1188444480 MySQL thread id 515, query id 1861567 127.0.0.1 root ---TRANSACTION 0 5303948, not started, process no 30336, OS thread id 1187912000 MySQL thread id 516, query id 1861566 127.0.0.1 root ---TRANSACTION 0 5303937, not started, process no 30336, OS thread id 1190308160 MySQL thread id 511, query id 1861568 127.0.0.1 root ---TRANSACTION 0 0, not started, process no 30336, OS thread id 1090791744 MySQL thread id 18, query id 1596073 172.18.112.84 root ---TRANSACTION 0 5303959, ACTIVE 63 sec, process no 30336, OS thread id 1090525504 fetching rows, thread declared inside InnoDB 500 mysql tables in use 1, locked 0 MySQL thread id 17, query id 1861400 localhost root Sending data select * from ap_statistic Trx read view will not see trx with id >= 0 5303960, sees < 0 5303960 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 63521 OS file reads, 294656 OS file writes, 5641 OS fsyncs 1 pending preads, 0 pending pwrites 149.38 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 318, seg size 320, 63593 inserts, 63593 merged recs, 9674 merges Hash table size 22086161, node heap has 607 buffer(s) 0.08 hash searches/s, 0.26 non-hash searches/s --- LOG --- Log sequence number 15 2873617336 Log flushed up to 15 2873617336 Last checkpoint at 15 2873617336 0 pending log writes, 0 pending chkp writes 269102 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 12452785320; in additional pool allocated 15261440 Dictionary memory allocated 789024 Buffer pool size 681152 Free buffers 610013 Database pages 70532 Modified db pages 0 Pending reads 1 Pending writes: LRU 0, flush list 0, single page 0 Pages read 65043, created 5488, written 45924 149.38 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 888 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 30336, id 1185782080, state: waiting for server activity Number of rows inserted 336555, updated 1112311, deleted 28681, read 29200669 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8258.58 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set, 1 warning (0.00 sec) ERROR: No query specified iostat -dx 2 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 2.50 141.50 11.50 4516.00 112.00 2258.00 56.00 30.25 0.95 6.23 5.70 87.25 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda2 0.00 2.50 141.50 11.50 4516.00 112.00 2258.00 56.00 30.25 0.95 6.23 5.70 87.25 dm-0 0.00 0.00 141.50 14.00 4516.00 112.00 2258.00 56.00 29.76 0.97 6.24 5.62 87.35 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 3.00 0.00 154.50 0.00 4932.00 0.00 2466.00 0.00 31.92 0.93 6.04 6.04 93.25 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda2 3.00 0.00 154.50 0.00 4932.00 0.00 2466.00 0.00 31.92 0.93 6.04 6.04 93.25 dm-0 0.00 0.00 157.50 0.00 4932.00 0.00 2466.00 0.00 31.31 0.95 6.04 5.93 93.40 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 3.00 1.50 150.50 1.50 4804.00 24.00 2402.00 12.00 31.76 0.94 6.15 6.14 93.40 sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sda2 3.00 1.50 150.50 1.50 4804.00 24.00 2402.00 12.00 31.76 0.94 6.15 6.14 93.40 dm-0 0.00 0.00 153.50 3.00 4804.00 24.00 2402.00 12.00 30.85 0.95 6.08 5.97 93.50 dm-1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 vmstat 2 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 1 1 16 27358488 18152 115500 0 0 2558 0 1193 8634 14 1 73 12 0 1 1 16 27346840 18168 115500 0 0 2356 12 1174 9129 14 2 73 12 0 2 1 16 27334320 18172 115504 0 0 2522 0 1184 8918 14 1 73 12 0 1 1 16 27321812 18180 115496 0 0 2456 12 1183 7357 13 1 74 12 0 1 1 16 27310132 18180 115504 0 0 2344 12 1174 6990 12 2 74 12 0 1 1 16 27297608 18184 115508 0 0 2506 0 1182 6203 12 2 74 11 0 1 1 16 27285444 18200 115504 0 0 2420 90 1187 9667 13 2 73 12 0 1 1 16 27277640 18200 115508 0 0 2248 0 1165 8103 19 2 69 11 0 2 1 16 27265380 18204 115504 0 0 2498 0 1179 5690 13 1 74 12 0 4 1 16 27252972 18216 115508 0 0 2434 12 1178 6096 14 1 74 12 0 1 1 16 27241032 18232 115496 0 0 2520 0 1181 9252 12 1 75 11 0 2 1 16 27229136 18240 115508 0 0 2468 10 1178 7116 13 1 74 12 0 1 0 16 27630612 18248 115508 0 0 1536 20 1121 4082 13 1 79 7 0 mpstat -P ALL 2 02:48:57 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s 02:48:59 PM all 13.69 0.00 1.31 11.56 0.00 0.62 72.81 1190.95 02:48:59 PM 0 33.67 0.00 0.50 0.00 0.00 0.00 65.83 1006.03 02:48:59 PM 1 6.53 0.00 0.50 92.96 0.50 0.50 0.00 160.80 02:48:59 PM 2 1.01 0.00 0.50 0.00 0.00 0.00 98.49 0.00 02:48:59 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 100.50 3.52 02:48:59 PM 4 35.68 0.00 1.01 0.00 0.00 1.01 62.81 13.57 02:48:59 PM 5 4.52 0.00 0.00 0.00 0.00 0.00 96.48 0.50 02:48:59 PM 6 3.52 0.00 0.00 0.00 0.00 0.00 96.98 0.50 02:48:59 PM 7 25.13 0.00 7.54 0.00 0.00 4.02 63.82 6.03 02:48:59 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s 02:49:01 PM all 12.50 0.00 1.19 11.69 0.00 0.56 74.06 1177.11 02:49:01 PM 0 22.89 0.00 1.49 0.00 0.00 1.49 74.13 995.52 02:49:01 PM 1 5.97 0.00 0.50 92.54 0.00 0.50 0.00 159.70 02:49:01 PM 2 0.50 0.00 0.50 0.50 0.00 0.00 98.01 1.99 02:49:01 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 99.50 2.49 02:49:01 PM 4 45.77 0.00 1.49 0.00 0.00 0.50 51.24 11.94 02:49:01 PM 5 0.00 0.00 0.00 0.00 0.00 0.00 99.50 0.50 02:49:01 PM 6 0.50 0.00 0.00 0.00 0.00 0.00 99.00 0.50 02:49:01 PM 7 23.38 0.00 5.47 0.00 0.00 1.99 68.16 4.48 02:49:01 PM CPU %user %nice %system %iowait %irq %soft %idle intr/s 02:49:03 PM all 13.05 0.00 1.12 11.62 0.00 0.50 73.70 1179.00 02:49:03 PM 0 43.50 0.00 0.50 0.00 0.00 0.00 56.00 1000.50 02:49:03 PM 1 6.50 0.00 1.00 93.00 0.00 0.50 0.00 157.00 02:49:03 PM 2 1.50 0.00 0.50 0.00 0.00 0.00 98.50 0.00 02:49:03 PM 3 0.00 0.00 0.00 0.00 0.00 0.00 100.00 2.50 02:49:03 PM 4 32.50 0.00 1.50 0.00 0.00 1.00 65.50 13.00 02:49:03 PM 5 11.00 0.00 4.00 0.00 0.00 1.50 83.50 0.50 02:49:03 PM 6 0.00 0.00 0.00 0.00 0.00 0.00 100.00 0.00 02:49:03 PM 7 10.50 0.00 2.00 0.00 0.00 1.00 87.00 5.50
|
No comments:
Post a Comment