Blogger

Baseline linux server v1 - part 2

posted Feb 21, 2014, 9:31 PM by Ricardo Fonseca   [ updated Feb 23, 2014, 8:18 PM ]

Following the last post here is a set of scripts to monitor linux generic performance counters as well as MySQL.


The data that will be gathered will be done according to your request. Data from CPU and / or memory and / or disk io and / or MySQL. The data generated will be dispatched to different csv files according to your configuration choices (by hour, day, week, month). This is mainly to ease the process when you will be in the process comparing a specific time frame with what people are saying abnormal activity against what was considered normal activity from historic baseline.


Script installation step:

  • make sure that all the prerequisites and requirements are fulfilled (prerequisites document)

  • extract all the script files to a folder of your choice. make sure to keep the structure of it as specified in the prerequisites document (baseline-installation-scripts.rar)

  • define personal script parameters, within baseline.sh file between the tags “BASELINE SCRIPT SETUP PARAMETERS ”

  • create a crontab item according to what you need to baseline and the frequency that fits your requirements.

  • finally, see the data coming ...

Then what ?:

This of course provide the baseline gathering process. Another good thing to do will be of course to find a way to quickly perform analysis of the data and find ways to extract, efficiently, meaningful information from it. To start I will direct you to the web. You will find may project to “graph” csv , others to split the data into quartiles and so forth.


Future step :

This post is part of a series that I will be doing on the baseline subject. The next phase will be to add to the baseline scripts the capability to handle Oracle and mongoDB databases specific counters.


Voilà, I hope you found the information here usefull and beneficial, enjoy. Please leave comment and suggestions , I will be more than glad to ear what you think.


Ricardo

baseline-linux-server feedback

baseline-linux-server feedback


The importance of having baseline of your servers - part 1

posted Feb 21, 2014, 9:31 PM by Ricardo Fonseca   [ updated Feb 23, 2014, 8:18 PM ]

Intro

I decided to discuss about this matter after one of those event where some people argue that performance decreased a lot on one of our MySQL server. To give you a little bit of context, the company where I work use largely the agile model. Not only for developers but sysadmin and dba as well. The event cited above occurred after a sprint release, but was it related, how could I be sure ?? Was it just some limitations on our system that had been reach, was it because some fundamental changes occurred in the way the applications was used that was generating a high load on the server ??

From there I had two axes to focus on to be able troubleshoot effectively  :

A. first : what I call “database code awarness”  


Portion of code deployed, running queries impacting database is not systematically shared to database administrators. Most of the time I have to dig to get the information. Therefore I couldn't quickly guide my scope of analysis around what has been developed in terms of query from our last sprint. Also there is the fact that sometimes we deploy features that we set in sleeping mode and are just not used for quite some time. A problem in the the beginning of a new sprint may come from various factors including start of functionality usage in a large scaled that was just turned on. Therefore it was impossible for me to confirm or infirm that the actual performance impact was from short term application change / additions .



B. second : the lack of baseline

For the past years I've always push back the clear fact that one requires to do and consolidate server baselines automatically and systematically. To do what ?  Well :


  • to be able to evaluate the cost of new development against server performance and have a clear view of the impacts on cpu, memory, io etc...

  • to be able to plan ahead, by doing historical you can do trends and with trend you could be proactive in the process of advising the stake orders about upgrades that will he required. Warning them ahead and spreading the news before it’s too late.

  • to let the developers aware of this process will probably motivate them more in the process to share information about what they do in terms of query load in their application code (point a). Also, they will be more careful when doing those changes knowing that there will be a clear visibility on the impact of those.

  • to be more effective in the troubleshooting process.  Too often I got into these situations that I go somehow blindly in the research of what may cause a performance problem. Issuing very basic performance snapshot ( almost every single time a different one) and trying to correlate information data about what the users are complaining about.

  • to standardize a project to create and manage baseline. This project ( set of scripts ) is based on largely known and long time adopted by many performance tools like iostat, vmstat, free which means that : 

    • the resource consumption of the script is very limited ( most of the data gathered is already computed)
    • the evolution of the script whenever the tools gets updated will be an easy process.
  • finally and most importantly to cover your ass! you need to be able to provide the right answers with concrete arguments to thpeople in charge and stockholders.

With the last event, I told to myself that it was enough. I had to create a baseline process in which the scope was to be small with different milestones . The goal is to make sure that I will take the time and then efforts required to appropriately achieve it and improve it.

From there I decided to create a baseline project that will provide for the first release counters on CPU (%user %sys), memory (swap, free) ,IO (process with most read,  process with most writes), and major MySQL monitoring counters( #threads, slow query, innodb query/sec ...) Like I said it was important to scope small to have a premier release that I will be able to put on all our Linux server knowing that the impact will be almost null.

The actual script and "how to" setup instructions will be provided soon in another post. I didn't want to mix the concerns "why" one need to have frequent baseline done versus the "how" to implement them.


Thank you


Ricardo


Guess who's back

posted Feb 7, 2014, 11:16 PM by Ricardo Fonseca

As a first post since quite a while, I start little and write a post with the information in regards my updated resume including all my latest role and qualifications.

We will find all the current data / experiences within the document :

And you will be able to find previous employment information following the link :

Talk to you soon with a post talking about the importance of a baseline.

Thank you.

Ricardo




Installation SQL Server 2008 Manual / Automatic

posted Oct 16, 2010, 2:21 PM by Ricardo Fonseca   [ updated Feb 17, 2011, 6:01 PM ]


Introduction


This post is here to provide a canvas for SQL Server 2008 installation , it fit most of the installation need. and describe brief information on every step on what option to select and why.

We take two perspective in this post the manual installation , step by step with print screen and the automatic installation process with a configuration template file where you specify the default directories and account name and password.

Please note that I for all account names and password are virtual and use only as illustration
. It will be up to you to set the values according to account credential that fit the best for your business.



Pre-Requisites

New local admin account

account name : dbadmin
account password : YourStrongPassword

Installation Process

As for advise, If anything goes not as indicated, or an error occur during the installation process. Make sur to transfer the information to the person in charge in your organisation.


Manual Installation Step by Step


Step 1

*


Note : Select the setup exe on the root folder, depending on your os subsystem it will know to run the x64 or x32 bits installation process

.

Step 2



Note : Select the “Installation” menu item on the left panel, then select the “New SQL Server Stand-alone installation or add features to an existing installation” menu item on the right panel



Step 3



Note : make sure to have all support requirements checked



.

Step 4




Note : the evaluation key will come automatically as this is a developer edition , just hit next button .


Step 5



Note : click the checkbox “I accept the license terms” option and hit next
.

Step 6



Note : at this stage , sql server will prompt you to install any requirements files missing , just hit install button
.

Step 7




Note : at this stage make sure that all support rules are checked and hit the next button

Step 8



Note : at this stage hit Select All button. and then the next button.

Step 9




Note : make sure you select the “default instance” radio button and that you have the instance id set to MSSQLSERVER

Step 10



Note : Space requirements step make sure you have all the requirements set and hit the next button


Step 11-1



Note : Set all account credentials to the dbadmin  credential , please refer to Manual Installation / Pre Requisites for details. Choose the startup-type according to the print-screen, Then proceed to the Collation Panel.


Setp 11-2


Note : at the Collation Panel you will need to change the Database Engine and Analysis Services to the collation Latin1_General_CI_AI in order to do so please follow steps indicated on the following print-screen


Step 11-3






Step 11-4





Step 11-5


Note : when both collation are correctly set you can hit the next button


Step 12-1



Note : at this stage you want to set the authentication mode to Mixed Mode. the Password for the system administrator account will be initially set to “YourStrongPassword”. After you will proceed and select dbadmin account as part of the SQL Server administrators. Once this is done you can select the Data Directories panel for advanced configuration



Step 12-2




Note : by default this can stay as it is unless we have setup the disksubsystem otherwise (example in production for maximum performance we have separated the logs files from the data files and tempdb file is also splitted). Therefore this is to be confirmed everytime we setup a new sql server installation. Then proceed to the filestream panel.


Step 12-3


Note : make sure the FileStream option is not checked and hit the next button.


Step 13-1


Note : at this stage, include the dbadmin account as part of the of the users who have administrative permissions for Analysis services. and then select the Data Directories panel.


Step 13-2



Note : by default this can stay as it is unless we have setup the disksubsystem otherwise (example in production for maximum performance we have separated the logs files from the data files and temp files can also be splitted). Therefore this is to be confirmed everytime we setup a new sql server installation. Then you can hit the next button.


Step 14




Note : at this stage make sure to select the radio button Install the native mode default and then hit the next button


Step 15



Note : uncheck all checkboxes and then hit the next button



Step 16



Note : at this stage make sure all installation rules are checked and then hit the Next Button.


Step 17


Note : Review all the options and configuration options that you have selected during the installation process and then hit the install Button.


Step 18



Note : Let the system proceed with installation and ask for person in charge to test the installation



Automatic Installation



As an alternative to the manual installation the SQL Server installation can be achieved in command line simply using the steps described below :
  • Proceed to command line
  • Proceed to the root directory of the media/folder where is located the SQL Server binaries (e:\ in my case)
  • Type the command
    • Setup.exe /ConfigurationFile=c:\MSSQLSERVER_Installation_ConfigurationFile.ini /QUIET
  • At this point the installation will start in silent mode
    • if the installation succeeds you will have a brief information advising you about it
    • if the installation fails you will have the detailes on wich step and why,
      • in that scenario the report will have to be transmitted to the person in charge

Important Notes

1. Make sure to change accounts name and password according to your business policy.
2. As for the manual steps described on the section Step 12-2 and 13-2 the configuration file may need to be updated with different data/log/temp locations drives and folder. This need to be addressed prior the installation within the configuration file.










High availability / High scalability pros and cons

posted Sep 11, 2010, 8:52 PM by Ricardo Fonseca   [ updated Sep 11, 2010, 9:13 PM ]

Following my last post, I summarize here a list of the pros and cons of the different solutions previously discussed.


High availability / scalability mode
Pros
Cons

Replication

- Transactional
- Merge
- Snapshot
- Peer-to-peer


[x] Data availability
[x] High scalability
[x] Can be mixed with db mirroring

[x] Good Reporting Solution
[x] Little down time

[x] No automatic fail over
[x] Possible Data Loss
[x] Latency on secondary server
[x] Adv. Setup
[x] Adv. Troubleshooting
[x] Schema driven

Log Shipping

[x] Data availability
[x] High scalability
[x] Easy Setup (FileSystem)
[x] Easy Troubleshooting
[x] can be mixed with db mirroring

[x] Good Reporting Solution
[x] Little down-time
[x] Same database can have multiple destination server

[x] No automatic fail over
[x] Latency on secondary server
[x] Possible Data Loss

[x] Need Shared Folder setup
[x] Require Folder Security
 
Database Mirroring

[x] Automatic failover with witness setup
[x] High availability
[x] Easy Setup
[x] Easy Troubleshooting
[x] No Data Loss
[x] Can be mixed with Log Shipping


[x] Data availability with latency only over snapshot database




High availability / High scalability solutions

posted Sep 11, 2010, 8:09 PM by Ricardo Fonseca   [ updated Sep 11, 2010, 9:09 PM ]


I have been asked a couple of times now what is the best solution for my environments in terms of availability, scalability. And as my experience grows I tend to answer with the famous "it depends". Why, well because regarding your needs (reporting server , load balance load across multiple server(s), hot fail-over server , data-warehouse server etc...) a solution may fit best for a customer and another one will suit best another customer.

As in the real life, describing the purpose of your secondary server will drive 80% of the requirements to answer correctly the question. The last 20% will go with related business requirements and possibilities in terms of infrastructure and $. I have listed below and describe the architecture for the replication, database mirroring and the log-shipping solutions.


a. Database Replication

Transactional / Merge / Snapshot / Peer to peer


   


Publishing Data and Database Objects

When creating a publication, you choose the tables and other database objects that you want to publish. You can publish the following database objects using replication. The database objects within a replication environment are called "articles".

Database object Snapshot replication and
 transactional replication
Merge replication

Tables

X

X

Partitioned Tables

X

X

Stored Procedures – Definition (Transact-SQL and CLR)

X

X

Stored Procedures – Execution (Transact-SQL and CLR)

X

no

Views

X

X

Indexed Views

X

X

Indexed Views as Tables

X

no

User-Defined Types (CLR)

X

X

User-Defined Functions
(Transact-SQL and CLR)

X

X

Alias Data Types

X

X

Full text indexes

X

X

Schema Objects
(constraints, indexes, user DML triggers, extended properties, and collation)

X

X


A replication high availability architecture is defined by 3 majors elements. The publisher that manage the publication of the data to be replicated, the distributor that manage the distribution of the transaction and finally the subscriber which will subscribe to a set of publisher data objects :

- the publisher manage the objects that that will be available for the subscribers to load (called articles)

- the distributor implement all the agents jobs that will deal with the transfer , historic and the maintenance of the transaction log distribution between the publisher and their subscribers.

- the subscriber persist the publisher data to an offset location. The susbriber will have the possibility to choose from a list of the publisher publication object list described above.


b. Database Mirroring


Architecture without witness server





Architecture with witness server



How database mirroring works

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role.

Database mirroring involves redoing every operation that occurs on the principal database onto the mirror database as quickly as possible. 

Redoing is accomplished by sending a stream of active transaction log records to the mirror server. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.

Note : beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.



Operating Modes

A database mirroring session runs with either synchronous (high-safety mode) or asynchronous (high-performance mode) operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness supports automatic failover by verifying whether the principal server is up and functioning.

Note : the high-performance mode is not available in SQL Server Standard versions.



Transaction Safety and Operating Modes

Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode.

If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

  • If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic failover.
  • If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

c. Log Shipping


  

Log Shipping Operations

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  2. Copy the transaction log file to the secondary server instance.
  3. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.


Primary Server and Database

The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server.

The primary database is the database on the primary server that you want to back up to another server. The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.


Secondary Server and Databases

The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database.

A secondary server can contain backup copies of databases from several different primary servers. The secondary database must be initialized by restoring a full backup of the primary database and the tail log backup.

Raid setup comparison - Part 2/2

posted Sep 10, 2010, 10:23 PM by Ricardo Fonseca   [ updated Sep 15, 2010, 1:14 PM ]

(RAID 5 vs RAID 6 vs RAID 10 )

This follows the first post regarding read / write test comparison against various RAID Setup. Note that in the use case we tested the following partitions :

- a RAID 5 Lun Parition with 5 Disks , 
- a RAID 6 Lun Parition with 5 Disks , 
- two different RAID 10 partition on on a lun using 6 Disks and another one using 8 disks.

The server used is a IBM x336 series with 16GB of RAM, windows 2003 x64 installed. The setup raid was local to the machine, the cache controller was integrated to the machine and its size was 256MB.

Following is the legend table to help you to interpret the different test cases scenario therefore will you find all the detail of each scenario that has been tested :


Test Casethreads#Block Size (KB)Out. RequestDuration
T12641120 seconds
T22642120 seconds
T32644120 seconds
T42648120 seconds
T526416120 seconds
T626432120 seconds
T726464120 seconds
T8264128120 seconds


A. Result extracted for write performance :

  • Table Presentation




T1
T2
T3
T4
T5
T6
T7
T8
1
RAID6 - 
9HD;1T;7200k -
MB/S
25.84
26.84
22.28
26.73
23.07
24.42
30.58
27.22

RAID6 -
9HD;1T;7200k -
IO/S
413.45
429.49
356.55
427.75
369.16
390.77
489.30
435.56
2
RAID5 -
5HD;1T;7200k -
MB/S
17.61
19.51
19.62
20.91
21.00
19.05
21.32
20.20

RAID5 - 
5HD;1T;7200k -
IO/S
281.83
312.24
313.92
334.59
336.14
304.86
341.16
323.32
3
RAID10 - 
6HD;146Go;15kFC -
MB/S
66.41
70.88
73.27
68.22
61.87
62.10
71.78
74.44

RAID10 - 
6HD;146Go;15kFC -
IO/S
1062.70
1134.21
1172.37
1091.59
990.04
993.60
1148.51
1191.04
4
RAID10 - 
8HD;300Go;15kFC -
MB/S
78.41
89.17
100.07
108.70
110.07
108.10
112.21
107.55

RAID10 - 
8HD;300Go;15kFC -
IO/S
1254.59
1426.80
1601.18
1739.20
1761.27
1729.73
1795.36
1720.81



















  • Graph Presentation



B. Result extracted for read performance :

  • Table Presentation



Type
T1
T2
T3
T4
T5
T6
T7
T8
1
RAID6 - 9HD;1T;7200k -
MB/S
21.17
39.34
62.95
86.31
102.64
111.41
119.42
119.62

RAID6 - 9HD;1T;7200k -
IO/S
338.84
629.45
1007.32
1381.07
1642.37
1782.65
1910.72
1913.98
2
RAID5 - 5HD;1T;7200k -
MB/S
15.49
26.56
35.59
42.37
44.22
43.99
44.72
44.45

RAID5 - 5HD;1T;7200k -
IO/S
247.90
425.08
569.53
678.01
707.58
703.85
715.65
711.33
3
RAID10 - 6HD;146Go;15kFC -
MB/S
37.08
60.20
92.65
119.86
124.92
130.66
135.52
137.32

RAID10 - 6HD;146Go;15kFC -
IO/S
593.40
963.34
1482.50
1917.91
1998.84
2090.68
2168.37
2197.15
4
RAID10 - 8HD;300Go;15kFC -
MB/S
41.60
76.63
118.05
174.60
192.70
189.30
180.71
185.79

RAID10 - 8HD;300Go;15kFC -
IO/S
665.60
1226.20
1888.93
2793.66
3083.24
3028.83
2891.40
2972.75


  • Graph Presentation



Result : As for the interpretation of the output we can clearly see that setup in RAID 10 is still the more the most efficient in regards our tests scenarios. For read and write instructions it has demonstraste to be faster quite significantly for reads and even more for write operations.

Raid setup comparison - Part 1/2

posted Sep 10, 2010, 10:13 PM by Ricardo Fonseca   [ updated Sep 15, 2010, 1:14 PM ]

(RAID 5 vs RAID 1E vs RAID 10 )

Every-time, as a consultant DBA, a customer ask me the question which RAID and why will fit best their needs. I tend to provide my experiences, my ideas and share but rarely it's enough.

In this blog entry I will try show exactly for one specific machine what will be the efficiency for each and individual RAID setup in terms of rough IO/S and MB/s.

Note that the tool that I have used is SQLIO, in a near future I plan to also re-do the tests with the tool Iometer. What is important to know is that the situation when I did the tests was ideal. The Raid Setup had been done then re-done according to the needs and there were no application and no user connected to the server during the tests.

The server used is a IBM x336 series with 16GB of RAM, windows 2003 x64 installed. The setup raid was local to the machine, the cache controller was integrated to the machine and its size was 256MB.

Following is the legend table to help you to interpret the different test cases scenario therefore will you find all the detail of each scenario that has been tested :

Test Casethreads#Block Size (KB)Out. RequestDuration
T12641120 seconds
T22642120 seconds
T32644120 seconds
T42648120 seconds
T526416120 seconds
T626432120 seconds
T726464120 seconds
T8264128120 seconds


A. Result extracted for write performance :

  • Table Presentation




T1T2T3T4T5T6T7T8
1RAID5 - 4HD;145G;15k -MB/S19.0621.8125.2039.9246.0846.0446.6446.82

RAID5 - 4HD;145G;15k -IO/S305.02349.08408.14638.73737.38736.64746.35749.15
2RAID1E - 4HD;300G;10k -MB/S13.6515.0115.2915.5415.4815.4915.2715.55

RAID1E - 4HD;300G;10k -IO/S218.40240.30244.76248.76247.72247.91244.35248.95
3RAID10 - 4HD;300G;10k -MB/S22.0527.0833.9157.4273.1274.5774.4473.93

RAID10 - 4HD;300G;10k -IO/S352.91433.41542.56918.761169.921193.241191.071182.93


  • Graph Presentation




B. Result extracted for write performance :


  • Table Presentation




T1T2T3T4T5T6T7T8
1RAID5 - 4HD;145G;15k MB/S12.3512.1712.3912.3712.3812.4612.5412.61

RAID5 - 4HD;145G;15k IO/S197.69194.78198.24198.07198.09199.46200.67201.8
2RAID1E - 4HD;300G;10k MB/S8.428.288.258.418.498.348.398.3

RAID1E - 4HD;300G;10k IO/S134.80132.59132.03134.63135.84133.56134.27132.85
3RAID10 - 4HD;300G;10k MB/S16.6416.5816.7016.4916.5516.5616.7216.76

RAID10 - 4HD;300G;10k IO/S266.36265.36267.35263.97264.81265.09267.56268.18

  • Graph Presentation

Result : As for the interpretation of the output we can clearly see that setup in RAID 10 is far the most efficient in regards our tests scenarios. For read and write instructions it has demonstraste to be faster quite significantly and that even though in the RAID5 setup we had 15k drive rather than the 10k when setup in RAID10.

DataTranscoder beta release

posted Feb 7, 2009, 4:50 PM by Ricardo Fonseca   [ updated Apr 24, 2009, 6:13 AM ]

The binary providing multithread database server data loading and comparing structure is available for download.

You can have a descriptions of what is included and download the binary in the following URL:

DataTranscoder

Thank you

Ricardo

SQLStressTool beta release

posted Dec 3, 2008, 7:43 AM by Ricardo Fonseca   [ updated Dec 19, 2008, 5:50 AM ]

The library providing multithread database server stress test is available for download.

you can have a descriptions of what is included and download the Dll in the following URL:

SQLStressTool

Thank you

Ricardo

1-10 of 13

Comments