The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

If you get this error after running the command:

LOAD DATA INFILE "path_to_file/file.csv"
INTO TABLE table_name
COLUMNS TERMINATED BY ','


Then just add the keyword LOCAL to the command and it will work:

LOAD DATA LOCAL INFILE "path_to_file/file.csv
INTO TABLE table_name"
COLUMNS TERMINATED BY ','


Copy MySql table

To create a copy of a table in MySql use the command:

CREATE TABLE foo SELECT * FROM bar

This will copy the table and the data.

To copy the table structure only, use the command:

CREATE TABLE foo SELECT * FROM bar LIMIT 0

Get an SQLite database off an Android device.

Open up your command prompt and change directory (cd command)to the directory that has your adb.exe file in it. On windows it is usually in:
C:\Users\USERNAME\AppData\Local\Android\sdk\platform-tools.
Then type in:

adb pull /data/data/com.package.name/databases/YourDatabaseName


If the application is not debuggable then you will need to try another option:
You can create a backup of the application and then extract the backup file.
To do this type in:

adb backup "-f /FileName.ab -noapk com.package.name"

This will create an Android backup file (.ab) in the base directory of you main drive.
To extract this file, download the Android Backup Extractor at this address:

https://sourceforge.net/projects/adbextractor/

Extract the Android Backup Extractor files and then put your .ab file in the Android Backup Extractor folder. The open a command prompt and cd to the Android Backup Extractor folder. The type in the command:

java -jar abe.jar unpack YourFile.ab YourFile.tar

You will now have a zipped file in the Android Backup Extractor folder. Extract this file and you will have the applications database in the extracted folder.

Use Android shared preferences.

Shared preferences stores data as key/value pairs.

To put the key/value pair into shared preferences you can use the .commit() or .apply() command.

apply()

This saves your data into memory immediately and saves the data to disk on a separate thread. So there is no chance of blocking the main thread (your app won’t hang).

It is the preferred technique but has only been available since Gingerbread (API 9, Android 2.3).

commit()

Calling this will save the data to the file however, the process is carried out in the thread that called it, stopping everything else until the save is complete. It returns true on successful completion, false on failure.

Use commit() if you need confirmation of the success of saving your data or if you are developing for pre-Gingerbread devices. commit() has been available since API 1

The following code will setup shared_prefs file and store different values.

public static final String MyPREFERENCES = "MyPrefs"; //This will be the name of the shared preferences file
public static final String Name = "nameKey";

SharedPreferences sharedpreferences;
sharedpreferences = getSharedPreferences(MyPREFERENCES, Context.MODE_PRIVATE);
SharedPreferences.Editor editor = sharedpreferences.edit();
editor.putString(Name, "string");
editor.putBoolean(Name2, false);
editor.putInt(Name3, 23424);
editor.apply();


 

 

A good idea is to put the code for shared preferences into its own class and declare static methods. Here is an example for putting a boolean value into shared preferences:

import android.content.Context;
import android.content.SharedPreferences;
import android.preference.PreferenceManager;
import android.util.Log;

public class PreferencesMgr {

    private static SharedPreferences sSharedPreferences;
    public static final String MyPREFERENCES = "MyPrefs";//This will be the name of shared preferences file.

    public static void setBoolean(Context context, String key, boolean data ){
        Log.i("PrefMgr setBoolean", "key: " + key + ", data: " + data);
        sSharedPreferences = context.getSharedPreferences(MyPREFERENCES, Context.MODE_PRIVATE);
        SharedPreferences.Editor editor = sSharedPreferences.edit();
        editor.putBoolean(key, data);
        editor.apply();
    }

    public static Boolean getBoolean(Context context, String key){
        sSharedPreferences = PreferenceManager.getDefaultSharedPreferences(context);
        return sSharedPreferences.getBoolean(key, false);

    }
}

You then set the boolean value with:

PreferencesMgr.setBoolean(this,KEY,true);

and you retrieve it using:

boolean myBoolean = PreferencesMgr.getBoolean(context, KEY);

You can view the shared_prefs file using a command prompt after the file has been created.

 

 

SQL storage performance.

The single most important metric is latency. Latency is a measure of system health and the availability of a system resource. Latency is governed by queuing theory and an important contribution to queuing theory is Little’s Law. This law states that as a steady state system reaches capacity then response time reach infinity. So for our systems this means that as the storage capacity reaches its limit then response times get so big that the system is unusable and response times are the I/O times. This means we can test our storage by using I/O times.

Physical I/O response time is the amount of time (in seconds) that an average read or write request to a physical disk takes to complete. You can get this data easily by using Performance Monitor’s (perfmon) Logical or Physical Disk counters for Avg. Disk sec/Read and Avg. Disk sec/Write. These latency measures are the single biggest indicator of I/O health.

Most experts would agree that with today’s technology, the response times when using 15,000rpm disks should be in the range that Figure 1 shows.

SQL server best practise generally call for no more than 20 ms of delay but if you implement synchronous data replication with AlwaysOn Availability Groups then you cannot tolerate more than 10 ms. Some application may be even more sensitive.

Another approach is to keep an eye on the PAGEIOLATCH_XX and WRITELOG waits. These stats will also tell you a lot about how well you’re keeping up with the data and log I/O requests. High average waits on either of these stats should spark interest in terms of physical I/O response times.

You must remember to view these times as part of the whole and not in isolation. There are many kinds of PAGEIOLATCH waits (this is signified with the XX at the end) the most common type is PAGEIOLATCH_SH.

PAGEIOLATCH_SH – (SHare) waiting for a data file page to be brought from disk into the buffer pool so its contents can be read. When this wait type is the most common on a server, the usual reaction is that the I/O subsystem must have a problem and so that’s where investigations should be fixed.

In fact the first thing to do is to compare the PAGEIOLATCH_SH wait count and duration against your baseline. If the volume of waits is more or less the same, but the duration of each read wait has become much longer, then I’d be concerned about an I/O subsystem problem, such as:

• A misconfiguration or malfunction at the I/O subsystem level
• Network latency
• Another I/O workload causing contention with our workload
• Configuration of synchronous I/O-subsystem replication/mirroring

In my experience, the pattern is often that the number of PAGEIOLATCH_SH waits has increased substantially from the baseline amount and the wait duration has also increased (i.e. the time for a read I/O has increased), because the large number of reads overloads the I/O subsystem. This isn’t an I/O subsystem problem – this is SQL Server driving more I/Os than it should be. The focus now needs to switch to SQL Server to identify the cause of the extra I/Os.

If you find that you need more IOPS (Input Output operations Per Second) then a simple solution is to add more disks. If one disk performs 150 IOPS then two will perform 300 and so on. Remember that read and write IOPS are different. For a RAID 10 system that has 10000 IOPs for reads it will only have 5000 IOPS for writes.

Remember that sequential disk access is much more efficient than random disk access. Designing an application to generate sequential access can be the most effective action that can be taken.

Generating I/Os that are too big will stress a system and increase latency. SQL server backup will generate up to 1000KB I/O. Changing the backup to use 512 I/O will normally reduce latency and reduce backup times.

To change the backup to a maximum transfer size of 512KB:

BACKUP DATABASE [DBName] TO DISK = ‘E:\dump\BAckupFile.bak’

WITH MAXTRANSFERSIZE=524288, NAME = BackupName

GO

NTFS allocation unit size:

When you are formatting a partition you can choose the allocation unit size…the default is 4KB. SQL server files are usually large and Microsoft recommends you use a 64Kb allocation unit file size for data, log and tempdb. When using FILESTREAM or SSAS use the 4KB allocation unit size.

Testing storage performance.

SQLIOSim is designed to simulate SQL Server I/O. It will stress the SQL server, the windows Server and the storage

If you start running SQLIO, it uses a default test size of 100MB.  This size test will likely be smaller than your storage cache. To create a larger test file we need to edit the param.txt file that is located wherever you installed SQLIO. You need administrator rights to edit this file so launch notepad or notepad++ or whatever text editor you’re using as an administrator. The param.txt file will have two rows:

c:\testfile.dat 2 0x0 100

#d:\testfile.dat 2 0x0 100

The first line identifies c:\ as the target drive and testfile.dat as the test file to create on that drive. The 2 refers to the number of threads to use when testing the I/O patterns. The 0x0 value indicates that all CPUs should be used.

The # at the start of the second line means that it is commented out so we don’t need to worry about that line. We do however want to change the first line.

Change the drive letter to the drive that you wish to test. To increase the test file size to a 20GB test file, replace 100 with 20480.

Save the file and then open a new file and call it testFile.bat.

Type in the following lines of code:

sqlio -kR -frandom -t1 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kR -frandom  -t2 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kW -frandom -t1 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kW -frandom -t2 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kR -fsequential -t1 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kR -fsequential -t2 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kW -fsequential -t1 -o4 -b64 -s60 -BH -LS E:\testfile.dat

sqlio -kW -fsequential -t2 -o4 -b64 -s60-BH -LS E:\testfile.dat

To explain what this code does I will explain each part of the first line of code.

The command starts by calling the sqlio utility.

–k            this option which specifies the I/O type (R for read operations and W for write operations).

–s             this option specifies the test duration in seconds, in this case, 300 seconds or 5 mins.

-f            this option determines how data blocks are handled, random or sequential. A random value indicates that blocks will be chosen randomly, as is often the case with T-SQL queries. The sequential value specifies that blocks should be accessed sequentially, which is typical for operations such as backups and bulk loads.

-o,          This option indicates the number of I/Os that can be outstanding in a single thread.

-b           This option. This is the I/O block size in kilobytes (KB). In the example, we’ve specified both 8 and 64KB.   The reason for this is that SQL Server does a lot of random stuff in 8KB chunks, and we’re also testing sequential stuff in 64KB chunks.

-BH        This option selects hardware buffering.

–LS         This option is to collect system latency information.

Now go to the command prompt and change directories to the directory where you installed SQLIO. Now type in:

sqlio -kW -s10 -fsequential -o8 -b64 -Fparam.txt

This will do a 10 second test and will also create the test file using the file location and size parameters you specified in param.txt.

Now type in :

testFile.bat

This runs the testFile that we created earlier.

In the SQLIO results the IO’s / sec and the MB’s / sec are usually the main numbers to look for.

Database workload types

When architecting a new SQL database it’s important you first understand the different SQL Server workload types you may  come across in your environment and the characteristics connected with them.

The different types of workload you can have on your server are:

1. On-Line TransactionProcessing is a data modelling approach typically used to facilitate and manage usual business applications. Most of applications you see and use are OLTP based. Pure OLTP tends to have large amounts of small queries, a relatively high percent of write activity, and sustained CPU utilisation during work hours and is sensitive to contention. Processors with higher base clock speeds and higher turbo speeds tend to perform better with OLTP queries. These workloads have higher I/O operations per second (IOPS) than an equivalent data warehouse. With a single OLTP database, you will see mostly sequential write activity to your transaction log file, and more random write activity to your data file(s).

2. On-Line AnalyticProcessing and is an approach to answer multi-dimensional queries. OLAP was conceived for Management Information Systems and Decision Support Systems. OLAP workloads have several different components, including reading data from the source(s) to initially build or update the cube, processing the cube when changes are made, and then actually running various types of OLAP queries to retrieve the data for users. Having processors with higher physical core counts, with better memory controllers in order to execute these types of queries as quickly as possible, is very valuable. Also very important for OLAP workloads is having a large amount of memory so that you can process large cubes quickly. OLAP workloads tend to have a lot of random I/O, so flash-based storage for the cube files can be very beneficial. Flash-based storage includes solid-state drives (SSDs) and other devices that use solid-state flash memory for permanent storage. These types of devices offer extremely high random I/O performance, which is very useful for OLAP workloads.

3. Data driven Decision support systems are used to access and manipulate data they are often used in conjunction with On line Analytical Processing. DSS workloads are characterized by a few queries that are longer running, resource intensive (CPU, memory, I/O), and often exhibit these characteristics during month, quarter, or year-end. DSS queries favour read over write, so it is important for the system to be able to provide that data in the quickest manner possible. This places a premium on having processors with higher physical core counts and better memory controllers in order to execute these types of queries as quickly as possible. Also very important for DW workload is to ensure you have adequate room for the buffer pool.

4. Batch/ETL workload types tend to be write intensive, run during off-peak hours, tolerate contention better than OLTP workloads, and sometimes are network intensive. Batch workloads are often run at the end of the business day to generate reports about transactions that occurred throughout the business day. Batch/ETL workloads are broken down into three distinct phases:

• Extract—when multiple sources are contacted and data is obtained from these sources.
• Transform—when an action (or actions) is taken upon the obtained data to prepare it for loading into a target system.
• Load—when the data is loaded into the target system (which is often a data warehouse).

The output of ETL is sent to a staging area (this is a place where you hold temporary tables on a data warehouse server). The staging area holds the data, and performs data cleansing and merging, before loading the data into warehouse. Without a staging area, the data will have to go from the OLTP system to the OLAP system directly, which will severely impede the performance of the OLTP system. This is the main reason for the existence of a staging area.

OLTP and Batch/ETL workloads are often put on the same server as OLTP runs during production hours and Batch/ETL runs during non-production hours. (Obviously in some companies this will not be the case).

OLTP and DSS are generally put on separate servers as they tend to run at the same time as the last thing you need is for a long-running DSS query to affect a revenue-generating customer-facing application that is connected to an OLTP database.

If there is pressure from above to consolidate servers then virtualization may be the answer.

RAID levels explained

RAID 0 is used to improve a server’s performance. It’s also known as “disk striping.” With RAID 0, data is written across several disks. This means the work that the computer is doing is handled by multiple disks rather than just one, increasing performance because multiple drives are reading and writing data, improving disk I/O. A minimum of two disks is required. The downside is that there is no fault tolerance. If one disk fails, then that affects the entire array and the chances for data loss or corruption increases.

Yellow = Data

RAID 1 is a fault-tolerance configuration known as “disk mirroring.” With RAID 1, data is copied from one disk to another, creating a mirror of the disk. If one disk crashes, the other can keep working. It’s the simplest way to implement fault tolerance and it’s relatively low cost.

The drawback is that RAID 1 causes a slight drag on performance. A minimum of two disks is required for RAID 1 hardware implementations. With software RAID 1, instead of two physical disks, data can be mirrored between volumes on a single disk. Also, RAID 1 cuts total disk capacity in half: If a server with two 1TB drives is configured with RAID 1, then total storage capacity will be 1TB not 2TB.

Yellow = Data
Blue = Fault tolerant information.

RAID 5 is by far the most common RAID configuration for business servers and enterprise NAS devices. This RAID level provides better performance than mirroring as well as fault tolerance. With RAID 5, data and parity (which is additional data used for recovery) are striped across three or more disks. If a disk gets an error or starts to fail, data is recreated from this distributed data and parity block— seamlessly and automatically. Essentially, the system is still operational even when one disk crashes and until you can replace the failed drive. Another benefit of RAID 5 is that it allows many NAS and server drives to be “hot-swappable” meaning in case a drive in the array fails, that drive can be swapped with a new drive without shutting down the server or NAS and without having to interrupt users who may be accessing the server or NAS. It’s a great solution for fault tolerance because as drives fail (and they eventually will), the data can be rebuilt to new disks as failing disks are replaced. The downside to RAID 5 is the performance hit to servers that perform a lot of write operations. For example, with RAID 5 on a server that has a database that many employees access in a workday, there could be noticeable lag.

Yellow = Data
Blue = Fault tolerant information.

RAID 10 is a combination of RAID 1 and 0 and is often denoted as RAID 1+0 or RAID 0+1. It combines the mirroring of RAID 1 with the striping of RAID 0. It’s the RAID level that gives the best performance, but it is also costly, requiring twice as many disks as other RAID levels, for a minimum of four. This is the RAID level ideal for highly utilized database servers or any server that’s performing many write operations. RAID 10 can be implemented as hardware or software, but the general consensus is that many of the performance advantages are lost when you use software RAID 10.

Yellow = data
Blue = Fault tolerant information